DBD::Oracle DBMS_METADATA and DBMS

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_
[download]

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!

Comment on DBD::Oracle DBMS_METADATA and DBMS_OUTPUT Download Code

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
Clueless In response to your second question see dbms_output_enable / dbms_output_put / dbms_output_get. Clueless	[reply]
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; [download] Adding: It appears that 1 MB is the upper limit to dbms_output's buffer size! (as of Oracle release 21)	[reply] [d/l]


Just another Perl shrine
	PerlMonks

DBD::Oracle DBMS_METADATA and DBMS_OUTPUT