Does anyone know how to close BFILE LOBs selected by TOracleDataSet without
having to close the TOracleSession?

I am accessing an Oracle Table with a BFILE field using the TOracleSession
and TOracleDataSet components. After making a quantity of selections from
this table, an ORA-22290 error is raised.

[excerpt from the Oracle document A67785.pdf]:
ORA-22290 operation would exceed the maximum number of opened files or LOBs
    Cause: The number of open files or LOBs has reached the maximum limit.
    Action: Close some of the opened files or LOBs and retry the operation.

Essentially the minimalistic code that generates this condition is:

iRecordCount := 11;
for iIndex := 1 to iRecordCount do begin
    odsMyDataSet.Close;
    odsMyDataSet.SetVariable('MyKey', iIndex)
    odsMyDataSet.Open;
end; //for iIndex

odsMyDataSet.SQL is:
"SELECT mt.mykey, mt.mybfile FROM mytable mt WHERE mt.mykey = :MyKey"

The error occurs on the 11 th record if the Oracle configuration parameter
(in the InitOra.ini file) is "session_max_open_files = 10".

Setting the ossMySession.Connected property to False clears the "Open Files"
but resetting the session connection frequently doesn't perform to well.

If my odsMyDataSet.SQL doesn't select a BFILE field, with the records, there
is no problem.

The following SQL property works for example:
"SELECT mt.mykey FROM mytable mt WHERE mt.mykey = :MyKey"

AJS

I don't work with BFILES, but I think a call to DBMS_LOB.FILECLOSEALL
will do the trick.

John Pierce

Thanks!!!  Calling the TOracleDataSet.CloseAll method (instead of
TOracleDataSet.Close) works!

Arnold

news:3B8E9625.A50E8B00@healthplanning.com...