Buffer Overflow

[THoey]

A co-worker is getting the following error after running a large stored procedure:

buffer overflow. Use SET command to reduce ARRAYSIZE or increase MAXDATA.

I have seen this before, but can't remember what causes it. I also don't have that much experience in stored procedures yet, so I don't know how she would change the setting for this. Any ideas? Terry M. Hoey

 

[karluk]

This is a SQL*Plus error that is telling you that the SQL*Plus buffer isn't large enough to hold all the data being read from your table.

The two suggested fixes work in different ways. "Arraysize" determines the number of rows that are read into the SQL*Plus buffers during a single database call. Reducing "Arraysize" will increase the maximum length of row that SQL*Plus can handle, because the buffer is being shared by fewer rows. "Maxdata" I think is the total size of the SQL*Plus buffer, so increasing that will also help.

The two commands can be entered at any SQL*Plus prompt. For example

set arraysize 1
set maxdata 60000