Re: spooling from PL/SQL

kiel wrote in message <3563399A.1479_at_webpre.com>...
>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

This is true but remember that you can increase the size of the buffer to one million bytes by using the size parameter. This gives you more room to work with but you still have to be careful.

set serveroutput on size 1000000
BEGIN
     DBMS_OUTPUT.PUT_LINE('Hello World'); END;
/

Patrick Flahan
flahan_at_leading.net Received on Fri May 22 1998 - 00:00:00 CEST