dbms_job and "ORA-03113: end-of-file on communication channel"

From: John Heinrich <johnrasmus_at_ofir.dk>
Date: 30 Apr 2004 01:56:55 -0700
Message-ID: <48a74ba7.0404300056.41ef7460@posting.google.com>

ORA-03113 after job execution

I have to users, eg. user1 and user2

From an ASP web application a procedure (eg. proc1, owned by user1) is being called by user2. Proc1 is putting a job in the queue and the running it (eg. proc2 owned by user1).
Proc2 is inserting a record in a tabel on a different database through a db-link.

Create procedure proc1(param1 in varchar2, pCursor out Cursor) is   jobno number;
BEGIN
  some sql-strings is generated.
    if blah_1 then

      BEGIN 


        -- inserting a record in a log table

        dbms_job.submit(jobno,proc2(param1),sysdate); 
        dbms_job.run(jobno); 
      EXCEPTION 
        when others then 
          shit happens! 
      END; 

    end if;
    if blah_2 then

      BEGIN 


        -- inserting another record in another log table

        dbms_job.submit(jobno,proc2(param2),sysdate); 
        dbms_job.run(jobno); 
      EXCEPTION 
        when others then 
          shit happens! 
      END; 

    end if;
  open cursor for "the generated sql-string" EXCEPTION
  when others then

     shit happens!
END; When I run the procedure from a sql prompt as user2 it just works fine.
When I run it from ASP as user2, and only the first jobs is generated and runned, it just works fine, but if both jobs is submitted and runned then the ASP receives an ora-error: "ORA-03113: end-of-file on communication channel".
(both jobs is actually executed and the records is inserted on the other the database).

What is happening? Received on Fri Apr 30 2004 - 03:56:55 CDT