Convert string to number from xml at server side

Question

I am having issues converting numbers stores as character (with . as decimal, sometimes are null) to number from xml on server side. Query is part of package. When I run the query below on my computer, it works. However when I call the procedure from UNIX, it fails and I am getting following error message:

Errors: ORA-06512: at "DTWH.PKG_GPA", line 246

----- PL/SQL Call Stack ----- object line object handle number name 0x16967d328 390 package body DTWH.PKG_GPA 0x90bf2568 3 anonymous block

ORA-01722: invalid number

SELECT cast(nvl(trim(replace(xmltype(t.sce_msg).extract('//RoofInsulationCSV/Total/text()', 'xmlns="http://INT010.GPA.Schemas.External.GPA_AllFile')
                      .getstringval(),'.',',')),0) AS NUMBER(7,2)) AS amount
FROM        ta_main.serv_cons_event t
INNER JOIN  ta_main.event e
      ON    t.sce_evt_id_s = e.evt_id_s
WHERE       e.evt_typ_id_c IN ('INV', 'ACC')

I tried various approaches like to_number, replace with regex or whatever I found. Always work with me, never from UNIX.

Any idea, what I am missing?

Answer 1

cast relies on your session's NLS settings to convert between datatypes.

You should use to_number with an explicit number format model, something like:

SELECT to_number(nvl(trim(replace(xmltype(t.sce_msg).extract('//RoofInsulationCSV/Total/text()', 'xmlns="http://INT010.GPA.Schemas.External.GPA_AllFile').getstringval(),'.',',')),0), '999999999,99')
FROM        ta_main.serv_cons_event t
INNER JOIN  ta_main.event e
      ON    t.sce_evt_id_s = e.evt_id_s
WHERE       e.evt_typ_id_c IN ('INV', 'ACC');

You'll have to play around with the format model to fit your expected data.