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?