• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1197
  • Last Modified:

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=". ";
0
fredjonzeTwo
Asked:
fredjonzeTwo
  • 6
  • 5
1 Solution
 
flow01Commented:
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'
0
 
fredjonzeTwoAuthor Commented:
I already tried the VARCHAR2 route but can't spot the problem. I don't have permissions to create a function (read-only access).
0
 
flow01Commented:
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
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
fredjonzeTwoAuthor Commented:
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?
0
 
flow01Commented:
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;
0
 
fredjonzeTwoAuthor Commented:
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.
0
 
flow01Commented:
in http://www.arikaplan.com/oracle/ari021000.html 
i found an alternative:

and length(translate(trim(AMOUNT),' +-.0123456789',' ')) <> 0
0
 
fredjonzeTwoAuthor Commented:
Now I'm really confused. When I add the where clause

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

No rows are returned.
0
 
sdstuberCommented:
try this...


SELECT so.tranid,
       "GeneralChargeID",
       "Name",
       "Status",
       "Quantity",
       CASE WHEN REGEXP_LIKE(amount_str, '[+-]?[0-9]+.?[0-9]*') THEN TO_NUMBER(amount_str) ELSE NULL END
           "Amount",
       "CreditFlag"
  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_str VARCHAR2(20) PATH 'Amount',
                   "CreditFlag" CHAR(1) PATH 'CreditFlag'
       )
0
 
flow01Commented:
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
0
 
fredjonzeTwoAuthor Commented:
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!
0
 
fredjonzeTwoAuthor Commented:
A great suggestion that solves a number of problems with unexpected data in XMLTable.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now