I am trying to create a SQL report with complex calculation and even though i created a procedure and function with the complete logic when i am trying to call my function to execute the procedure which has to insert records into my table its throwing an error
ORA-14551: cannot perform a DML operation inside a query
But if i run the procedure explicitly the insertion operation is taking place. But my integrator only accepts function not procedure kindly help me out with this . this is the sample structure i am following
function ()
declare
...
...
..
begin
procedure(parameter);
..
..
..
end
procedure (parameter)
declare
..
..
..
begin
insert into table_name values (....);
or
EXECUTE IMMEDIATE insert into table_name values (....);
...
end
I have tried both the ways to insert but still its not working
create or replace PROCEDURE RECURRENCE_WEEK(inE# IN NUMBER := NULL) AS
PRAGMA AUTONOMOUS_TRANSACTION;
the key word PRAGMA AUTONOMOUS_TRANSACTION helps to perform DML operations in function and procedure