Clueless

In response to your second question see dbms_output_enable / dbms_output_put / dbms_output_get.

Clueless

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)