Re: Line length limitations in PL/SQL

From: Tansel Ozkan <tansel_at_openix.com>
Date: 1998/10/15
Message-ID: <3626254C.310F@openix.com>#1/1

Christopher Beck wrote:
>
> On Wed, 14 Oct 1998 17:16:48 -0400, Tansel Ozkan <tansel_at_openix.com>
> wrote:
>
> >Hello,
> >
> >I have to write a PL/SQL program that will output 1300 bytes
> >per record. I have come to learn that DBMS_OUTPUT.PUT_LINE has
> >a limitation of 255 bytes. Then I have looked into UTL_FILE.PUT_LINE
> >and found out that it has a limitation of 1023 bytes per line.
>
> You could write your own wrapper around either dbms_output.put_line
> or utl_file.put_line to chuck up you output into 255 byte size pieces.

Thanks for your input.

But I need 1300 bytes PER LINE. If I am not mistaken, when I use your wrapper, each record will be in more than one lines. This is not what I want. I tried using dbms_output.put instead of dbms_output.put_line function in your wrapper to make it work, but still got the same error message. 'Line longer than 255'

> eg.
>
> procedure my_put_line( p_string varchar2 ) is
> l_string varchar2(32767);
> begin
> l_string := p_string;
> loop
> exit when l_string is null;
> dbms_output.put_line( substr( l_string, 1, 255 ) );

                  ^^^^^^^^^
            This will output 255 bytes per line..



>     l_string := substr( l_string, 256 );

> end loop;
> dbms_output.put_line( chr(13) ); --new line between records
> end my_put_line;
>
> >
> >What is the solution? Is UTL_FILE.PUTF a solution since there is
> >no mention of a limitation for it? If that doesn't work I am planning
> >to store the record in a database table and do a select from SQLPlus
> >where the only limitation is the varchar2 column length, which is 2000.
>
> Note: If your program fails and rolls back then you will also roll
> back all your debug too. :-(
>
> >
> >Also, what is the maximum number of bytes you can output from PL/SQL?
> >I know you can set it with eg. 'set serveroutput on size 100000' but
> >I don't really know the maximum value for this parameter? (Oracle7.3)
> >
>
> The default buffer size is 20000 bytes. The minimum is 2000 and the
> maximum is 1,000,000.
>
> >Thanks..
> >
> >Tansel
>
> hope this helps.
>
> chris.
Received on Thu Oct 15 1998 - 00:00:00 CDT