Solved

pl/sql XMLTable INVALID_NUMBER error

Posted on 2012-12-21
12
1,145 Views
Last Modified: 2012-12-21
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
Comment
Question by:fredjonzeTwo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
12 Comments
 
LVL 20

Expert Comment

by:flow01
ID: 38713785
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
 

Author Comment

by:fredjonzeTwo
ID: 38713797
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
 
LVL 20

Expert Comment

by:flow01
ID: 38713799
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:fredjonzeTwo
ID: 38713810
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
 
LVL 20

Expert Comment

by:flow01
ID: 38713818
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
 

Author Comment

by:fredjonzeTwo
ID: 38713828
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
 
LVL 20

Expert Comment

by:flow01
ID: 38713838
in http://www.arikaplan.com/oracle/ari021000.html 
i found an alternative:

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

Author Comment

by:fredjonzeTwo
ID: 38713898
Now I'm really confused. When I add the where clause

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

No rows are returned.
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 320 total points
ID: 38713918
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
 
LVL 20

Expert Comment

by:flow01
ID: 38713984
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
 

Author Comment

by:fredjonzeTwo
ID: 38714013
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
 

Author Closing Comment

by:fredjonzeTwo
ID: 38714015
A great suggestion that solves a number of problems with unexpected data in XMLTable.
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

718 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question