Re: ?:TO_DATE: ORA-01843: not a valid month

Gary England wrote:
>
> pavelf01_at_mcrcr6.med.nyu.edu wrote:
> >
> > I'm developing a web app that includes macros (1 each for text, numeric, and
> > date form entry fields) that "build" the "set" and "where" clauses for the
> > SQL update syntax by concatenating all the updated fields appropriately
> > --including single quotes as needed for text fields; Oracle "to_date"
> > conversion of date fields. This works well for text and numeric fields, but
> > I keep getting the following Oracle error when I update a date:
> > Error: SQL request failed: ORA-01843: not a valid month
> >
> > Following is the app log file entry that shows the dumped value of
> > the SET clause that is concatenated which includes 1 date field that I
> > manipulate with the to_date function in order to have Oracle accept it
> > as a valid date field:
> > ********************************************************************************
> > DUMP: value of set_clause is medical.intake_dt = to_date('09/15/1993',
> > 'MM/DD/YYYY'),medical.pregnancy_nu = 3,medical.birth_control_fl = 'Y',
> > medical.birth_control_type = 'Orthonovum'
> > ********************************************************************************
> > I used the same statement format interactively in SQLPLUS to update a record
> > and it worked just fine:
> > ********************************************************************************
> > SQL> update medical
> > 2 set medical.intake_dt = to_date('08/12/1989','MM/DD/YYYY')
> > 3 where pat_id = 1;
> >
> > 1 row updated.
> >
> > SQL> select pat_id, intake_dt from medical;
> >
> > PAT_ID INTAKE_DT
> > ---------- ---------
> > 1 12-AUG-89
> > 3
> > 10
> > 4
> > 11 03-SEP-97
> > 11 15-SEP-88
> >
> > 6 rows selected.
> > ********************************************************************************
> >
> > If you happen to notice anything blatantly wrong in the syntax of my set
> > clause, or have any suggestions as to what else I can try to get Oracle to
> > accept my date fields, please let me know. Thanks for your help.
> >
> > +----------------------------------------------------------------------------+
> > |Frieda Pavel, Systems Mgr, Research Computing Resource, NYU Medical Center |
> > |E-Mail: PAVELF01_at_MCRCR.MED.NYU.EDU
> > +----------------------------------------------------------------------------+
>
> Does not look like the problem is in the clause you included in the
> posting. What host language are you using and what is in the WHERE
> clause?
>
> Gary England

I saw a similar problem which was
resolved by loading a newer version of
sqlnet on the client. No explanation
as to why.
Cheers,
Terry Received on Wed Sep 24 1997 - 00:00:00 CDT