Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments
COURSE of the MONTH
14 days, 6 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
pl/sql XMLTable INVALID_NUMBER error
Posted on 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=". ";
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=". ";
[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
Active 1 day ago
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'
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'
I already tried the VARCHAR2 route but can't spot the problem. I don't have permissions to create a function (read-only access).
Active 1 day ago
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),'
DECODE(IS_NUMBER(AMOUNT),'
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?
Active 1 day ago
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;
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;
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.
Active 1 day ago
in http://www.arikaplan.com/oracle/ari021000.html
i found an alternative:
and length(translate(trim(AMOU
i found an alternative:
and length(translate(trim(AMOU
Now I'm really confused. When I add the where clause
where length(translate(trim(AMOU
No rows are returned.
where length(translate(trim(AMOU
No rows are returned.
Active today
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'
)
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'
)
Active 1 day ago
where length(translate(trim(AMOU
'+300+' would return 0
'100.888,333.2' would return 0
Maybe the regexp of ststuber brings easy relief
'+300+' would return 0
'100.888,333.2' would return 0
Maybe the regexp of ststuber brings easy relief
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!
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!
Featured Post
Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Working with Network Access Control Lists in Oracle 11g (part 1)
Part 2: http://www.e-e.com/A_9074.html
So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
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. …
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses
Course of the Month14 days, 6 hours left to enroll
800 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.