A non-numeric character was found
A non-numeric character was found
(OP)
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
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
RE: A non-numeric character was found
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
Then in your SELECT statement, you provide dates that don't match that format...they are in the format "dd-mon-yy":
CODE
5 out of the 8 characters in your date strings are non-numeric characters.
So, my questions for you are:
CODE
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel."
RE: A non-numeric character was found
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?
RE: A non-numeric character was found
RE: A non-numeric character was found
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).
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel."
RE: A non-numeric character was found