cannot perform a dml operation inside a query [message #275941] |
Tue, 23 October 2007 08:09  |
vkrmhj
Messages: 9 Registered: May 2007
|
Junior Member |
|
|
I have written a package body that executes two procedures in parallel (proctest1 and proctest2).Then if both procedures have executed i return the control(see sql query) to the place where the function(new_fun) was called which returns 'OK'.But i am getting the following error:cannot perform a dml operation inside a query: Pls. provide an solution to this.
-------------------------------------------------------------
create or replace package body new_pack is
procedure proctest1 is
v_tab1 tab1%ROWTYPE;
begin
select *
into v_tab1
from tab1
order by col1;
end proctest1;
procedure proctest2 is
v_tab2 tab2%ROWTYPE;
begin
select *
into v_tab2
from tab2
order by col1;
end proctest2;
function new_fun return varchar2 is
vJob1 number;
vJob2 number;
checkfortrue boolean := TRUE;
v_last_date user_jobs.last_date%type;
begin
dbms_job.submit(job => vJob1, what => 'proctest1;');
dbms_output.put_line('Job1 ' || to_char(vJob1));
dbms_job.submit(job => vJob2, what => 'proctest1;');
dbms_output.put_line('Job2 ' || to_char(vJob2));
<<whileloop>>
While checkfortrue loop
select last_date into v_last_date from user_jobs where job = vJob1;
If v_last_date IS NULL then
dbms_lock.sleep(5);
goto whileloop;
else
select last_date into v_last_date from user_jobs where job = vJob2;
If v_last_date IS NULL then
dbms_lock.sleep(5);
goto whileloop;
else
commit;
return ('ok');
end if;
end if;
End loop;
end;
begin
null;
--v_name :=fun_dj;
--dbms_output.put_line (v_name);
end new_pack; -------------------------------------------------------------
a package specification:
CREATE OR REPLACE package new_pack is
function new_fun return varchar2;
procedure proctest1;
procedure proctest2;
end new_pack;
-------------------------------------------------------------
and an sql to call the package function:
select new_pack.new_fun from dual;
----------------------------------------------------------
-
Attachment: error.doc
(Size: 26.50KB, Downloaded 791 times)
[Updated on: Tue, 23 October 2007 22:39] by Moderator Report message to a moderator
|
|
|
|
Re: cannot perform a dml operation inside a query [message #276092 is a reply to message #275946] |
Tue, 23 October 2007 22:43  |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
I don't think this is very good design - submitting background jobs from within a SELECT. But that's your problem, not mine.
I suspect the calls to DBMS_JOB.SUBMIT are the issue. They are inserting rows into a table, which is illegal when the function is initiated from a SELECT.
Lookup PRAGMA AUTONOMOUS_TRANSACTION in the doco. It should help.
My prediction, this is the first in a long line of issues that this technique will create for you.
Ross Leishman
|
|
|