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

3
votes

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?

1
web-servicesoraclejakarta-eetransactionsweblogic
why using auto commit? In general not the best way to work with your database. (do things like audit/logging take place within the procedure/functions that you use?) – ik_zelf
Is the 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 a where 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 Poole
@ik_zelf We're using the default configuration for connection pooling that comes with WebSphere. We have been playing with auto-commit in an attempt to fix this issue. – Carlos Gavidia-Calderon
@AlexPoole how do I identify a function that's declared safe with pragma? – Carlos Gavidia-Calderon
Actually I'm not sure it would even need a pragma. You can have a function that just does a commit 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

1 Answers

3
votes

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.