PI JDBC receiver on Oracle

[Former Member]

Hi,

I'm getting one error on inserting records into Oracle DB. Error is:

Error while parsing or executing XML-SQL document: Error processing

> request in sax parser: Error during batched statement execution:

> java.sql.BatchUpdateException: ORA-01000: maximum open cursors exceeded

I marked Batch Mode because number of INSERTS into DB is huge and with batch processing I'm getting on performance.

So what to do to avoid this errors? Will "Disconnect from Database After Processing Each Message" help or can I somehow specify how many entries can be in on batch? Oracle DB is seted up for 5000 open cursors max which is sure far more than 50 what is default value....

kr

Mario

[Former Member]

Not answered but still closed

[Former Member]

Hi Mario,

is the database used by other applications either, which might cause this lack of resources?

Did you try to provide less data to see, wether it's really a resource problem or if there are possible other problems?

You might also switch off "batch mode" and see if the error still persists.

Just some thougths.

Regards

Sven

[Former Member]

DB is using for data exchange between SAP R/3 (PI) on one side and third POS system on second side, so it use frequently used from two sides. It could happend that PI didn't alone produce this error, but....

Also I would like not to switch off Batch processing, because of performance. Any other idea?

[Former Member]

Hi Mario,

have you verified that all cursors are properly closed after processing? You should monitor the oracle database (with toad) and see if the amount of cursors is reduced after a time, or if this value is only increasing. That would mean, some application, or your StoredProcedure does not close the cursor.

By the way, why do you need cursors while inserting data ? I assume you execute a stored procedure and you need to do some further processing of the data?

Regards

Sven

[Former Member]

I tried to switch batch mode off, but no luck. Actually, DB is used from PI for writing and from other system which is reading DB and updates status. I'm not using stored proc. but I'm using prepared statement, because I'm inserting in some field large XMLs as CLOB, which can be more than 4000 chars and therefore I have to use prepared statement. And my com. channel is increasing opened cursors with every insert, and when I reach 5K it is over. If I restart com.channel, simple stop and start, session on DB is deleted, cursor also and I can work further but this is not the solution.

Any other idea?

kr

mario

[Former Member]

Is there maybe way to change behaviour of com.channel with some customer module? Can it be done?

[Former Member]

Solution is to use "Disconnect after each message processing", but how much slower will it be then?

[Former Member]

Solution is to use "Disconnect after each message processing", but how much slower will it be then?

How often you expecting messages to be sent to DB? If there is an interval of more than 60 secs between each message, then using this option has not much impact on the performance.

Regards,

Neetesh

[Former Member]

We will run change pointers on IDOCs every 10-15 minutes, after this time there will be certain amount of IDOCs to write to DB, how much hard to tell, it is SAP Retail to POS, so I'd say significant load

[Former Member]

Mario,

This sounds like a heavy load and I might not recommend using this option. But I would like to wait and hear from other experts on this issue.

Regards,

Neetesh

[Former Member]

Not acceptable, I've tested today and it takes to long....to to long....

[Former Member]

What If I use BPM, collect IDOCs and deliver this bunch every 5 min with Disconnect after each message processing.

What do you think abou that - performance?!

kr

mario