2 Replies Latest reply: Jul 24, 2013 8:15 PM by rp0428 RSS

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

    Narashima Dharma

      I am getting below error, can anyone please suggest how to correct the below error, and in the function I want return 1 or 0, because this function will be called from front end if insert is successfully I have return 1 or if insert failed then I have return 0

      CREATE TABLE TEST_CLOB
      (
      ID NUMBER,
      FILE_NAME CLOB
      );

      SQL> create or replace function fn_clob(p_id number, p_file_name clob)
      2 return number is
      3 pragma autonomous_transaction;
      4 begin
      5 insert into test_clob(id,file_name) values(p_id,p_file_name);
      6 return 1;
      7 exception
      8 when others then
      9 rollback;
      10 return 0;
      11 end;
      12 /

      Function created.

      SQL> select fn_clob(200,'dsfafasdffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffasfdasdfsadf') from dual
      2 /
      select fn_clob(200,'dsfafasdffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffasfdasdfsadf') from dual
      *
      ERROR at line 1:
      ORA-14552: cannot perform a DDL, commit or rollback inside a query or DML
      ORA-06512: at "CRIS_ADJUSTMENT_USER.FN_CLOB", line 9
      ORA-06519: active autonomous transaction detected and rolled back

        • 1. Re: ORA-14552: cannot perform a DDL, commit or rollback inside a query or DML
          Clearance 6`- 8``

          It is because, you mentioned rollback in case of an exception, but not when there is a successful call. Autonomous transaction means, it creates a separate session for itself when called and the basic purpose it was introduced was to achieve certain tasks that you could not do in a transaction, for eg. logging. Have a commit or rollback before the return statement should solve your issue.

          • 2. Re: ORA-14552: cannot perform a DDL, commit or rollback inside a query or DML
            rp0428

            That is a TERRIBLE thing to want to do for many reasons.

             

            1. use of WHEN OTHERS - that completely hides the real problem from everyone. No one will ever know just what went wrong.

            2. you are using PL/SQL to do a job that SQL can do

            3. you are HARD CODING a query into the function. That eliminates scalability and makes maintenance more difficult.

            4. that is NOT a proper use for autonomous transactions.

            5. you are using a SELECT query to alter the database. That hides the true purpose of the function and makes it easy to misuse the function.

             

            Abandon that idea and either do the job using pure SQL or use a normal procedure/function to perform the transaction.

             

            Your exception is raised because an autonomous transaction needs to perform either a COMMIT or a ROLLBACK before it returns to the caller.

             

            This works for me:

            >

            CREATE OR REPLACE function SCOTT.fn_clob(p_empno number)

            return number is

            pragma autonomous_transaction;

            begin

            insert into emp_copy (empno) values (p_empno);

            commit;  -- need a COMMIT or ROLLBACK before the function returns

            return 1;

            exception

            when others then

            rollback;

            return 0;

            end;

            /

            >