Oracle CLOB and ORA-01062: unable to allocate memory for the define buffer

Question

I have a table in an Oracle database which has a CLOB field. My ASP.NET application (using ODAC Oracle data provider) queries this table.

At the point of running the query we are seeing this error (from a 9i database):

ORA-01062: unable to allocate memory for the define buffer

In another Oracle database (10g) the query executes without errors. In both cases the table contains no data.

The Oracle OTN website says this:

ORA-01062, 00000, "unable to allocate memory for define buffer"

Cause: Exceeded the maximum buffer size for current plaform

Action: Use piecewise fetch with a smaller buffer size / 1070 - 1099: V6 program interface errors

I'm not an Oracle DBA and I have no idea what this means...

Could this be due to differences between Oracle 9i and 10g ?

Is there some sort of setting that can be changed on the database to stop this error from occurring?

Answer 1

Sounds like your CLOB is too big to be dealt with in one chunk through the provider. You could check that your patches are up to date on 9i (you'd want to be on 9.2.0.8).