ORA-14552: cannot perform a DDL, commit or rollback inside a query or DML

Question

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?

Answer 1

Following on from a comment, this isn't an answer, but shows an example of what could be happening, if your query is calling a function at some point.

create function f42 return number as
begin
    commit;
    return 0;
end;
/

Function created.

SQL> select * from dual where extract(day from sysdate) = 16 or f42 = 0;

D
-
X

This is fine because today is the 16th, so the first part of the or is true and the second part doesn't need to be evaluated; so the function is not called. Changing just the day I'm looking for:

SQL> select * from dual where extract(day from sysdate) = 15 or f42 = 0;

select * from dual where extract(day from sysdate) = 15 or f42 = 0
                                                           *
ERROR at line 1:
ORA-14552: cannot perform a DDL, commit or rollback inside a query or DML
ORA-06512: at "STACKOVERFLOW.F42", line 3

This time the first part of the or is false, so it does call the function, which throws the error.

But the error stack shows you where the problem is really coming from, unless of course you're catching (and squashing) the stack, or your client isn't reporting it. Calling the package procedure directly, from SQL*Plus, would show the whole stack - assuming you know the parameters that cause the problem and you aren't squashing the error.

But it isn't clear if the problem only affects certain parameter values, or is based on something transient (like sysdate), or indeed is caused by something else entirely. I'd start by seeing if you can replicate it reliably like this though, and if you can then the error stack should give you a better of idea what's happening.