Re: Same Procedure runs and fails intermittently

Ed Stevens <nospam_at_noway.nohow> wrote in message news:<6g99b0tj4343bbqmdla8a09rorr70ijvif_at_4ax.com>...
> On 26 May 2004 05:24:59 -0700, watuni2000_at_yahoo.co.nz (Tyler Smith
> Watu) wrote:
>
> >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')
>
>
> Do some occurances of t.start_time have nulls? And sometimes these
> are picked up by your WHERE clause? Just an off-the-cuff guess.

In response to D Rolfe ------
I am not sure if data should be at a transient stage at anytime. When you examine the ACID properties of an RDBMS Under consistency there should be no time when data is under an interim/transient stage.Data will be seen as under the initial or final stage.

 ED,s suggestion------- ,except I am
 missing something if a transaction starts there will be a start time if it does not it should not have a start_time of null.

Thanks for taking the time to respond

TY Received on Wed May 26 2004 - 13:04:18 CDT