Re: spooling from PL/SQL

On Fri, 05 Jun 1998 21:07:23 GMT, csrao_at_my-dejanews.com wrote:

>I would like to spool the results from a PL/SQL procedure.
>
>Is there a way?
>
>Right now, I am inserting the variable values from the procedure to a temp
>table and selecting from that...

Are you running this script in SQL*Plus? If you don't have a huge amount of output, you can use DBMS_OUTPUT to do what you want. For example:

SQL> SET SERVEROUTPUT ON
SQL> BEGIN
  2 DBMS_OUTPUT.PUT_LINE('HI THERE');   3 END;
  4 /
HI THERE PL/SQL procedure successfully completed.

You won't see the output until the PL/SQL procedure is completed. Don't forget the SET SERVEROUTPUT ON command, or you won't see anything. By default, the maximum number of characters you can output is 2000. You can changes this by using DBMS_OUTPUT.ENABLE(1000000). One million is the maximum value. I've never needed to output anywhere near that much.

One other thing. Leading spaces go away. For example:

SQL> L
  1 BEGIN
  2 DBMS_OUTPUT.PUT_LINE(' HI THERE');   3* END;
SQL> /
HI THERE Notice that the leading spaces are gone. Tabs however, will come through. Example:

SQL> begin
  2 dbms_output.put_line(chr(9) || 'hi there');   3 end;
  4 /

        hi there

Hope this helps.

Jonathan Received on Sun Jun 07 1998 - 00:00:00 CDT