Re: spooling from PL/SQL

kiel wrote:

> 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.
>

Yes, default value is 2k but you can increase it by:SET SERVEROUTPUT ON SIZE n, where n <= 1000000.

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

Igor Received on Fri May 22 1998 - 00:00:00 CDT