http://qs1969.pair.com?node_id=11142125


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)