Re: Line length limitations in PL/SQL

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.

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 ) );     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 Wed Oct 14 1998 - 00:00:00 CDT