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?

John Heinrich wrote:

> 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?

Does your Oracle have a version and edition?
Does it run on any hardware?
Is there an operating system?
If *NIX have you verified the kernel parameters?

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)

Daniel Morgan <damorgan@x.washington.edu> wrote in message news:<1083386243.147711@yasure>...
> John Heinrich wrote:
>
> > 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?
>
> Does your Oracle have a version and edition?
> Does it run on any hardware?
> Is there an operating system?
> If *NIX have you verified the kernel parameters?

Oracle version 8.1.6.1 EE
Windows 2000 SP5

John Heinrich wrote:

> Daniel Morgan <damorgan@x.washington.edu> wrote in message news:<1083386243.147711@yasure>...
>
>>John Heinrich wrote:
>>
>>
>>>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?
>>
>>Does your Oracle have a version and edition?
>>Does it run on any hardware?
>>Is there an operating system?
>>If *NIX have you verified the kernel parameters?
>
>
> Oracle version 8.1.6.1 EE
> Windows 2000 SP5

You have an unsupported version, almost paleolithic, on an operating
system for which it was never tested or certified.

The solution, it would seem to me, is pretty clear. Upgrade at a
minimum to 8.1.7.4 which begins desupport in December of this year or,
better yet, move to 9.2.0.4 or above.

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)

Daniel Morgan wrote:

[snip]

>
> You have an unsupported version, almost paleolithic, on an operating
> system for which it was never tested or certified.

Actually, 8.1.6 was indeed certified for Windows 2000. It was the first
release certified for that platform, actually.

> The solution, it would seem to me, is pretty clear. Upgrade at a
> minimum to 8.1.7.4 which begins desupport in December of this year or,
> better yet, move to 9.2.0.4 or above.

Well, he might be constrained to run on 8.1.6... in which case, I seem
tor ecall that 8.1.6.3 was the first (and only) stable release of 8.1.6.
Not that he'll readily be able to obtain the necessary upgrade packages
even so, of course.

Regards
HJR