Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01858
ORA-01858 [message #376246] Tue, 16 December 2008 10:42 Go to next message
ramtin
Messages: 37
Registered: November 2008
Member
Hi,

I have a function which return 'Y' or 'N'. When I use that in English version of Oracle DB 10G everything is fine but when I use same function in Franšais version DB then I will get this error :

ORA-01858: a non-numeric character was found where a numeric was expected

Please advice.
Thank you in advance.
Re: ORA-01858 [message #376248 is a reply to message #376246] Tue, 16 December 2008 10:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Without any code, table, value how do you expect we can answer.

Oh yes, I have one, fix what is wrong.

Regards
Michel
Re: ORA-01858 [message #376249 is a reply to message #376246] Tue, 16 December 2008 11:07 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Wild guess: use explicit date formats!
Re: ORA-01858 [message #376253 is a reply to message #376249] Tue, 16 December 2008 12:10 Go to previous messageGo to next message
ramtin
Messages: 37
Registered: November 2008
Member
Here is my code for function

FUNCTION F_DOES( P NUMBER,
P_AS_OF_DATE DATE DEFAULT SYSDATE) RETURN VARCHAR2 IS
BEGIN
GV_COUNT := 0;

SELECT COUNT(0)
INTO GV_COUNT
FROM PDRRTFD A, VDRGFEDN B1
WHERE A.P = P_P
AND A.P = B1.P
AND A.BCODE = B1.BCODE
AND A.BCODE IN('240', '241', '242', '243', '246','247')
AND TRUNC(P_AS_OF_DATE) BETWEEN A.BDATE
AND
NVL(A.EDATE, '31-DEC-2999')
AND B1.STATUS = 'A'
AND B1.EFFECTIVE_DATE = (SELECT MAX(B2.EFFECTIVE_DATE)
FROM VDRGFEDN B2
WHERE B1.P = B2.P
AND B1.BCODE = B2.BCODE
AND B2.EFFECTIVE_DATE <= P_AS_OF_DATE);

IF GV_COUNT > 0 THEN
RETURN 'Y';
ELSE
RETURN 'N';
END IF;

END F_DOES;

and I am calling function like

IF f_does(global) = 'Y' THEN

Then I get

ORA-01858: a non-numeric character was found where a numeric was expected

Please advise
Re: ORA-01858 [message #376256 is a reply to message #376253] Tue, 16 December 2008 12:47 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
ramtin wrote on Tue, 16 December 2008 13:10

NVL(A.EDATE, '31-DEC-2999')



This is now how you use DATEs in Oracle. Please see the documentation for the TO_DATE function and the concepts guide about DATE datatype.
Previous Topic: dbms_output.put_line
Next Topic: BFILE
Goto Forum:
  


Current Time: Tue Dec 06 12:38:08 CST 2016

Total time taken to generate the page: 0.13556 seconds