aspose file tools*
The moose likes JDBC and the fly likes URGENT:::Can DML operation Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "URGENT:::Can DML operation "insert" works inside a PL/SQL stored function ?" Watch "URGENT:::Can DML operation "insert" works inside a PL/SQL stored function ?" New topic
Author

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

Raj Sikka
Ranch Hand

Joined: Apr 11, 2003
Posts: 34
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
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: URGENT:::Can DML operation "insert" works inside a PL/SQL stored function ?