Re: SYSDATE problem - Oracle - Solaris

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Sat, 12 Jun 2004 13:18:58 -0700
Message-ID: <1087071553.763366@yasure>

KPATTON_at_indy.rr.com wrote:

> "anacedent" <anacedent_at_hotmail.com> wrote in message
> news:D2Eyc.63290$tI2.52985_at_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/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)

Received on Sat Jun 12 2004 - 15:18:58 CDT