Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?
 
PerlMonks  

Comment on

( #3333=superdoc: print w/ replies, xml ) Need Help??
To be used in conjunction with Grant McLean's Sprog::PrintProxy

Sends output for a given file handle to Oracle's utl_file.put()

You invoke it like this:

use DBI; use Sprog::PrintProxy; use DBPrinter; my $dbh = DBI->connect("dbi:Oracle:", "uid", "passwd") or die "$!: Can't connect to DB"; my $fh = Sprog::PrintProxy->new( DBPrinter->new($dbh, "/some/directory +", "outfile.txt", "dblinkname") );

Now all output to $fh will end up in /some/directory/outfile.txt on the server where dblinkname runs. You can also simply output to a directory seen by the DB from your DBI connection by leaving out the dblinkname parameter. Can't think of a reason this would be useful, but there might be.

Note: Oracle requires that utl_file output directories be declared in init.ora, and the directory in the utl_file call must be "spelled" exactly the same as the directory listed in init.ora.

here's the code

# ==================================================================== +======== # DBPrinter # # Intercepts output to tie()'ed filehandle and writes it to Oracle # UTL_FILE.FILE_TYPE filehandle. Allows perl output to be redirected # from one server to another over a DB link. Max length per write (l +ine) # is 2000 chars. # # Jeremy Hickerson, 4/13/2006 # ==================================================================== +======== package DBPrinter; use strict; use DBI; # ==================================================================== +======== # Package level vars # ==================================================================== +======== my (%glb_prepared_sql, $sth_pls_write_DBfh, $DBH, $DB_fh, $Filedir, $F +ilename, $DB_linkname); # Constants my $SQL_CHAR = 1; my $SQL_NUMERIC = 2; my $SQL_DECIMAL = 3; my $SQL_INTEGER = 4; my $SQL_SMALLINT = 5; my $SQL_FLOAT = 6; my $SQL_REAL = 7; my $SQL_DOUBLE = 8; my $SQL_DATE = 9; my $SQL_TIME = 10; my $SQL_TIMESTAMP = 11; my $SQL_VARCHAR = 12; my $SQL_LONGVARCHAR = -1; my $SQL_BINARY = -2; my $SQL_VARBINARY = -3; my $SQL_LONGVARBINARY = -4; my $SQL_BIGINT = -5; my $SQL_TINYINT = -6; my $SQL_BIT = -7; my $SQL_WCHAR = -8; my $SQL_WVARCHAR = -9; my $SQL_WLONGVARCHAR = -10; # ==================================================================== +======== # Constructor # ==================================================================== +======== sub new { my ($class, $self); ($class, $DBH, $Filedir, $Filename, $DB_linkname) = @_; if ($DB_linkname) { $DB_linkname = "\@$DB_linkname" } else { $DB_linkname = "" } open_DBfh(); $self = { }; bless($self, $class); return $self; } # ==================================================================== +======== # Methods # ==================================================================== +======== sub print { my ($class, $data) = @_; # from Oracle's utl_file package: # # FILE_TYPE - File handle # # TYPE file_type IS RECORD (id BINARY_INTEGER); # we can populate/save id field of outfile with a bind var # for performance we will only prepare repeated sql once if (exists $glb_prepared_sql{"sth_pls_write_DBfh"} ) { $sth_pls_write_DBfh = $glb_prepared_sql{"sth_pls_write_DBfh"}; } else { $sth_pls_write_DBfh = $DBH->prepare( qq{ DECLARE outfile sys.UTL_FILE.FILE_TYPE$DB_linkname; BEGIN outfile.id := :DB_fh; sys.utl_file.put$DB_linkname(outfile, :data); END; } ); $sth_pls_write_DBfh->bind_param_inout(":DB_fh", \$DB_fh, $SQL_ +BINARY); $glb_prepared_sql{"sth_pls_write_DBfh"} = $sth_pls_write_DBfh; } $sth_pls_write_DBfh->bind_param_inout(":data", \$data, $SQL_VARCHA +R); $sth_pls_write_DBfh->execute(); } sub open_DBfh { my $sth_pls_open_DBfh = $DBH->prepare( qq{ DECLARE outfile sys.UTL_FILE.FILE_TYPE$DB_linkname; MAX_LINESIZE integer := 2000; BEGIN outfile := sys.utl_file.fopen$DB_linkname(:Filedir, :Filename, + 'w', MAX_LINESIZE); :DB_fh := outfile.id; END; } ); $sth_pls_open_DBfh->bind_param_inout(":DB_fh", \$DB_fh, $SQL +_BINARY); $sth_pls_open_DBfh->bind_param_inout(":Filename", \$Filename, $SQL +_VARCHAR); $sth_pls_open_DBfh->bind_param_inout(":Filedir", \$Filedir, $SQL +_VARCHAR); $sth_pls_open_DBfh->execute(); } sub close_DBfh { my $sth_pls_close_DBfh = $DBH->prepare( qq{ DECLARE outfile sys.UTL_FILE.FILE_TYPE$DB_linkname; BEGIN outfile.id := :DB_fh; sys.utl_file.fclose$DB_linkname(outfile); END; } ); $sth_pls_close_DBfh->bind_param_inout(":DB_fh", \$DB_fh, $SQ +L_BINARY); $sth_pls_close_DBfh->execute(); } # ==================================================================== +======== # Destructor # ==================================================================== +======== sub DESTROY { %glb_prepared_sql = (); close_DBfh(); } 1;

In reply to Redirecting output across Oracle DB link by jeremyh

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post; it's "PerlMonks-approved HTML":



  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • Outside of code tags, you may need to use entities for some characters:
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.
  • Log In?
    Username:
    Password:

    What's my password?
    Create A New User
    Chatterbox?
    and the web crawler heard nothing...

    How do I use this? | Other CB clients
    Other Users?
    Others about the Monastery: (9)
    As of 2014-07-31 23:42 GMT
    Sections?
    Information?
    Find Nodes?
    Leftovers?
      Voting Booth?

      My favorite superfluous repetitious redundant duplicative phrase is:









      Results (255 votes), past polls