ORA-22290 BFile/LOB problem

[Jarle]

We use a query like this:

SELECT t1.thumbnail.Getbfile() thumbnail, title from sometable t1 where somefield=5

as a datasource for a (DevExpress) grid to display a thumbnail image and some other info for each row.
The OracleMonitor shows that for each row with a thumbnail, the LOB is opened and closed, except for the last thumbnail, which is opened, but never closed.

This results in leaving one extra LOB open for each run of the query (TOracleDataset), quickly resulting in an ORA-22290 message:
"operation would exceed the maxiumum number of opened files or LOBs"

TOracleDataset.Close does not close the open LOB(s), but CloseAll does. However, it is pretty inconvenient having to do CloseAll after each time having activated (or changed parameters) to one of the datasets running these kinds of queries. Is there another way to solve this problem?

DOA: 3.4.6.1
Oracle: 8.1.7
Delphi 5, Update Pack 1

Cheers,
Jarle

 

[Marco Kalter]

I can open and close datasets with LOB fields all day long without any error.

What exactly does Getbfile() do?

------------------
Marco Kalter
Allround Automations

 

[Jarle]

Getbfile() is a method of an ORDIMAGE (ORDSYS.ORDIMAGE) object to get the image content if the image data is stored in a BFile.

If the image data is stored in the row itself (BLOB), the corresponding call to get the data is GetContent(), and in that situation there is no problem.

By searching on Google for ora-22290, I find a similar report about the same problem.

Not sure whether this monster URL will work: http://groups.google.com/groups?hl=...v=/groups?q=ORA-22290&ie=UTF-8&oe=UTF-8&hl=en

Cheers,
Jarle

 

[Marco Kalter]

Thanks for the info. I will look into this, but my first guess is that the ReadBuffer value will determine the number of simultaneously opened BFILE's. Therefore there might be 3 workarounds, each with its own drawbacks:

1. Decrease ReadBuffer for BFILE queries to 1. This will also decrease performance, though this may be minimal if the BFILE contents are large.

2. Increase the SESSION_MAX_OPEN_FILES initialization parameter (according to the google thread).

3. Call CloseAll for BFILE queries.

I think a combination of 1 and 2 will work out okay. The best permanent solution will probably be a TOracleDataSet.CloseAfterEOF property.

------------------
Marco Kalter
Allround Automations

 

[Jarle]

Originally posted by mkalter:
...my first guess is that the ReadBuffer value will determine the number of simultaneously opened BFILE's. Therefore there might be 3 workarounds, each with its own drawbacks:

1. Decrease ReadBuffer for BFILE queries to 1. This will also decrease performance, though this may be minimal if the BFILE contents are large.

This actually seems to make the problem worse, because (AFAICT) then one file gets "leaked" for each row in the resultset containing a LOB.
(I initially assumed the last LOB in the resultset got leaked, but now it seems to me that it is the last one in the "group" of records fetched which gets leaked, meaning the problem could be somewhat worse than I initially assumed (but I'm not 100% sure about this!))

2. Increase the SESSION_MAX_OPEN_FILES initialization parameter (according to the google thread).

Then one can run more (or larger) queries before the problem hits you in the head, but it's not a pretty solution!

3. Call CloseAll for BFILE queries.

Seems to me that this is the best known workaround.

Cheers,
Jarle