Avatar of fredjonzeTwo
fredjonzeTwo
 asked on

pl/sql XMLTable INVALID_NUMBER error

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=". ";
Oracle Database

Avatar of undefined
Last Comment
fredjonzeTwo

8/22/2022 - Mon
flow01

change (temporary) the number(10,2) to VARCHAR2(25)  to get results .
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'
fredjonzeTwo

ASKER
I already tried the VARCHAR2 route but can't spot the problem. I don't have permissions to create a function (read-only access).
flow01

and the cause is really 'bad' data then you have to leave the VARCHAR2 definition and you can use the same function for the decode

DECODE(IS_NUMBER(AMOUNT),'Y',TO_NUMBER(AMOUNT),0) AMOUNT
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
fredjonzeTwo

ASKER
If I can't create a function (read only rights) is there an alternate decode statement I can use to substitute the bad value with zero?
flow01

I presume you can execute a pl/sql-block

DECLARE
    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;
BEGIN
    FOR   R1 IN (SELECT .. your select) LOOP
       if IS_NUMBER(r1.amount) = 'Y' THEN
           NULL;
      else
        DBMS_OUTPUT .PUT_LINE('bad value >'   || r1.amount || '<');
                            -- and maybe some keyvalues
      END IF;
    END LOOP;
END;
fredjonzeTwo

ASKER
The pl/sql needs to be inserted into an MS SQL Server SSIS data source, so I'm limited to just using a direct query.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
flow01

in http://www.arikaplan.com/oracle/ari021000.html 
i found an alternative:

and length(translate(trim(AMOUNT),' +-.0123456789',' ')) <> 0
fredjonzeTwo

ASKER
Now I'm really confused. When I add the where clause

where length(translate(trim(AMOUNT),' +-.0123456789',' ')) <> 0

No rows are returned.
ASKER CERTIFIED SOLUTION
Sean Stuber

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
flow01

where length(translate(trim(AMOUNT),' +-.0123456789',' ')) <> 0

'+300+'  would return 0
'100.888,333.2' would return 0
Maybe the regexp  of ststuber brings easy relief
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
fredjonzeTwo

ASKER
I ran a quick query in RazorSQL with success. I'm currently running it in SSIS to see if that will also work. It just finished with success.

While I was waiting for SSIS I did some quick experimenting to see why the length/translate clause didn't work. The culprit? Two rows that had only a minus sign with no number. Apparently Oracle sees a lone minus sign as text instead of a sign. I could simply have a clause to filter out minus...but I think you're case statement function will handle other unknown values much bettter.

I've been yanking my hair for two days trying to figure out XMLTable and INVALID_NUMBER. Thanks...and an excellent for you!
fredjonzeTwo

ASKER
A great suggestion that solves a number of problems with unexpected data in XMLTable.