Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

URGENT:::Can DML operation "insert" works inside a PL/SQL stored function ?

 
Raj Sikka
Ranch Hand
Posts: 34
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi ,
I am working with PL/SQL functions .
I am trying to execute a simple "insert" statement through a PL/SQL function.
It is giving following error:
"Cannot perform DML operations inside a query".
It works fine with a PL/SQL procedure.
here is a sample code:
-----------------------------------------------
create or replace procedure testQueryProc(myarg number) is
begin
testQuery(1);
end testQueryProc;
/

create or replace procedure testQuery(arg number)is
i number(2);
read_id1 number(2);
read_id2 number(2);
begin
select id1 into read_id1 from testTable where id2=99;
dbms_output.put_line('id1 value is : '||read_id1);
for i in 1..10 loop
insert into testTable values(i,i+1);
end loop;
end testQuery;
/
above code works fine .
-----------------------------------------------
but when i try below code it says above mentioned error:
-------------------------------------------
create or replace function testQueryFunc(myarg number) return number as
begin
testQuery(1);
return 1;
end testQueryFunc;
/
create or replace procedure testQuery(arg number)is
i number(2);
read_id1 number(2);
read_id2 number(2);
begin
select id1 into read_id1 from testTable where id2=99;
dbms_output.put_line('id1 value is : '||read_id1);
for i in 1..10 loop
insert into testTable values(i,i+1);
end loop;
end testQuery;
/
-----------------------------------------------
please clarify .
Thanks ,
Srikanth
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic