| ORA-01858 [message #376246] |
Tue, 16 December 2008 10:42  |
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 #376253 is a reply to message #376249] |
Tue, 16 December 2008 12:10  |
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 |
joy_division
Messages: 4963
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
