Beefy Boxes and Bandwidth Generously Provided by pair Networks
Pathologically Eclectic Rubbish Lister
 
PerlMonks  

DBD::Oracle DBMS_METADATA and DBMS_OUTPUT

by clueless newbie (Curate)
on Mar 07, 2022 at 17:41 UTC ( [id://11141889]=perlquestion: print w/replies, xml ) Need Help??

clueless newbie has asked for the wisdom of the Perl Monks concerning the following question:

I'm dealing with an Oracle database via DBI and DBD::Oracle and have several questions: WARNING: TOTAL Oracle newbie!

1) Exactly What does the statement to be prepared look like when one need "dbms_metadata.set_transform_param(dbms_metadata.session_transform,'storage',false)" prior to ones "select to_char(dbms_metadata.get_ddl(object_type, object_name, owner))"?

$STH=$DBH->prepare('_SQL_'); dbms_metadata.set_transform_param(dbms_metadata.session_transform,' +storage',false); SELECT to_char(dbms_metadata.get_ddl(object_type, object_name, owne +r)) ... _SQL_

yields "DBD::Oracle::st execute failed: ORA-01036: illegal variable name/number (DBD ERROR: OCIBindByName)...

2) From the DBI how does one "fetch" DBMS_OUTPUT's buffer?

Thanks!

Replies are listed 'Best First'.
Re: DBD::Oracle DBMS_METADATA and DBMS_OUTPUT
by clueless newbie (Curate) on Mar 08, 2022 at 15:57 UTC
Re: DBD::Oracle DBMS_METADATA and DBMS_OUTPUT
by clueless newbie (Curate) on Mar 15, 2022 at 20:28 UTC

    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: perlquestion [id://11141889]
Approved by Corion
Front-paged by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others rifling through the Monastery: (4)
As of 2024-04-14 18:00 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found