A non-numeric character was found

iamtrying · Sep 22, 2013

Hi,

I am baffled by this error. I can run the statement below in SQLPLUS, Toad, and SQL Developer. However, when I try to execute it as job in Oracle 9i, the error below is returned. I know it is a simple fix, but I have been unsuccessful in finding the solution. Thanks

set pagesize 0;
set heading off;
set trimspool on;
set feedback off;
alter session set nls_date_format='MMDDYYYY';
spool G:\KNX_Export\KNX_Hold_Downs.txt

select
(e."personnelID")||','||
trim(dp."runNumber")||','||
trim(dp."blockID")||','||
trim(To_Char(dp."opDate", 'MM/DD/YYYY' ))||','||
trim(dbo.hh_mma(dp."actDrivBegTime"))||','||
trim(dbo.hh_mma(dp."actDrivEndTime"))
from "dailyPiece" dp, "employeeStatus" es, "employee" e, "empAssgn" ea
where dp."opDate" between '1-jul-13' and '3-jul-13'AND
es."emp_SID" = dp."emp_SID" and es."dateEffective" <= dp."opDate" and es."dateEnd" >= dp."opDate"
and e."emp_SID" = dp."emp_SID" and
ea."heldBySid"(+) = dp."emp_SID" and ea."recType" (+) = 'P' and dp."opDate" between ea."begDate"(+) and ea."endDate"(+) and ea."heldBySid" is not null
order by dp."blockID", dp."opDate";
spool off;

SQL> es."emp_SID" = dp."emp_SID" and es."dateEffective" <= dp."opDate" and es."dateEnd" >= dp."opDate"
*
ERROR at line 10:
ORA-01858: a non-numeric character was found where a numeric was expected

SantaMufasa · Sep 23, 2013

I Am Trying,

The problem is that just prior to the execution of your SELECT statement, you advised Oracle that for your session's default dates must be in the format, "MMDDYYYY":

Code:

alter session set nls_date_format='MMDDYYYY';

Then in your SELECT statement, you provide dates that don't match that format...they are in the format "dd-mon-yy":

Code:

...where dp."opDate" between '1-jul-13' and '3-jul-13'...

5 out of the 8 characters in your date strings are non-numeric characters.

So, my questions for you are:[ul][li]Why are you changing the default date format?

Code:

alter session set nls_date_format='MMDDYYYY';

[/li][li]And more importantly, why are you overriding Oracle's default object-naming convention by surrounding all object names in double quotes? (I believe that the entire Oracle World considers it bad form to use double quotes to force absolute case sensitivity in every reference to an Oracle object.)[/li][/ul]

[santa]

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

iamtrying · Sep 23, 2013

Mufasa,

The database was designed some 13 years ago and the double quotes are required for case sensitivity. What are your recommendations besides redsigning the database?

iamtrying · Sep 23, 2013

Okay, I removed the alter session statement and the job processed correctly. Thank you

SantaMufasa · Sep 23, 2013

You are welcome.

As far as removing the double quotes are concerned, I recommend just living with the problem. To fix the problem will take not only a complete recreation of the database (without the double quotes), but also the removal of all the double quotes from all of your application references (i.e., application software).

[santa]

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

iamtrying · Sep 23, 2013

The database was designed by a vendor and the application is supported by the same. They are fully aware of the issues associated with the DB design and they write the majority of the SQL statements for development. I have been living with this monster for awhile. Again thanks.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

A non-numeric character was found

iamtrying

Technical User

SantaMufasa

Technical User

iamtrying

Technical User

iamtrying

Technical User

SantaMufasa

Technical User

iamtrying

Technical User

Similar threads

Part and Inventory Search

Sponsor