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.
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 thepragma autonomous_transaction
, but it would be better to avoid it. – Nick Krasnov