Re: date insertion error

"Pizza Goldberg" <pizzagoldberg_at_hotmail.com> wrote in message news:M_OdnfRpC8vXM1-iRVn-vA_at_comcast.com...
|
| "Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message
| news:1069705896.469815_at_yasure...
| > Yousaf wrote:
| >
| > > Hi,
| > >
| > > I am developing an ASP VBScript app, and trying the following query
via
| ASP
| > > code:
| > >
| > > INSERT INTO RISK(RISK_NO, USER_ID, DESCRIPTION, RAISED_BY, LOG_DATE,
| IMPACT,
| > > OWNER, RESOLUTION, STATUS, EXPECTED_CLOSURE_DATE, CLOSED_DATE,
| > > ADDITIONAL_OWNER, OTHER_OWNER)
| > >
| > > VALUES(RISK_NOSeq.nextval, 'ROCKERM' ,'dsfda','HILLJE', SYSDATE, 'H',
| > > 'TOWDXX', '-','O', '09-AUG-1938', '09-AUG-1938', 'N', '-')
| > >
| > > And it is throwing up the following error:
| > >
| > > Error Type:
| > > OraOLEDB (0x80040E14)
| > > ORA-01858: a non-numeric character was found where a numeric was
| expected
| > >
| > >
| > > But the strange thing is, if I run the same query in SQL Plus, it
| inserts a
| > > record without throwing any error!
| > >
| > > Could anyone shed some light on that?
| > >
| > > Thanking you in advance
| > >
| > > Regards
| >
| > I agree with the other comments but want to point out that SQL*Plus
| > facilitates what is known as implicit conversion: Oracle will attempt
| > to make up for your bad coding by changing data types. This is a bad
| > thing to rely upon and you should always make ALL conversion explicit.
| >
| > I'd be wraping TO_DATE around the dates and if there are any numeric
| > fields removing the single quote marks.
| > --
| > Daniel Morgan
| > http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
| > http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
| > damorgan_at_x.washington.edu
| > (replace 'x' with a 'u' to reply)
| >
|
| Consider bind variables for your inserts.
|
| Oracle works much better and it solves your date problem.
|
|
|
|
|

good idea, pete, but only if the bind variable is a date datatype, no?

dan, your comment is a little bit inaccurate and misleading -- right conclusion, wrong explanation.

the implicit conversion happens in the database, SQL*Plus is doing absolutely nothing special. the statement INSERT INTO EMP ( EMPNO, ENAME, HIREDATE) VALUES (1,'BOZELL', '01-JAN-2004') will fail equally well in SQL*Plus and VB if the default date format does not match the literal date string (or variable contents)

so the issue in all environments is to avoid implicit conversion, do to potentially different session settings for NLS_DATE_FORMAT

• mcs