Buffer Overflow - need to extend or otherwise avoid

[CharlieMike73]

Hi,

I need to find a way to output the text string (from within a loop) created by a procedure we are running in PL/SQL+.

When successfully run the script would output approx 44k records to the text file, for another script to access later.

The problem is that each line is about 250 (-/+ 100) chars in length, so when we use DBMS_OUTPUT.PUT_LINE (...) we hit he buffer limit before we can even blink.

How can we avoid hitting the buffer limit?

we tryed things like:

SPOOL C:\SQL_SCRIPTS\DYNAMIC_SECURITY_UPDATE.SQL

SET FEEDBACK ON;
SET SERVEROUTPUT OFF;
SET VERIFY OFF;
SET BUFFERSIZE 1000000; -- set the buffer limit to 1million
SET ARRAYSIZE 2; -- to avoid Overflowing the buffer
[\code]

Any help is appreciated,

Thank you in advance!

 

[carp]

You might want to look at using PL/SQL with calls to the utl_file package.

 

[sem]

You may do not use dbms_output and move to pure sql (to return results). The idea is to create [stored] procedure, returning refcursor. You may call it (with bind parameter) and print the bind variable content. You may make minor changes into your existing procedure by creating a type as table of varchar2 (long enough for your needs), declare a variable of this type in your procedure, replace all calls to dbms_output to "inserts" into this variable and finally return "select *" from it.

 

[rk0000]

Don't try to print whole of the string at atime. u can print 256 chars at one time.
so try this:

dbms_output.put_line(' The string is ' || substr(var_string_name,1,200));
dbms_output.put_line(substr(var_string_name,201,200));

first statement print 1 to 200 chars of var_string_name.
second statement print 201 to 200 chars of var_string_name. (ie 201 th char to 400) so on
u can write loop if u have a very long char string.
bye, hope it work
Ramakrishna

 

[Highrisk]

Hi, All you need is to just execute the following statement on sql+

SQL > execute dbms_output.enable(5000000);

It will increase the bufferwidth in sql+ session.

Regards

 

[sem]

Highrisk: large buffer doesn't mean large line size. SET BUFFERSIZE 1000000 makes the same as dbms_output.enable(1000000), though this doesn't affect the size of dbms_output.put_line parameter, that is still limited by 255