hi all,

I am trying to execute a query and getting the following error
" Buffer Overflow use set command to reduce array size or increase maxdata"

It is always safe to assume, not that the old way is wrong, but that there may be a better way...
*************************
Rekha

__________________
It is always safe to assume, not that the old way is wrong, but that there may be a better way...
*************************
Rekha

Hi Rekha,

That is a memory error. Arraysize is a SQL*Plus variable to set the number of records fetched in a batch at a time. A high value requires more memory and that's why SQL*Plus complains for queries that return large number of rows. As mentioned in the error message, reduce the arraysize (using the SET command).

SQL> show arraysize
arraysize 15
SQL> set arraysize 9
SQL> show arraysize
arraysize 9
SQL> -- now run your query again...

I think MAXDATA is obsolete, hence setting maxdata will probably be ignored by SQL*Plus.

Hope that helps.
azizmasih

Thanks for response. But it is not working after setting the arraysize to 9 also I m getting the same error.

It is always safe to assume, not that the old way is wrong, but that there may be a better way...
*************************
Rekha

Hi Rekha,

Try with some lesser values for ARRAYSIZE and higher values for MAXDATA. I think you can go till 60000 for MAXDATA.

With regards,
azizmasih