0

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?

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.

  • Thanks for help, however is not working. I mean, works when executed from my PC, not from server. – HonzaB Feb 3 '15 at 13:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.