We have some Web Services deployed in Oracle WebLogic Servers, and the main responsability of this services is to invoke Stored Procedures and sent this data to the clients. The technology stack of the Services is:
- JAX-WS
- Spring Framework
- MyBatis
The Services gets connections to the Database from the Connection Pool offered by WebLogic. For months the Services were running fine but this days we're having the following problem:
SERVER: WLSDesa_ManagedServer1 [DEBUG] [15-05-2013 12:23:03.897] (JakartaCommonsLoggingImpl.java:46) - ooo Using Connection [weblogic.jdbc.wrapper.PoolConnection_oracle_jdbc_driver_T4CConnection@59bd]
SERVER: WLSDesa_ManagedServer1 [DEBUG] [15-05-2013 12:23:03.898] (JakartaCommonsLoggingImpl.java:46) - ==> Preparing: { call package.iOnlyDoASelect( ?, ?) }
a.package.from.project.CommonException: org.springframework.jdbc.UncategorizedSQLException:
### Error querying database. Cause: java.sql.SQLException: ORA-14552: cannot perform a DDL, commit or rollback inside a query or DML
### The error may exist in a/package/from/the/project/ImAMyBatisMap.xml
### The error may involve a.package.from.the.project.ImADaoClass.invokeProcedure-Inline
### The error occurred while setting parameters
### SQL: { call package.iOnlyDoASelect( ?, ?) }
### Cause: java.sql.SQLException: ORA-14552: cannot perform a DDL, commit or rollback inside a query or DML
; uncategorized SQLException for SQL []; SQL state [72000]; error code [14552]; ORA-14552: cannot perform a DDL, commit or rollback inside a query or DML
; nested exception is java.sql.SQLException: ORA-14552: cannot perform a DDL, commit or rollback inside a query or DML
The procedure is only a Select
to several tables, and we can invoke it from another Java Applications and Database Clients normally; and in the Service we're not using any explicit transaction management code. The issue happens sporadically and makes the Service useless. The workarround we have is either restart WebLogic Server or turn auto-commit off and then to on. This happens almost 5 times a day.
Any clues? Is WebLogic related, a Database Issue o it's Web-Service code?
select
inside the package calling any functions, which might have been declared safe with a pragma but actually are doing something they shouldn't? Sounds unlikely from what you've said, but could be intermittent if the call is in awhere
clause and isn't always reached. Also don't think it quite matches, but when it happens are you getting a newly-created connection from the pool, and do you have a logon trigger that commits? (Clutching at straws, clearly...) – Alex Poolecommit
and include it in the query as long as it never gets evaluated; if it does the you'll get this error. (So it depends on the order and result of predicate evaluation). You could search all the source code looking for a commit/rollback/etc. but you don't know what's being called, so you probably need to start the other end and see what functions your query is calling, and see what they're calling, etc. You can narrow it down to functions that aren't always called, or try calling them stand-alone. – Alex Poole