how do you spool from a stored procedure that is executed through a database link?

Question

I am using a UNIX script to run sql code that kicks off a stored procedure via database link. I can get the procedure to complete successfully, however none of the DBMS outputs are spooled to the SPOOL file indicated.

SQL within UNIX:

set feedback off;
set linesize 500;
set serveroutput on size 1000000;
set serveroutput on format wrapped;
spool $SQLspool;

whenever oserror exit;
whenever sqlerror exit sql.sqlcode;

DECLARE

retcode integer :=0;

BEGIN

owner.procedure@db;

dbms_output.put_line('');
dbms_output.put_line('return code: ' || retcode);
dbms_output.put_line('');


EXCEPTION
        WHEN OTHERS THEN
        RAISE;  

END;
/
EXIT;

---

SPOOLFILE CONTENTS:

return code: 0

---

I list a bunch of DMBS outputs within the stored procedure but nothing is written to the spool file.

How can I get it to output to the spool file?

I tried to have IN OUT variables, but because the procedure contains COMMITs it errors out with the parameters since it is going through the DB Link...

Answer 1

The output for PUT and PUT_LINE is buffered. From the Oracle docs :

SQL*Plus does not display DBMS_OUTPUT messages until the PL/SQL program completes. There is no mechanism for flushing the DBMS_OUTPUT buffers within the PL/SQL program.

So, if you are looking to stream responses you are going to need to write a small program which does not buffer the output.