[Date Prev][Date Next][Thread Prev][Thread Next]
[Author Index]
[Date Index]
[Thread Index]
[SQR-USERS Info]
[SQRUG Home Page]
Re: SQR Error
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