[Date Prev][Date Next][Thread Prev][Thread Next]
[Author Index] [Date Index] [Thread Index]
[SQR-USERS Info] [SQRUG Home Page]
Re: SQR Error NLS_DATE_FORMAT Resolution

Subject: Re: SQR Error NLS_DATE_FORMAT Resolution
From: Tony DeLia <tdelia@EROLS.COM>
Date: Wed, 23 Dec 1998 10:10:37 -0500
Organization: Tony DeLia
References: <199811290025.TAA03265@shire.ontko.com> <3663009E.DA7@erols.com>
*** Here is an update on the NLS_DATE_FORMAT scenario I described
earlier... See 'SQR Error' threads from End of November'98 for
additional background. ***

I've received several requests to forward any information on this
problem so here it is... It is for ORACLE only...

Brief Description:
While running SQR in PeopleSoft the NLS_DATE_FORMAT is set in setenv.sqc
then it may randomly revert back to the default date format... This
could result in 'ORA-01830 Date Picture Format.... ' Errors even though
dates "appear" to be correct...

Findings:
It has been confirmed this is a known bug in Oracle... the version we
are running is 7.3.2.2.0 - There are fixes that need to be applied that
should correct the problem... It's also recommended to set the default
system date format to that used in the setenv.sqc routine (this does
defeat the purpose of the routine though)...

That's all the information I can supply... For those who've encountered
this extremely annoying problem it's nice to know we're not crazy!!!

                              -Tony DeLia

