to_number conversion error

[roedelfroe]

Hello everybody,

since our database upgrade from 9i to 10g we have problems with a function. It works fine in 9i, but in 10g we got ORA-06502: PL/SQL: numeric or value error: character to number conversion error ORA-06512: at "AB.P_V_NUMBER".

Following select is used:

SELECT
SUBSTR(DS,1,4) AS YEAR,
SUBSTR(DS,5,2) AS MOTH,
SUBSTR(DS,19,5) AS OEH,
SUBSTR(DS,24,3) AS CANDZ,
SUBSTR(DS,27,1) AS ATYPE,
REPLACE(SUBSTR(US,45,17),'.',',') AS ORIG_DIVPLAN,
AB.P_V_NUMBER(REPLACE(SUBSTR(US,45,17),'.',',')) AS DIVPLAN,
REPLACE(SUBSTR(US,28,17),'.',',') AS ORIG_DIVIST,
AB.P_V_NUMBER(REPLACE(SUBSTR(US,28,17),'.',',')) AS DIVIST FROM AB.IMPORTTB WHERE
SUBSTR(DS,7,2) = '03' AND
SUBSTR(DS,1,4) = '2008' AND
SUBSTR(DS,5,2) = '03'
order by 3,4,5;

Function AB.P_V_NUMBER is as follows:

FUNCTION P_V_NUMBER (P_NUMBER IN VARCHAR2
) return number
IS
L_DECTRENNER CHAR(1) DEFAULT '.';
L_NUMBER VARCHAR2(30);
begin

L_NUMBER := REPLACE(P_NUMBER,'.',L_DECTRENNER);
L_NUMBER := REPLACE(L_NUMBER,',',L_DECTRENNER);

return to_number(L_NUMBER);

end;

A sample input string: +000014059750,000 (ORIG_DIVPLAN)
Should be returned as : 14059750 (DIVPLAN)

Of course i've checked the language parameters, they are the same in 9i and 10g.

Any hints?

Thanks a lot in advance

Roland

 

[hoinz]

Hello Roland,

ok, you've checked the language parameters. But I still think that there must be some difference.
Did you take into consideration that there are three levels of NLS parameters:
* database parameters
* instance parameters
* session parameters

You didn't tell us from which tool you are running your query. Is it sqlplus?
One guess could be:
Formerly you used an Oracle 9i client. Now you are using a recently installed Oracle 10g client, and the session parameter for the decimal separator is different now.

hope this helps

 

[ChrisHunt]

I'd start by determining just what input data is causing your problem. Something like this (not tested):

FUNCTION P_V_NUMBER (P_NUMBER IN VARCHAR2
                        ) return number
IS
L_DECTRENNER CHAR(1) DEFAULT '.';
L_NUMBER VARCHAR2(30);
begin

L_NUMBER := REPLACE(P_NUMBER,'.',L_DECTRENNER);
L_NUMBER := REPLACE(L_NUMBER,',',L_DECTRENNER);

return to_number(L_NUMBER);
[red]EXCEPTION
   WHEN OTHERS THEN
      dbms_output.put_line ('Couldn''t convert '||L_NUMBER);
      RAISE;[/red]
end;

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd