Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.
Course Of The Month
7 days, 21 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
write a function to call a procedure to insert values into a table in PL SQL
Posted on 2016-09-25
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
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
2
-
2
- +1
7 Comments
Active 3 days ago
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?
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 .
Active today
I cannot speak for SQL Server, but in Oracle, you most certainly can do dynamic SQL and DDL/DML within a function. How you call it determines whether or not that is legal.
You can also call a procedure from within a function. Again, how you call it determines whether that is legal or not.
Simple example:
Judging by the original post stating that this is a report, my guess is that they are executing the function the second way, which would cause an error like the one they are seeing.
You can also call a procedure from within a function. Again, how you call it determines whether that is legal or not.
Simple example:
create or replace procedure check_ret2 as
cnt pls_integer;
begin
select count(1) into cnt from user_tables;
end;
/
create or replace function check_ret return number as
begin
execute immediate 'create table z (x number)';
check_ret2();
return 0;
end;
/
declare
retval number;
begin
retval := check_ret();
end;
/
select check_ret() from dual;
Judging by the original post stating that this is a report, my guess is that they are executing the function the second way, which would cause an error like the one they are seeing.
Thank you johnsone. I have found an alternate solution for the same
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
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
Active today
Sorry, we cross posted. I was writing my case up while you were posting your other comments.
Yes, AUTONOMOUS_TRANSACTION will allow you to do that. As long as you are aware of the consequences. It is a separate transaction and if the main transaction is rolled back, the autonomous transaction is not. Also, because it is a separate transaction outside of your select statement and function call, since the select was issued first, it will not be able to see the data from the autonomous transaction. Those are the main ones that apply to what you are doing.
Yes, AUTONOMOUS_TRANSACTION will allow you to do that. As long as you are aware of the consequences. It is a separate transaction and if the main transaction is rolled back, the autonomous transaction is not. Also, because it is a separate transaction outside of your select statement and function call, since the select was issued first, it will not be able to see the data from the autonomous transaction. Those are the main ones that apply to what you are doing.
Active today
Creating tables inside procedures is rarely needed in Oracle. Especially temporary ones like you seem to be describing. There is almost always a better way. You also need to worry about only one person executing the code at a time.
If you can explain a little more about your specific requirements we can probably offer alternatives.
I would look at one of the collection types offered. This keeps everything in the procedure/function and in most cases, in memory.
http://docs.oracle.com/database/121/LNPLS/composites.htm#LNPLS005
In very rare cases, you might need temporary tables but even then, you don't create them in the code. You use Oracle's Global Temporary tables. You create them once and just keep reusing them in code.
http://docs.oracle.com/database/121/ADMIN/tables.htm#ADMIN11633
If you can explain a little more about your specific requirements we can probably offer alternatives.
I would look at one of the collection types offered. This keeps everything in the procedure/function and in most cases, in memory.
http://docs.oracle.com/database/121/LNPLS/composites.htm#LNPLS005
In very rare cases, you might need temporary tables but even then, you don't create them in the code. You use Oracle's Global Temporary tables. You create them once and just keep reusing them in code.
http://docs.oracle.com/database/121/ADMIN/tables.htm#ADMIN11633
Featured Post
This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows syntax for various backup options while discussing how the different basic backup types work. It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables.
Make a table:
Update a specific column given a specific row using the UPDATE statement:
Remove a set of values using the DELETE s…
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month7 days, 21 hours left to enroll
617 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.