A non-numeric character was found

[iamtrying]

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]

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":

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":

...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?

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]

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

 

[iamtrying]

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]

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

 

[SantaMufasa]

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).

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

 

[iamtrying]

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.