Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

ORA-01044: size of buffer bound to variable exceeds maximum

392308Feb 20 2004 — edited Dec 8 2005

Hello Oracle Gurus,

I have a tricky problem.

I have a stored procedure which has to retun more than 100,000 records. In my stored procedure, I have "TABLE OF VARCHAR2(512) INDEX BY BINARY_INTEGER". It fails when I try to get 80,000 records.

I get an error "ORA-01044: size 40960000 of buffer bound to variable exceeds maximum 33554432"

A simple calculation shows that 512*80000=40960000.

Oracle help suggests to reduce buffer size (i.e., number of records being returned or size of variable).

But, reducing the number of records returned or reducing the size of variable is not possible because of our product design constraints.

Are there any other options like changing some database startup parameters to solve this problem?

Thanks,
Sridhar

Locked Post

New comments cannot be posted to this locked post.

Locked on Jan 5 2006

Added on Feb 20 2004

#general-database-discussions

7 comments

1,330 views