I'm relatively new to pl/sql and xml.
I have an sql query that uses XMLTable to shred transaction details. When I run the query I get an INVALID_NUMBER error. If I comment out the 'Amount' element the query runs, so I'm assuming the problem lies here. I have no control over the content, so can't fix it. Is there any way to alter the XMLTable to either ignore the row with the error or do a decode to substitute zero when an invalid_number is found?
SELECT
so.tranId
, CHARGES.*
FROM mytable1 so,
XMLTABLE('//Charge' PASSING xmltype(so.xmlClob)
COLUMNS "GeneralChargeID" NUMBER PATH 'GeneralChargeID',
"Name" VARCHAR2(255) PATH 'Name',
"Status" VARCHAR2(25) PATH 'Status',
"Quantity" NUMBER PATH 'Quantity',
"Amount" NUMBER(10,2) PATH 'Amount',
"CreditFlag" CHAR(1) PATH 'CreditFlag'
) as CHARGES
I tried calling this statement, but the same error was thrown.
alter session set nls_numeric_characters=". ";
Inspect the results for finding the reason.
If the number of resulting rows is too large to inspect, create a function
create or replace function is_number(p_in varchar2) return varchar2
is
n1 number(10,2);
rsl varchar2(1);
begin
begin
n1 := to_number(p_in);
rsl := 'Y';
exception when others then
rsl := 'N';
end;
RETURN rsl;
end;
and select rows
AND IS_NUMBER(AMOUNT) <> 'Y'