Re: spooling from PL/SQL

Kapustin A.G. wrote:
>
> Hello!
>
> Yes, 'SET FEEDBACK OFF' works fine -
> Messages like 'PL/SQL Procedure successfully...' don't appear after it.
>
> But I have other question:
> How can I put to pl/sql-spool, for example, simple phrase ('Hello!')?
>
> Assume I have pl/sql block:
>
> DECLARE
> STR VARCHAR2(100);
> BEGIN
> SELECT 'Hello!' INTO STR FROM DUAL;
> END;
> /
>
> Before executing it, I say: 'SET FEEDBACK OFF', 'SET HEADING OFF',
> 'SPOOL sp.lst'.
> Then I execute block, and - empty output.
>
> How can I put 'Hello!' to spool?
> Decisions like 'Try using SQL, not PL/SQL' - not a good idea.
>
> Alexander G. Kapustin
>
> Russia, Togliatti
> AVTOVAZ Inc.
> =================================================

You can use the package DBMS_OUTPUT
which includes a procedure put_line.

Be aware though that DBMS_OUTPUT is actually a bit kludgy , IHMO. For one the output isn't put on your screen immediately, but into a buffer, which is displayed after the routine has finished. So, for instance, you can't get progress reports from a routine which takes a long time.
Related to that is the fact, that this buffer has a preassigned size, 2000 bytes by default I believe. If the buffer is full, and you try to produce more output your routine will abort.

For interactive routines I'd suggest looking into perl or oraperl.

Christian Received on Wed May 20 1998 - 00:00:00 CDT