Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer
 
PerlMonks  

Redirecting output across Oracle DB link

by jeremyh (Beadle)
on Apr 14, 2006 at 22:05 UTC ( #543446=CUFP: 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;

Comment on Redirecting output across Oracle DB link
Select or Download Code

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: CUFP [id://543446]
Approved by planetscape
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others avoiding work at the Monastery: (8)
As of 2014-09-23 07:16 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    How do you remember the number of days in each month?











    Results (210 votes), past polls