Solved

write a function to call a procedure to insert values into a table in PL SQL

Posted on 2016-09-25
7
41 Views
Last Modified: 2016-11-08
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
0
Comment
Question by:Vignesh Marthandan
[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
7 Comments
 
LVL 12

Expert Comment

by:Máté Farkas
ID: 41814635
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?
0
 

Author Comment

by:Vignesh Marthandan
ID: 41814644
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 .
0
 
LVL 35

Assisted Solution

by:johnsone
johnsone earned 332 total points
ID: 41814663
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:
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;

Open in new window

This shows a function that does dynamic DDL.  Then it calls a procedure.  If you execute from an anonymous block (the first method), it works just fine (this would be true if you were calling from any other type of PL/SQL object as well.  If you execute it the second way (within a select statement), then it will fail.

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.
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

Accepted Solution

by:
Vignesh Marthandan earned 2 total points
ID: 41814695
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
0
 
LVL 35

Assisted Solution

by:johnsone
johnsone earned 332 total points
ID: 41814732
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.
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 166 total points
ID: 41815103
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
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
push and Pull replication 31 46
Excess Redo 3 32
In SQL 2000 combine mulitple rows into a single row seperated by a semi colon 10 40
SQL Throw Error 7 32
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question