Tony DeLia wrote:
>
> Hello,
>
> It's quite a coincidence this 'ORA-01830 Date Picture Format' topic has
> come up... I've recently encountered this under some strange
> circumstances... Hopefully a "seasoned" Oracle DBA will read this and
> provide some insight on what's happening... Here's the details...
>
> - SQR and COBOL processes (in Production) "randomly" generate ORA-01830
> Errors (I'll focus on the SQR programs).
> - The SQR programs use SETENV.SQC and alters the NLS_DATE_FORMAT using
> 'DD-MON-YYYY'...
> - $AsOfDate is set at the beginning of the program in 'DD-MON-YYYY'
> format and does not change throughout the life of the program...
> - $AsOfDate is used in a number of SQL statements (SELECT, INSERT, etc)
> without a problem (because it is indeed a valid date)...
> - Thousands of rows are processed using $AsOfDate without fail...
> - Without warning the NLS_DATE_FORMAT set by SETENV.SQC is "lost"...
> Oracle reverts back to it's 'DD-MON-YY' format...
> - Since $AsOfDate is set in the beginning of the program as
> 'DD-MON-YYYY' it is "suddenly" invalid due to the mysterious date format
> change...
> - The client informed me they experienced the exact problem a year
> earlier without resolution... The problem has returned and I feel it
> should be dealt with... to change each and every SQR (and COBOL) process
> to accomodate this anomaly would be insane!!!
> - In the past they've added workarounds that weren't very effective and
> altered the original coding... Specifically they added a counter that
> re-executes the ALTER SESSION command every 100 rows/records... If the
> unpredicatable date format change occurs within 100 the SQL statement
> fails... they would run their process(es) over and over until it
> completed successfully (ignoring the real problem)... that's NUTS!!!!
>
> - As a "temporary" work-around to remain productive during this time I
> added TO_DATE functions against any date variables whenever possible...
> The SELECT statement changes were obvious... Here's a workaround for
> INSERTS that use the VALUE clause...
>
> Replace - INSERT INTO table
>           VALUES ($AsOfDate,
>                   $PayEndDt);
>
> With    - INSERT INTO table
>           SELECT TO_DATE($AsOfDate,'{Native-Date}'),
>                  TO_DATE($PayEndDt,'{Native-Date}')
>             FROM DUAL;
>
> Replacing the VALUES clause with a SELECT clause against the DUAL table
> allows you to use the TO_DATE function.
>
> Keep in mind... for this particular problem (where the NLS_DATE_FORMAT
> is lost before the end of the session)... the solution IS NOT coding
> workarounds - rather finding why the NLS_DATE_FORMAT is lost...
>
> Has anyone experienced this same problem??? and more importantly
> resolved it?!!!
>                                 -Tony DeLia
>
> Ray Ontko wrote:
> >
> > Bill,
> >
> > I'm guessing that the problem is in your where clause.
> > If you want to compare a date variable to a string variable,
> > do an explicit conversion of the string to a date.
> > For example:
> >
> > >                         AND Effdt <= $ThruDate)
> >
> > should perhaps be
> >
> >                           AND effdt <= to_date($thrudate,'YYYYMMDD')
> >
> > The error you're getting is Oracle complaining that the default
> > date format (probably 'dd-mon-yy' on your system) doesn't match
> > the value you're passing.
> >
> > Hope this helps.
> >
> > Ray
> > >      I am executing the following select and receiving the following sqr
> > >      error.  Anybody have any ideas?
> > >
> > >      Bill
> > >
> > >      (Bowersb@psph.providence.org)
> > >
> > >
> > >
> > >      (SQR 5528) ORACLE OFETCH error -1830 in cursor 2:
> > >         ORA-01830: date format picture ends before converting entire input
> > >      string
> > >
> > >      Error on line 240:
> > >         (SQR 3725) Bad return fetching row from database.
> > >
> > >      SQRW: Program Aborting.
> > >
> > >
> > >      I am using Oracle and SQR version 3.
> > >
> > >      Note: Line 240 is the begin-select statement
> > >
> > >      begin-SELECT  loops=50
> > >       show '100 **************************************'
> > >      !Personal Data
> > >      PD.Name
> > >       show '100 &pd.name: ' &pd.name
> > >      PD.SSN
> > >      PD.Address1
> > >      PD.Address2
> > >      PD.City
> > >      PD.State
> > >      PD.ZIP
> > >      PD.Sex
> > >      PD.Birthdate
> > >      !to_char(PD.Birthdate, 'YYYYMMDD')    &Birthdate
> > >      PD.Emplid
> > >      !Employment
> > >      E.Hire_Dt
> > >      !to_char(E.Hire_Dt, 'YYYYMMDD')       &Hire_dt
> > >      E.Termination_dt
> > >      !to_char(E.Termination_Dt, 'YYYYMMDD')    &Termination_dt
> > >
> > >      !Job
> > >      J.Emplid
> > >      J.Empl_Rcd#
> > >      J.Company
> > >      J.Empl_Status
> > >      J.Action_Dt
> > >      J.Effdt
> > >      J.Action
> > >       !show '100 &j.action: ' &J.action
> > >      J.Action_Reason
> > >       !show '100 &j.action_reason: ' &J.action_reason
> > >
> > >      !Health_benefit
> > >      HB.Plan_Type
> > >        do 1200-Write-Emp  !Builds detail record
> > >
> > >      HB.Benefit#
> > >      hb.coverage_elect_dt
> > >      hb.coverage_begin_dt
> > >      hb.coverage_elect
> > >      HB.Benefit_Plan
> > >      HB.Covrg_Cd
> > >
> > >      !Benefit Plan
> > >      bp.Plan_type
> > >
> > >
> > >      HB.Effdt
> > >      !to_char(HB.Effdt, 'YYYYMMDD')    &Effdt
> > >      !to_char(HB.Coverage_Begin_Dt, 'YYYYMMDD')    &covrg_begin_dt
> > >      !to_char(hb.Coverage_Begin_Dt, 'MM/DD/YYYY')  &Print_Coverage_Date
> > >
> > >      BP.Provider
> > >
> > >
> > >      FROM  PS_Personal_Data  PD,
> > >            PS_Employment     E,
> > >            PS_Job            J,
> > >            PS_Health_Benefit HB,
> > >            PS_Benef_Plan_Tbl BP
> > >
> > >      WHERE J.Effdt = (SELECT MAX(Effdt) FROM PS_Job
> > >                        WHERE Emplid    = J.Emplid
> > >                          AND Empl_Rcd# = J.Empl_Rcd#
> > >                         AND Effdt <= $ThruDate)
> > >                          !AND Effdt <= $AsOfDate
> > >                         !and Action_dt between $FromDate and $ThruDate)
> > >                          !and Action_dt >= $FromDate)
> > >
> > >        AND J.Effseq = (Select MAX(Effseq) FROM PS_Job
> > >                         WHERE Emplid    = J.Emplid
> > >                           AND Empl_Rcd# = J.Empl_Rcd#
> > >                           AND Effdt     = J.Effdt)
> > >        AND J.Empl_Rcd# = 0
> > >        AND J.Company in [$Company_Reqstd]
> > >        AND E.Emplid = J.Emplid
> > >        AND E.Empl_Rcd# = 0
> > >        AND PD.EmplID = J.EmplID
> > >        AND HB.EmplID = J.EmplID
> > >        AND HB.Empl_Rcd# = J.Empl_Rcd#
> > >        AND HB.Benefit# = 0
> > >       AND HB.Effdt = (SELECT Max(Effdt)
> > >                        FROM PS_Health_Benefit
> > >                       WHERE Emplid    = HB.Emplid
> > >                         AND Empl_Rcd# = HB.Empl_Rcd#
> > >                         AND Plan_Type = HB.Plan_Type
> > >                         AND Benefit#  = HB.Benefit#
> > >                         !and coverage_begin_dt is not null
> > >                         and coverage_begin_dt < = $ThruDate)
> > >                         !and effdt < = $ThruDate)
> > >
> > >         AND HB.Coverage_Elect = 'E'
> > >        AND (HB.Coverage_End_Dt IS NULL  OR
> > >             HB.Coverage_End_Dt > $ThruDate)
> > >        AND BP.Plan_Type    = HB.Plan_Type
> > >        AND BP.Benefit_Plan = HB.Benefit_Plan
> > >        AND BP.EffDt = (SELECT Max(EffDt)
> > >                         FROM PS_Benef_Plan_Tbl
> > >                        WHERE Plan_Type    = BP.Plan_Type
> > >                          AND Benefit_Plan = BP.Benefit_Plan
> > >                          and effdt < = $ThruDate)
> > >
> > >        AND BP.Provider = 'FRSTHE'
> > >
> > >      ORDER BY PD.Name, HB.Plan_Type,HB.Benefit_Plan
> > >
> > >      end-SELECT
> > >
> > >      end-procedure
> > >
> >
> > ----------------------------------------------------------------------
> > Ray Ontko       |  Ray Ontko & Co  |  "Time for a new signature line."
> > rayo@ontko.com  |  Richmond, In    |  See us at http://www.ontko.com/
>
> --
> Tony DeLia
> AnswerThink Consulting Group
> PeopleSoft Solutions Practice - Delphi Partners
> tdelia@erols.com

--
Tony DeLia
AnswerThink Consulting Group
PeopleSoft Solutions Practice - Delphi Partners
tdelia@erols.com
References:
- Re: SQR Error
  - From: Ray Ontko <rayo@ONTKO.COM>
- Re: SQR Error
  - From: Tony DeLia <tdelia@EROLS.COM>
Prev by Date: Re: Output formatting for HP printers from client side
Next by Date: Re: Output formatting for HP printers from client side
Prev by thread: Re: SQR Error
Next by thread: Re: SQR Error
Indexes: [ Thread] [ Author] [ Date]
SQR-USERS List and Archive Info
SQRUG Home Page