[cx-oracle-users] Strange behavior when using SessionPool and cx_Oracle

[Hancock, D. \(DHANCOCK\) <DHA...@ar...>]

Good morning/afternoon/evening!  I've just learned of the list and
signed up.  Below is a question from a developer on our team; it's
probably not specifically a cx_Oracle issue, but we'd appreciate any
ideas list members might have.

Cheers!
--
David Hancock | dha...@ar... | 410-266-4384=20


We use cx_Oracle (4.1) as our driver to connect our Python code and
Oracle DB. The application can either get a connection object from
cx_Oracle.connect('eliu/password@adc') or from the Oracle 10g
SessionPool object's acquire() method. By default, we get a connection
from SessionPool. =20

We have experienced weird behavior of the SessionPool object recently.
Our application tries to insert records into a table repeatly (in a
loop, with the same query statement but different values).  The table
schema and query statement look like this:

	create table product_reports(id varchar2(4), product
varchar2(20), report varchar2(4000));
            insert into product_reports(id, product, report) values (:1,
:2, :3)

The application works fine and all records are saved into the table
properly as long as :1, :2, :3 lengths are within the specified range
above. Currenly, some product report's sizes are bigger than expected
(>4000 chars), so they are failed to get stored and the error code is:=20

           ORA-01461: can bind a LONG value only for insert into a LONG
column

As I mentioned the insertion is in a loop; if one of the records failed
with above reason, then all subsequent records will fail even if all 3
values are valid, with the error code:

           ORA-01003: no statement parsed.=20

I wrote a unit test and found the following:

1. The same code will fail if the connection object obtained from
SessionPool. That means the same code will pass if I use
cx_Oracle.connect('eliu/password@adc') to get the connection object.

2. If the connection object obtained from SessionPool, the query will
work for rest of inserts if failed on the first time. That means:

     2.1 If I need to insert 5 records (in a loop and the 1st record has
large data) into the same table with the same query and    the
application just cut-over (the same query never used since cut-over),
only the first record has ORA-01461 and rest of records inserted
correctly.

     2.2  If I need to insert 5 records (in a loop and the 2nd record
has large data) into the same table with the same query and then the
first record inserted OK, the 2nd will have ORA-01461, and rest of 3
records all have ORA-01003.=20

Is this making any sense to you? I guess this may have something to do
with how Oracle handles statement within the cursor object internally.
Do you have any suggestion? Thanks a lot.