Vignesh Marthandan
asked on
write a function to call a procedure to insert values into a table in PL SQL
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
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
Máté Farkas
Some notices:
- This is not an MSSQL problem it is in Oracle (tag list extended)
- In SQL Server or Oracle you cannot execute a procedure, dynamic sql or any DML/DDL operation inside a function
- Why do you want to call a procedure inside a function? You have to create a procedure not a function.
- How do you want to call your function?
ASKER
My DML operation is inside my procedure. I need to call my procedure using function to execute the DML statement. so is there any alternate way to execute my Procedure using select statement .
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.