Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01858: a non-numeric character was found where a numeric was expected
ORA-01858: a non-numeric character was found where a numeric was expected [message #234921] Thu, 03 May 2007 08:25 Go to next message
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
Senior Member
I'm writing a sql plus report and running it through the Oracle Apps for an AP report. The sql below is giving me problems within the apps. Can anyone advise on what i should do? I tried putting in a hard coded date and the report works fine, but I need to pass variables as such below. Please help if you can.


SELECT /*+ INDEX_JOIN(C) */ vendor_id

FROM APPS.PO_VENDORS C

where months_between ('&&as_of_date', trunc(C.creation_date)) >= '&&months_inactive' --to_number('&&months_inactive')


ERROR

Enter value for as_of_date: EXEC FND_CONC_STAT.COLLECT;
Enter value for months_inactive:
where months_between ('EXEC FND_CONC_STAT.COLLECT;', trunc(C.creation_date)) >= '' --to_number('')
*
ERROR at line 7:
ORA-01858: a non-numeric character was found where a numeric was expected
Re: ORA-01858: a non-numeric character was found where a numeric was expected [message #234926 is a reply to message #234921] Thu, 03 May 2007 08:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Nothing shocked you in the clause?
where months_between ('EXEC FND_CONC_STAT.COLLECT;', trunc(C.creation_date)) >= ''
Nothing shocked you giving "EXEC FND_CONC_STAT.COLLECT;" as a variable name?

If nothing shocked you, I can't do anything for you.

Regards
Michel


Re: ORA-01858: a non-numeric character was found where a numeric was expected [message #234931 is a reply to message #234926] Thu, 03 May 2007 08:45 Go to previous messageGo to next message
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
Senior Member
I'm not sure I understand what you are saying. This is part of the sql that I'm using:

SELECT /*+ INDEX_JOIN(C) */ vendor_id

FROM APPS.PO_VENDORS C

where months_between ('&&as_of_date', trunc(C.creation_date)) >= '&&months_inactive' --to_number('&&months_inactive')


I'm not giving a variable name of: EXEC FND_CONC_STAT.COLLECT;"

Explain what you are saying that I may be missing?

Re: ORA-01858: a non-numeric character was found where a numeric was expected [message #234936 is a reply to message #234931] Thu, 03 May 2007 08:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You posted:
Quote:
Enter value for as_of_date: EXEC FND_CONC_STAT.COLLECT;
Enter value for months_inactive:

So you gave "EXEC FND_CONC_STAT.COLLECT;" as a value.

As you said:
Quote:
I need to pass variables

I thought you meant this "value" is a variable.

Regards
Michel
Re: ORA-01858: a non-numeric character was found where a numeric was expected [message #234949 is a reply to message #234936] Thu, 03 May 2007 09:35 Go to previous messageGo to next message
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
Senior Member
I'm not passing the following variable as such. There seems to be a problem with the apps converting the as_of_date. That is what seems to be the problem here.

Below is the error message from the output file.

ERROR

Enter value for as_of_date: EXEC FND_CONC_STAT.COLLECT;
Enter value for months_inactive:
where months_between ('EXEC FND_CONC_STAT.COLLECT;', trunc(C.creation_date)) >= '' --to_number('')
*
ERROR at line 7:
ORA-01858: a non-numeric character was found where a numeric was expected
Re: ORA-01858: a non-numeric character was found where a numeric was expected [message #234964 is a reply to message #234949] Thu, 03 May 2007 10:07 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
First of all, you are using character strings as DATEs.
Second, as Michel has stated, you have given EXEC FND_CONC_STAT.COLLECT as your value.
Re: ORA-01858: a non-numeric character was found where a numeric was expected [message #234967 is a reply to message #234964] Thu, 03 May 2007 10:14 Go to previous messageGo to next message
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
Senior Member
So for the sql I am using, what would be the correct way to write the sql? Suggestions please.


Anne
Re: ORA-01858: a non-numeric character was found where a numeric was expected [message #234970 is a reply to message #234967] Thu, 03 May 2007 10:33 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Did you by any chance copy-paste a couple of commands into sqlplus?

Did the original look like this?

SELECT /*+ INDEX_JOIN(C) */ vendor_id
FROM APPS.PO_VENDORS C
where months_between ('&&as_of_date', trunc(C.creation_date)) >= '&&months_inactive' --to_number('&&months_inactive');

EXEC FND_CONC_STAT.COLLECT;

....etc

??
Re: ORA-01858: a non-numeric character was found where a numeric was expected [message #234972 is a reply to message #234967] Thu, 03 May 2007 10:36 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As Joy said, you (should) enter a string for "as_of_date" and you have to translate it to date using TO_DATE function in your query.

Next as it is written your column can't access through an index. It should be rewritten as:
where trunc(C.creation_date) >= (or <=) month_between(to_date('&&as_of_date','<your input format>'),&&months_inactive)
and an index may be created on trunc(C.creation_date)

(Notice that I removed ' around the last input value to keep its number definition and avoid useless casting)
Of course, I assumed the logic of your query is correct.

Regards
Michel
Previous Topic: Spacing.
Next Topic: Some questions in pl/sql
Goto Forum:
  


Current Time: Sat Dec 10 10:58:26 CST 2016

Total time taken to generate the page: 0.08405 seconds