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.

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

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