Home » SQL & PL/SQL » SQL & PL/SQL » cannot perform a dml operation inside a query (ORACLE 9i)
cannot perform a dml operation inside a query [message #275941] Tue, 23 October 2007 08:09 Go to next message
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 #275946 is a reply to message #275941] Tue, 23 October 2007 08:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

We don't download doc files.

Regards
Michel
Re: cannot perform a dml operation inside a query [message #276092 is a reply to message #275946] Tue, 23 October 2007 22:43 Go to previous message
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
Previous Topic: Trigger help
Next Topic: Distinct jobs
Goto Forum:
  


Current Time: Thu Feb 06 22:27:59 CST 2025