Re: DBMS_OUTPUT.PUT_LINE - any alternative

[Quoted] Why don't you insert the data into a temporary table, and then use SQL Plus to create a flat file using a Select?

DBMS_OUTPUT is more of a debugging tool. Besides, it buffers everything in memory, so if your amount of data gets very large, it will fail for that reason, too.

Steve Cosner

In article <37C6A338.AC7F7C4F_at_spamme.ed.ac.uk>, Fraser Boswell <Fraser.Boswell_at_spamme.ed.ac.uk> wrote:
>I've changed the PUT_LINE to PUT, and done
>dbms_output.enable (10000);
>and now I'm getting up to 2000 bytes out before getting the following error
>
>declare
>*
>ERROR at line 1:
>ORA-06502: PL/SQL: numeric or value error
>ORA-06512: at line 82
>
>Is this because the buffer isn't increasing in size, i.e. I don't have permission?
>
>Fraser
>
>Fraser Boswell wrote:
>
>> No joy I'm afraid I'm still getting the same error:
>>
>> ORA-20000: ORU-10028: line length overflow, limit of 255 bytes per line
>> ORA-06512: at "SYS.DBMS_OUTPUT", line 99
>> ORA-06512: at "SYS.DBMS_OUTPUT", line 65
>> ORA-06512: at line 83
>>
>> Frederic DEBRUS wrote:
>>
>> > You must change the buffer size look below
>> >
>> > hope this help
>> > ciao
>> > FreD
>> >
>> > The ENABLE procedure enables calls to the other DBMS_OUTPUT modules. If you
>> > do not first call ENABLE, then any other calls to the package modules are
>> > ignored. The specification for the procedure is,
>> >
>> > PROCEDURE DBMS_OUTPUT.ENABLE
>> > (buffer_size IN INTEGER DEFAULT 20000);
>> >
>> > buffer_size The size of the buffer that will contain the information stored
>> > by calls to PUT and PUT_LINE. The buffer size can be as large as 1,000,000
>> > bytes. You can pass larger values to this procedure without raising an
>> > error, but doing so will have no effect besides setting the buffer size to
>> > its maximum.
>> >
>> > You can call ENABLE more than once in a session. The buffer size will be
>> > set to the largest size passed in any call to ENABLE. In other words, the
>> > buffer size is not necessarily set to the size specified in the last call.
>> >
>> > If you want to make sure that the DBMS_OUTPUT package is enabled in a
>> > program you are testing, add a statement like this one to the start of the
>> > program:
>> >
>> > DECLARE
>> > ... declarations ...
>> > BEGIN
>> > DBMS_OUTPUT.ENABLE (1000000);
>> > ...
>> > END;
>
Received on Fri Aug 27 1999 - 18:31:05 CEST