Background:
Dev database - Solaris 2.x, Oracle 8.1.7.1
Test database - Solaris 8.x, Oracle 8.1.7.1

Issue: I have a stored procedure which contains the following
assignment:
v_date := SYSDATE;

This procedure runs fine on our Dev database, but when we tried to run
it on the Test database (note environment differences above) it makes
the stored procedure throw off errors and eventually I receive the
generic ORA-03113 end-of-file communication channel error.

On the Test database, I changed this SP code to perform a more
traditional assignment of the date:
Select SYSDATE into v_date from dual;

This works fine on the Test database.

Any ideas as to why the first assignment of SYSDATE is causing errors
on my Test database?

bunky wrote:

> Background:
> Dev database - Solaris 2.x, Oracle 8.1.7.1
> Test database - Solaris 8.x, Oracle 8.1.7.1
>
> Issue: I have a stored procedure which contains the following
> assignment:
> v_date := SYSDATE;
>
> This procedure runs fine on our Dev database, but when we tried to run
> it on the Test database (note environment differences above) it makes
> the stored procedure throw off errors and eventually I receive the
> generic ORA-03113 end-of-file communication channel error.
>
> On the Test database, I changed this SP code to perform a more
> traditional assignment of the date:
> Select SYSDATE into v_date from dual;
>
> This works fine on the Test database.
>
> Any ideas as to why the first assignment of SYSDATE is causing errors
> on my Test database?

I suspect that the "default" date format is different between the 2 DBs.
I suspect that "v_date" is of datatype VARCHAR2 and not of type DATE;
which is forcing an implicit datatype conversion (which is failing).

"anacedent" <anacedent@hotmail.com> wrote in message
news2Eyc.63290$tI2.52985@fed1read07...
> bunky wrote:
>
> > Background:
> > Dev database - Solaris 2.x, Oracle 8.1.7.1
> > Test database - Solaris 8.x, Oracle 8.1.7.1
> >
> > Issue: I have a stored procedure which contains the following
> > assignment:
> > v_date := SYSDATE;
> >
> > This procedure runs fine on our Dev database, but when we tried to run
> > it on the Test database (note environment differences above) it makes
> > the stored procedure throw off errors and eventually I receive the
> > generic ORA-03113 end-of-file communication channel error.
> >
> > On the Test database, I changed this SP code to perform a more
> > traditional assignment of the date:
> > Select SYSDATE into v_date from dual;
> >
> > This works fine on the Test database.
> >
> > Any ideas as to why the first assignment of SYSDATE is causing errors
> > on my Test database?
>
> I suspect that the "default" date format is different between the 2 DBs.
> I suspect that "v_date" is of datatype VARCHAR2 and not of type DATE;
> which is forcing an implicit datatype conversion (which is failing).

Oops - I forgot to state in my original post that v_date is declared as DATE
datatype, which makes this even more baffling to me.

KPATTON@indy.rr.com wrote:

> "anacedent" <anacedent@hotmail.com> wrote in message
> news2Eyc.63290$tI2.52985@fed1read07...
>
>>bunky wrote:
>>
>>
>>>Background:
>>>Dev database - Solaris 2.x, Oracle 8.1.7.1
>>>Test database - Solaris 8.x, Oracle 8.1.7.1
>>>
>>>Issue: I have a stored procedure which contains the following
>>>assignment:
>>>v_date := SYSDATE;
>>>
>>>This procedure runs fine on our Dev database, but when we tried to run
>>>it on the Test database (note environment differences above) it makes
>>>the stored procedure throw off errors and eventually I receive the
>>>generic ORA-03113 end-of-file communication channel error.
>>>
>>>On the Test database, I changed this SP code to perform a more
>>>traditional assignment of the date:
>>>Select SYSDATE into v_date from dual;
>>>
>>>This works fine on the Test database.
>>>
>>>Any ideas as to why the first assignment of SYSDATE is causing errors
>>>on my Test database?
>>
>>I suspect that the "default" date format is different between the 2 DBs.
>>I suspect that "v_date" is of datatype VARCHAR2 and not of type DATE;
>>which is forcing an implicit datatype conversion (which is failing).
>
>
> Oops - I forgot to state in my original post that v_date is declared as DATE
> datatype, which makes this even more baffling to me.

Not if, as Ana C. suggests the date formats are different. Check all of
the related information in v_$parameter.

Might also want to patch to 8.1.7.4 while you are at it.

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

bunky wrote:
>
> Background:
> Dev database - Solaris 2.x, Oracle 8.1.7.1
> Test database - Solaris 8.x, Oracle 8.1.7.1
>
> Issue: I have a stored procedure which contains the following
> assignment:
> v_date := SYSDATE;
>
> This procedure runs fine on our Dev database, but when we tried to run
> it on the Test database (note environment differences above) it makes
> the stored procedure throw off errors and eventually I receive the
> generic ORA-03113 end-of-file communication channel error.
>
> On the Test database, I changed this SP code to perform a more
> traditional assignment of the date:
> Select SYSDATE into v_date from dual;
>
> This works fine on the Test database.
>
> Any ideas as to why the first assignment of SYSDATE is causing errors
> on my Test database?

817 was the first release for a while I think to start getting sysdate
via a direct call (and thus avoid the overhead of an implicit 'select
from dual'). Presumably 8171 had some probs with the transition - I can
vouch for the fact that 8174 solaris works fine in this regard

hth
connor
--
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
ISBN: 1590592174

web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald@yahoo.com

Coming Soon! "Oracle Insight - Tales of the OakTable"

"GIVE a man a fish and he will eat for a day. But TEACH him how to fish,
and...he will sit in a boat and drink beer all day"

------------------------------------------------------------