-1

Why do I get the error shown when I try to run this query in SQL Developer:

DECLARE
    p_latitude number;
BEGIN
    p_latitude:=TO_NUMBER(LTRIM(RTRIM(REGEXP_SUBSTR('BT1 1AA|54.60240|-5.92214|875082434', '[^|]+', 1, 2),'"'),'"'));
END;

Error:

ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 4

Can anyone help me solve this error?

Expected output:

54.60240

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
Sindhu Choudary
  • 19
  • 4
  • 1
    Possible duplicate of [decimal separator oracle](https://stackoverflow.com/questions/38739525/decimal-separator-oracle) – Egan Wolf Mar 19 '19 at 05:29
  • 1
    Can you post the output of `select value from nls_session_parameters where parameter = 'NLS_NUMERIC_CHARACTERS';`? – Kaushik Nayak Mar 19 '19 at 05:33
  • The output is ,. – Sindhu Choudary Mar 19 '19 at 06:33
  • 1
    Since you are expected to read decimals separated by `"." ` , there's no reason why it is set to `,.` , quite possibly by a mistake ? read [this](https://stackoverflow.com/a/38739718/7998591) answer to know the details of the error and how to modify it – Kaushik Nayak Mar 19 '19 at 06:47

1 Answers1

1

I think you can manage it by such a conversion :

DECLARE
 p_latitude number;
BEGIN
 with t(nr) as
 (
  select LTRIM(RTRIM(REGEXP_SUBSTR('BT1 1AA|54.60240|-5.92214|875082434', '[^|]+', 1, 2),'"'),'"') 
     from dual
 )
 select to_number(replace(nr,'.',','),'fm99G990D00000','NLS_NUMERIC_CHARACTERS = '',.''')
   into p_latitude
   from t;
END;
Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55