ora 14552- Cannot use DDl, COMMIT Error

Question

I'm using this select statement inside an Oracle function this input is delivered to a Cursor for processing. When I use the where clause it is erroring out "ORA-14552, cannot perform a DDL, commit or rollback inside a query or DML" if I remove this it is working fine.

Select * 
  from TableName 
 where ((EFFECTIVE_DATE<= to_date(to_char(sysdate,'mm-dd-yyyy'),'mm-dd-yyyy')) 
   and (Effective_End_Date >= SYSDATE  OR Effective_End_Date = TO_DATE('01/01/0001','MM/DD/YYYY')))

actual Function:-

Create Function Name(in_name) Return varchar2
Is
Cursor S
IS Select v1,v2,v3 from tablename;
Begin 
    OPEN S
      Loop Fetch S into v_1, v_2, v_3;
         Exit when S%NOTFOUND;
        if (v_3 is null)
            Then For gm_rec in ( Select x,y,z,c from Tablename where
                        Tablename.x=V_1
                        and Tablename.y=V_2
                     )
            Loop
            RETURN(gm_rec.z)
            End Loop;

        if (v_3 is not null)
            Then For gm_rec1 in ( Select x,y,z,c from Tablename where
                        Tablename.x=V_1
                        and Tablename.y=V_2
                       )
            Loop
            RETURN(gm_rec1.c)
            End Loop;
                End IF;
           Close S;
       Commit;
END

Is there a way to overcome this error? I need to add the where clause to filter out old records.

Usually, the error ORA-14552 is raised when you are using a stored procedure that contains commit, in a select statement. You can work around this error by using the pragma autonomous_transaction, but it would be better to avoid it. — Nick Krasnov
You have another option you can use dynamic SQL to run the DML and TCL. — Gourabp

Colin 't Hart Colin 't Hart · Accepted Answer · 2013-09-26T20:51:01

I'm presuming that you're actually querying a view (or table with virtual column) and that the view (or virtual column) is calling a PL/SQL function.

Inside said PL/SQL function is some DML with a commit.

Either convert that function to an autonomous transaction, or don't do the commit, or don't do any DML in a function!

ora 14552- Cannot use DDl, COMMIT Error

1 Answers