cancel
Showing results for 
Search instead for 
Did you mean: 

MII 12.1 JTA Block errors (Oracle too many open cursors)

Former Member
0 Kudos

Hi Everyone,

Just wondering has anyone encountered issues with inserting multiple records into many tables using the custom JTA blocks in SAP MII 12.1 ?

I now seem to be getting errors returned back from Oracle saying there are too many open cursors

[ERROR] [MII_SQL_Commit] Error found trying to execute action 'JCM_JTAExecute' Exception: [Error invoking Transaction Command action: JTATransactionCommand - A SQL Command Exception occured against <NW JDBC Datasource> with the following command <SQL command here> The message was: ORA-01000: maximum open cursors exceeded]

(I've just edited out the particulars from my error message)

The SQL commands themselves are correct its just the maximum open cursors error which is the blocker.

I'm inserting roughly 80 records per table (with 4 tables each) I wouldn't think the DB commands that are being sent would use up one cursor per SQL command it executes is it?

Just throwing it out there if anyone has an idea, perhaps there is some configuration that I have missed that could solve this problem?

Regards,

Danny

Accepted Solutions (0)

Answers (3)

Answers (3)

Former Member
0 Kudos

Been trying to use this Sam but I keep getting this error in my transaction, is there a way to found out more info so I can fix this?

[ERROR] [MII_SQL_CommitBatch] Error found trying to execute action 'JTA_Add_Command_To_Batch_0' Exception: [Error invoking Transaction Add Command to Batch action: JTAAddCommandToBatch - while trying to invoke the method com.sap.mii.custom.actions.jta.JTAAccompaniamentData.mustRollback() of an object loaded from field com.sap.mii.custom.actions.jta.JTAPrepCmdAccompData.startAccompData of an object loaded from local variable 'this']

In my scenario we generate our SQL statements then have been passing to the JTA Execute blocks to run, I can't really use a prepared statement because its not always the same base SQL code with varying parameters (so it'll be a mix of inserts, updates, deletes etc.)

I tried just setting the prepared statement as "?" and in my add command to batch I added the full SQL statement into the SQL Value list input (hoping it'll just 'work') but I keep getting the error above and now I'm not sure what its doing...

I'm hoping you have some ideas

Former Member
0 Kudos

Looking into my Log Viewer I see these error:

SQL error occurred on connection SSMPER-MIISND\MII_DATABASES:MII_DB:dbo: code=0, state="<null>", message="The value is not set for the parameter number 1.";
SQL statement is "INSERT INTO MII_DB.[dbo].[Z_TestDB_DY] ([Name],[Value]) VALUES(?,?)".
[EXCEPTION]
com.microsoft.sqlserver.jdbc.SQLServerException: The value is not set for the parameter number 1.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)

I don't understand what's going on though because I think I've followed the guide correctly... but I can't get the parameters to be assigned to my prepared statement for it to run

0 Kudos

Danny,

I recently updated the actions to support batch statements as well; might be worth looking into to ease your pain

Sam

Former Member
0 Kudos

Hi Sam,

Just a question on this, the batch statements in the install guide talk about using batch prepared statements but can I just perform a normal batch statement as well? (ie. not using prepared statements)

As I have a collection of updates/inserts/deletes and I just want to reduce/control the usage of cursors in the Oracle JDBC driver, instead of using one cursor per statement being executed and performing commits to clear the cursor usage is really counterproductive as then I'll need to add all sorts of code to ensure my initial SQL statements are all atomically committed

shaji_chandran
Participant
0 Kudos

Please do a JTA Commit after each insert statements and try. I know this is a costly one but it will close all open cursors