×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

A non-numeric character was found

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

RE: A non-numeric character was found

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:
  • Why are you changing the default date format?

    CODE

    alter session set nls_date_format='MMDDYYYY'; 
  • 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.)

santaMufasa
(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

(OP)
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?

RE: A non-numeric character was found

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

RE: A non-numeric character was found

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

santaMufasa
(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

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close