We have had this problem as well, but believe we have worked around it.
We are an Oracle database. On the PC level, we have the following lines
included in our ORACLE.INI.: NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
NLS_DATE_FORMAT=DD-MON-YYYY
On the Database level, you must have both of these lines...or neither of
them. Believe it or not, the NLS_LANG will have an effect on the was
the date is used. If you have both of these lines, there should be no
problem. If you have neither, the date format relies on what your op
system is set to, in our case Unix. At that level your year should be
in the format YYYY, not RR.
Raymond M. Riepenhoff Lockheed Martin Utility
Services, Inc.
Computing Specialist PO Box 628
Information Technology XT-801, MS 6001
Business Applications Support Piketon, OH 45661
Phone: (740) 897-2331 ext 5842
FAX: (740) 897-2066 e-mail: riepenhoffrm@lmus.com
-- BEGIN included message
- Subject: Re: SQR Error
- From: Tony DeLia <tdelia@EROLS.COM>
- Date: Mon, 30 Nov 1998 15:31:26 -0500
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
-- END included message