Re: Same Procedure runs and fails intermittently

From: D Rolfe <dwrolfeFRUITBAT_at_orindasoft.com>
Date: Wed, 26 May 2004 13:52:41 +0100
Message-ID: <40B49319.7020709@orindasoft.com>

Tyler,

ORA-1843 is "Not a valid month". The column t.start_time is a VARCHAR2(20). There is no reason why it *has* to contain a string in the   form "MM/DD/YY:HH24:MI:SS". If I go to SQL*PLus and Issue:

select to_date(' ','MM/DD/YY:HH24:MI:SS') from dual /

I get:

ERROR at line 1:
ORA-01843: not a valid month

This would make me suspect that you are seeing some kind of transient value which isn't null and isn't a fully formatted date.

If you are running this as a script why not have a query that does:

SELECT 'x'||t.start_time||'x' FROM v$transaction t;

at the start. Sooner or later you'll see an example of the value that is causing you trouble.

David Rolfe
Orinda Software
Dublin Ireland

---

orindabuild automates JDBC development.

> HP Unix 11.0
> Oracle 8.1.7.4
> I have a procedure which runs OK sometimes and errs out at other
> times with an ORA-1843. This would have been expected to fail
> consistently if there was a date mismatch not only occasionally .The
> procedure was created with no errors.
>
> The procedure in question is called via a shell script (if that helps
> any)
>
> Your input will be greatly appreciated?
>
> TY
>
>
>
> if the code will be of any help the heart of the code is here
>
>
> select (sysdate-to_date(t.start_time,'MM/DD/YY:HH24:MI:SS'))
> ,s.sid,s.serial#,
> replace(substr(s.client_info,0,8),',')
> Name from v$transaction t,v$session s
> where s.taddr=t.addr and to_date(t.start_time,
> 'DD/MM/YY:HH24:MI:SS') < (SYSDATE-(1/(96)))
> and substr(s.client_info,0,2) not in ('PS')
Received on Wed May 26 2004 - 07:52:41 CDT