Beefy Boxes and Bandwidth Generously Provided by pair Networks
Just another Perl shrine
 
PerlMonks  

Re: DBD::Oracle DBMS_METADATA and DBMS_OUTPUT

by clueless newbie (Curate)
on Mar 15, 2022 at 20:28 UTC ( [id://11142125]=note: print w/replies, xml ) Need Help??


in reply to DBD::Oracle DBMS_METADATA and DBMS_OUTPUT

Hey, Clueless - Here you go!

$DBH=DBI->connect($task->{$DATA_SOURCE},$task->{$USERNAME},$task-> +{$PASSWORD}, { HandleError=>Exception::Class::DBI->handler,LongReadLen=>409 +6,PrintError=>1,RaiseError=>1 }); ################################################### # A Allocate some memory for dbms_output to use! $DBH->func(1_000_000,'dbms_output_enable'); my $SQL=<<"_SQL_"; -- <Your select goes here!> SELECT ... as something FROM ...-- no ";" _SQL_ $DBH->do(<<"_SQL_"); BEGIN dbms_metadata.set_transform_param(dbms_metadata.session_tr +ansform,'PRETTY',true); dbms_metadata.set_transform_param(dbms_metadata.session_tr +ansform,'SQLTERMINATOR',true); dbms_metadata.set_transform_param(dbms_metadata.session_tr +ansform,'STORAGE',false); FOR row IN ($SQL) LOOP -- use dbms_output.put_line to stash the results dbms_output.put_line(row.something); END LOOP; END; _SQL_ # Get the contents stashed in dbms_output my @lines=$DBH->func('dbms_output_get'); # Do something useful with @lines ################################################### $DBH->disconnect;

Adding: It appears that 1 MB is the upper limit to dbms_output's buffer size! (as of Oracle release 21)

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://11142125]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others pondering the Monastery: (4)
As of 2024-04-24 06:59 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found