date insertion error

[Database Administrator]

"Pizza Goldberg" wrote in message
news:M_OdnfRpC8vXM1-iRVn-vA@comcast.com...
|
| "Daniel Morgan" wrote in message
| news:1069705896.469815@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/e...ad/oad_crs.asp
| > http://www.outreach.washington.edu/e...oa/aoa_crs.asp
| > damorgan@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

[Database Administrator]

"Pizza Goldberg" wrote in message
news:M_OdnfRpC8vXM1-iRVn-vA@comcast.com...
|
| "Daniel Morgan" wrote in message
| news:1069705896.469815@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/e...ad/oad_crs.asp
| > http://www.outreach.washington.edu/e...oa/aoa_crs.asp
| > damorgan@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

[Database Administrator]

|
| The first thing you do after connecting to oracle in the VB app:
| alter session set nls_date_format='DD-MON-YYYY';
|
| ... or whatever date format you like to have in your application as long
| as you use that format when you code against the database.
|
| The nls_date_format can be set in several different places. The alter
| session overrides them all.
|
|
|
|
| Janne
|

be careful with the ALTER SESSION unless you're in an environtment with good
coding standards

until last month i used to advocate basically the same thing -- i'd include
an ALTER SESSION in the startup code of my packages to ensure that the date
format for my package was what i expected

however, we started to get intermittent errors in some of the other
packages -- turned out the other developers were doing implicit date
conversion, which usually worked, but when their code was run in a shared
session (9iAS) that my code had been run in, they would get conversion
errors

so ALTER SESSION is good if EVERYBODY does it -- perhaps including it in the
authentication code, but definitely making it part of the QA process

-- mcs

[Database Administrator]

|
| The first thing you do after connecting to oracle in the VB app:
| alter session set nls_date_format='DD-MON-YYYY';
|
| ... or whatever date format you like to have in your application as long
| as you use that format when you code against the database.
|
| The nls_date_format can be set in several different places. The alter
| session overrides them all.
|
|
|
|
| Janne
|

be careful with the ALTER SESSION unless you're in an environtment with good
coding standards

until last month i used to advocate basically the same thing -- i'd include
an ALTER SESSION in the startup code of my packages to ensure that the date
format for my package was what i expected

however, we started to get intermittent errors in some of the other
packages -- turned out the other developers were doing implicit date
conversion, which usually worked, but when their code was run in a shared
session (9iAS) that my code had been run in, they would get conversion
errors

so ALTER SESSION is good if EVERYBODY does it -- perhaps including it in the
authentication code, but definitely making it part of the QA process

-- mcs

[Database Administrator]

|
| The first thing you do after connecting to oracle in the VB app:
| alter session set nls_date_format='DD-MON-YYYY';
|
| ... or whatever date format you like to have in your application as long
| as you use that format when you code against the database.
|
| The nls_date_format can be set in several different places. The alter
| session overrides them all.
|
|
|
|
| Janne
|

be careful with the ALTER SESSION unless you're in an environtment with good
coding standards

until last month i used to advocate basically the same thing -- i'd include
an ALTER SESSION in the startup code of my packages to ensure that the date
format for my package was what i expected

however, we started to get intermittent errors in some of the other
packages -- turned out the other developers were doing implicit date
conversion, which usually worked, but when their code was run in a shared
session (9iAS) that my code had been run in, they would get conversion
errors

so ALTER SESSION is good if EVERYBODY does it -- perhaps including it in the
authentication code, but definitely making it part of the QA process

-- mcs

[Database Administrator]

mcstock wrote:
> yousaf,
>
> regarding the date error -- always use explicit date conversion (with the
> TO_DATE() function) when using string literals to populate oracle date
> columns. it is very possible that the session setting NLS_DATE_FORMAT will
> be different in different environments -- either because the DBA sets it
> differently in different databases or because some other code using the same
> session issues an ALTER SESSION statement to change it (this is especially a
> potential problem in web-based apps where sessions are re-used)
>
> regarding the hanging -- not enough details to provide any assistance --
> suggest you re-post with more details and a different subject
>
> -- mcs


yep. You and Daniel Morgan are correct.

One should always do explicit type conversions.

I stand corrected.

Janne!

[Database Administrator]

mcstock wrote:
> yousaf,
>
> regarding the date error -- always use explicit date conversion (with the
> TO_DATE() function) when using string literals to populate oracle date
> columns. it is very possible that the session setting NLS_DATE_FORMAT will
> be different in different environments -- either because the DBA sets it
> differently in different databases or because some other code using the same
> session issues an ALTER SESSION statement to change it (this is especially a
> potential problem in web-based apps where sessions are re-used)
>
> regarding the hanging -- not enough details to provide any assistance --
> suggest you re-post with more details and a different subject
>
> -- mcs


yep. You and Daniel Morgan are correct.

One should always do explicit type conversions.

I stand corrected.

Janne!

[Database Administrator]

mcstock wrote:
> yousaf,
>
> regarding the date error -- always use explicit date conversion (with the
> TO_DATE() function) when using string literals to populate oracle date
> columns. it is very possible that the session setting NLS_DATE_FORMAT will
> be different in different environments -- either because the DBA sets it
> differently in different databases or because some other code using the same
> session issues an ALTER SESSION statement to change it (this is especially a
> potential problem in web-based apps where sessions are re-used)
>
> regarding the hanging -- not enough details to provide any assistance --
> suggest you re-post with more details and a different subject
>
> -- mcs


yep. You and Daniel Morgan are correct.

One should always do explicit type conversions.

I stand corrected.

Janne!

[santtarius]

Hi,

I have problem inserting special character like Euro symbol,Yen symbol etc into ORacle database.In windows box i dont have any issue.But in HP Unix box it is not inserted properly.Please let me know how to resolve this.

Regards
Santhosh