Oracle XMl parsing gives pl/sql numeric or value error

Hello - I am relatively new to XML and I hope all the experts here can help me. We have XML retrieved from a web service stored in out oracle DB into a CLOB field. I have a proc that parses this XML and inserts the data into another oracle table. I am running into "EXCEPTION--6502-ORA-06502: PL/SQL: numeric or value error" when I run this proc.

The table has about 300 000 records and some of them have data issues which I want to skip and continue processing. Below is the code I wrote and I am looking for your suggestions on what I am doing wrong. Thanks.

CREATE OR REPLACE PROCEDURE EMPI_DETAIL_EXTRACT IS
curr_val VARCHAR2(20) := 0;
l_msg varchar2(255);
CURR_VAL_INIT VARCHAR2(20) := 0;

    TYPE ROWOBJ1 IS RECORD (EMPI                 VARCHAR2(4000 BYTE),
  GENDER               VARCHAR2(4000 BYTE),
  DOB                  VARCHAR2(4000 BYTE),
  LAST_NAME            VARCHAR2(4000 BYTE),
  FIRST_NAME           VARCHAR2(4000 BYTE),
  MIDDLE_NAME          VARCHAR2(4000 BYTE),
  ADDRESS_LINE1        VARCHAR2(4000 BYTE),
  ADDRESS_LINE2        VARCHAR2(4000 BYTE),
  CITY         VARCHAR2(4000 BYTE),
  STATE        VARCHAR2(4000 BYTE),
  ZIP          VARCHAR2(4000 BYTE),
  MRNS                 XMLTYPE,
  INS_SUMMARY          XMLTYPE,
  LOCAL_ADDRESS_LINE1  VARCHAR2(4000 BYTE),
  LOCAL_ADDRESS_LINE2  VARCHAR2(4000 BYTE),
  LOCALCITY            VARCHAR2(4000 BYTE),
  LOCALZIP             VARCHAR2(4000 BYTE)
    );

    TYPE CURROBJ IS TABLE OF ROWOBJ1 ;
   
    ALLOBJ CURROBJ;
     
    OBJCOUNT NUMBER := 0;

 
BEGIN

select  extract(xmlstr2, '/SiteDetailReply/@UID' ).getStringVal() as EMPI,
 EXTRACT(xmlstr2, '//SiteDetailReply/PID/@Gender').getStringVal() as Gender,
       EXTRACT(xmlstr2, '//SiteDetailReply/PID/@DOB').getStringVal() as DOB,
       EXTRACT(xmlstr2, '//SiteDetailReply/PID/Name/@Last').getStringVal() as Last_Name,
       EXTRACT(xmlstr2, '//SiteDetailReply/PID/Name/@First').getStringVal()as First_Name,
       EXTRACT(xmlstr2, '//SiteDetailReply/PID/Name/@MI').getStringVal() as Middle_Name,
       EXTRACT(xmlstr2, '//SiteDetailReply/PID/Address/@Line1').getStringVal() as Address_Line1,
       EXTRACT(xmlstr2, '//SiteDetailReply/PID/Address/@Line2').getStringVal() as Address_Line2,
       EXTRACT(xmlstr2,'//SiteDetailReply/PID/Address/@City').getStringVal() as Address_City,
       EXTRACT(xmlstr2, '//SiteDetailReply/PID/Address/@State').getStringVal() as Address_State,
       substr(EXTRACT(xmlstr2, '//SiteDetailReply/PID/Address/@Zip'),1,5) as Address_Zip,
       EXTRACT(xmlstr2, '//SiteDetailReply/PID/MRNs' ) as MRNs,
      EXTRACT(xmlstr2, '//SiteDetailReply/IN' ) as Ins_Summary,'' as local_address_line1,'' as local_address_line2, '' as localcity,
       EXTRACT(xmlstr2, '//SiteDetailReply/PID/Address/@Zip').getStringVal() as localzip
       bulk collect into ALLOBJ
FROM (SELECT xmltype(REGEXP_REPLACE(xmlstr, '<!DOCTYPE[^<]*>', ''),null,1,1) xmlstr2 FROM empi_detail) where rownum between 60000 and 100000;

        FOR i in ALLOBJ.FIRST .. ALLOBJ.LAST
         loop
         begin
         
         insert into empi_detail_extract_stv1 values (ALLOBJ(i).EMPI,ALLOBJ(i).GENDER,ALLOBJ(i).DOB,ALLOBJ(i).LAST_NAME,ALLOBJ(i).FIRST_NAME,ALLOBJ(i).MIDDLE_NAME,ALLOBJ(i).ADDRESS_LINE1, ALLOBJ(i).ADDRESS_LINE2,
         ALLOBJ(i).CITY,ALLOBJ(i).STATE,ALLOBJ(i).ZIP,ALLOBJ(i).MRNs,ALLOBJ(i).INS_SUMMARY, ALLOBJ(i).LOCAL_ADDRESS_LINE1,ALLOBJ(i).LOCAL_ADDRESS_LINE2,
         ALLOBJ(i).LOCALCITY,ALLOBJ(i).LOCALZIP);
         
          EXCEPTION
       when others then
       l_msg := 'EXCEPTION-'||sqlcode||'-'||sqlerrm;
      insert into  empi_detail_error
       values ('BCBS',l_msg);
       
        END;
 
    END loop;  

 EXCEPTION
       when others then
       l_msg := 'EXCEPTION-'||sqlcode||'-'||sqlerrm;
      insert into  empi_detail_error
values ('BCBS',l_msg);

COMMIT;
END EMPI_DETAIL_EXTRACT

Open in new window

;
svkworldAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
where does the error happen?
 in converting your data to xmltype, in the extract or in the insert?
0
sdstuberCommented:
if the error happens in the conversion or extract, then try bulk collecting the exceptions so you can determine which row it fails on.

if the error happens on insert,  what are the values that cause it to fail?

if you don't know where it happens, change your exception handlers so they produce slightly different output

maybe something like..

 l_msg := 'EXCEPTION in loop-'||sqlcode||'-'||sqlerrm;

and

 l_msg := 'EXCEPTION in main-'||sqlcode||'-'||sqlerrm;
0
svkworldAuthor Commented:
I did change the exception code as @sdstuber suggested and it did not error out on insert. So I am assuming it is happening when I am extracting. Any suggestions on how I can fix this? Thanks.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

sdstuberCommented:
do you have a misplaced parentheses?

should

 xmltype(REGEXP_REPLACE(xmlstr, '<!DOCTYPE[^<]*>', ''),null,1,1)

be

xmltype(REGEXP_REPLACE(xmlstr, '<!DOCTYPE[^<]*>', '',null,1,1) )
0
svkworldAuthor Commented:
Unfortunately it failed with "ORA-01760: illegal argument for function" error.
0
sdstuberCommented:
I probably should have phrased that differently.

Were you intending the null,1,1  to be parameters of the regexp_replace function
or of the xmltype constructor?

Original usage was legal for xmltype constructor, but I wanted to be sure that's what you really intended.  Simply moving the parenthese, as you saw, isn't legal for the regexp_replace, but that's easy to fix if it was your intent.
0
svkworldAuthor Commented:
I intended it to be for the regexp_replace. I had the expression in place because the XML that was returned had DTD reference which was generating a call back. They now donot return the DTD information so I am OK with not using regexp_replace as well.
0
sdstuberCommented:
so your code now is ...

SELECT ....
FROM (SELECT xmltype(xmlstr) xmlstr2 FROM empi_detail)

?

is that right?


and if you just run the select by itself,  not inside pl/sql  o you get the error?

remember to take out the "BULK COLLECT"  line
0
svkworldAuthor Commented:
As suggested I did run select by itself and it ran Ok, so then I ran the insert into the table withput pl/sql and even that ran OK.

I am now confused why it is failing in my proc. I even tried to limit the rows in the proc and it runs OK until it hits rownum = 50000 and after that it errors out. I am not sure why?
0
sdstuberCommented:
You mean you tried this?




 insert into empi_detail_extract_stv1
select  extract(xmlstr2, '/SiteDetailReply/@UID' ).getStringVal() as EMPI,
 EXTRACT(xmlstr2, '//SiteDetailReply/PID/@Gender').getStringVal() as Gender,
       EXTRACT(xmlstr2, '//SiteDetailReply/PID/@DOB').getStringVal() as DOB,
       EXTRACT(xmlstr2, '//SiteDetailReply/PID/Name/@Last').getStringVal() as Last_Name,
       EXTRACT(xmlstr2, '//SiteDetailReply/PID/Name/@First').getStringVal()as First_Name,
       EXTRACT(xmlstr2, '//SiteDetailReply/PID/Name/@MI').getStringVal() as Middle_Name,
       EXTRACT(xmlstr2, '//SiteDetailReply/PID/Address/@Line1').getStringVal() as Address_Line1,
       EXTRACT(xmlstr2, '//SiteDetailReply/PID/Address/@Line2').getStringVal() as Address_Line2,
       EXTRACT(xmlstr2,'//SiteDetailReply/PID/Address/@City').getStringVal() as Address_City,
       EXTRACT(xmlstr2, '//SiteDetailReply/PID/Address/@State').getStringVal() as Address_State,
       substr(EXTRACT(xmlstr2, '//SiteDetailReply/PID/Address/@Zip'),1,5) as Address_Zip,
       EXTRACT(xmlstr2, '//SiteDetailReply/PID/MRNs' ) as MRNs,
      EXTRACT(xmlstr2, '//SiteDetailReply/IN' ) as Ins_Summary,'' as local_address_line1,'' as local_address_line2, '' as localcity,
       EXTRACT(xmlstr2, '//SiteDetailReply/PID/Address/@Zip').getStringVal() as localzip
FROM (SELECT xmltype(xmlstr) xmlstr2 FROM empi_detail) 

Open in new window


And it worked?
0
svkworldAuthor Commented:
Yes, it worked fine but pl/sql dooesn't.
0
sdstuberCommented:
does this work?
if not, then you've encountered a bug and will need to contact Oracle support

begin
 insert into empi_detail_extract_stv1
select  extract(xmlstr2, '/SiteDetailReply/@UID' ).getStringVal() as EMPI,
 EXTRACT(xmlstr2, '//SiteDetailReply/PID/@Gender').getStringVal() as Gender,
       EXTRACT(xmlstr2, '//SiteDetailReply/PID/@DOB').getStringVal() as DOB,
       EXTRACT(xmlstr2, '//SiteDetailReply/PID/Name/@Last').getStringVal() as Last_Name,
       EXTRACT(xmlstr2, '//SiteDetailReply/PID/Name/@First').getStringVal()as First_Name,
       EXTRACT(xmlstr2, '//SiteDetailReply/PID/Name/@MI').getStringVal() as Middle_Name,
       EXTRACT(xmlstr2, '//SiteDetailReply/PID/Address/@Line1').getStringVal() as Address_Line1,
       EXTRACT(xmlstr2, '//SiteDetailReply/PID/Address/@Line2').getStringVal() as Address_Line2,
       EXTRACT(xmlstr2,'//SiteDetailReply/PID/Address/@City').getStringVal() as Address_City,
       EXTRACT(xmlstr2, '//SiteDetailReply/PID/Address/@State').getStringVal() as Address_State,
       substr(EXTRACT(xmlstr2, '//SiteDetailReply/PID/Address/@Zip'),1,5) as Address_Zip,
       EXTRACT(xmlstr2, '//SiteDetailReply/PID/MRNs' ) as MRNs,
      EXTRACT(xmlstr2, '//SiteDetailReply/IN' ) as Ins_Summary,'' as local_address_line1,'' as local_address_line2, '' as localcity,
       EXTRACT(xmlstr2, '//SiteDetailReply/PID/Address/@Zip').getStringVal() as localzip
FROM (SELECT xmltype(xmlstr) xmlstr2 FROM empi_detail) ;
end;
0
svkworldAuthor Commented:
The above did work but now I cannot put logic to skip the records that fall under exception?
0
sdstuberCommented:
that's ok, I wanted to make sure you weren't having a problem with sql inside of pl/sql.

Since that's been eliminated, then the error is somewhere else in the pl/sql and we can address that now.
0
svkworldAuthor Commented:
Can you please tell me where to start? The reason I had it in proc was to skip the exception records and proceed.
0
sdstuberCommented:
Here's a slightly simpler version, removing the bulk collect because it doesn't really help performance since cursor FOR loops already bulk collect automatically (for 10g and higher)


Note, the exception messages don't contain any identifying information so you won't know which rows failed with which errors.

CREATE OR REPLACE PROCEDURE empi_detail_extract
IS
    l_msg VARCHAR2(255);
BEGIN
    -- Cursor for loops already do bulk operations behind the scenes.
    FOR x
        IN (SELECT EXTRACT(xmlstr2, '/SiteDetailReply/@UID').getstringval() AS empi,
                   EXTRACT(xmlstr2, '//SiteDetailReply/PID/@Gender').getstringval() AS gender,
                   EXTRACT(xmlstr2, '//SiteDetailReply/PID/@DOB').getstringval() AS dob,
                   EXTRACT(xmlstr2, '//SiteDetailReply/PID/Name/@Last').getstringval() AS last_name,
                   EXTRACT(xmlstr2, '//SiteDetailReply/PID/Name/@First').getstringval()
                       AS first_name,
                   EXTRACT(xmlstr2, '//SiteDetailReply/PID/Name/@MI').getstringval() AS middle_name,
                   EXTRACT(xmlstr2, '//SiteDetailReply/PID/Address/@Line1').getstringval()
                       AS address_line1,
                   EXTRACT(xmlstr2, '//SiteDetailReply/PID/Address/@Line2').getstringval()
                       AS address_line2,
                   EXTRACT(xmlstr2, '//SiteDetailReply/PID/Address/@City').getstringval()
                       AS address_city,
                   EXTRACT(xmlstr2, '//SiteDetailReply/PID/Address/@State').getstringval()
                       AS address_state,
                   SUBSTR(EXTRACT(xmlstr2, '//SiteDetailReply/PID/Address/@Zip'), 1, 5)
                       AS address_zip,
                   EXTRACT(xmlstr2, '//SiteDetailReply/PID/MRNs') AS mrns,
                   EXTRACT(xmlstr2, '//SiteDetailReply/IN') AS ins_summary,
                   '' AS local_address_line1,
                   '' AS local_address_line2,
                   '' AS localcity,
                   EXTRACT(xmlstr2, '//SiteDetailReply/PID/Address/@Zip').getstringval()
                       AS localzip
              FROM (SELECT xmltype(xmlstr) xmlstr2 FROM empi_detail))
    LOOP
        BEGIN
            INSERT INTO empi_detail_extract_stv1
            VALUES (
                       x.empi,
                       x.gender,
                       x.dob,
                       x.last_name,
                       x.first_name,
                       x.middle_name,
                       x.address_line1,
                       x.address_line2,
                       x.address_city,
                       x.address_state,
                       x.address_zip,
                       x.mrns,
                       x.ins_summary,
                       x.local_address_line1,
                       x.local_address_line2,
                       x.localcity
                   );
        EXCEPTION
            WHEN OTHERS
            THEN
                l_msg := 'EXCEPTION-' || SQLCODE || '-' || SQLERRM;

                INSERT INTO empi_detail_error
                VALUES ('BCBS', l_msg);
        END;
    END LOOP;
EXCEPTION
    WHEN OTHERS
    THEN
        l_msg := 'EXCEPTION-' || SQLCODE || '-' || SQLERRM;

        INSERT INTO empi_detail_error
        VALUES ('BCBS', l_msg);

        COMMIT;
END empi_detail_extract;

Open in new window

0
sdstuberCommented:
and another version that can take advantage of the bulk collect by using the bulk operations on the insert  but saving the exceptions for each row in order to process them as needed.

As in the original and my previous code you'll probably want to change the error message to include more information from the bulk errors, like part of the data to identify which line failed.


CREATE OR REPLACE PROCEDURE empi_detail_extract
IS
    TYPE rowobj1 IS RECORD
    (
        empi                VARCHAR2(4000 BYTE),
        gender              VARCHAR2(4000 BYTE),
        dob                 VARCHAR2(4000 BYTE),
        last_name           VARCHAR2(4000 BYTE),
        first_name          VARCHAR2(4000 BYTE),
        middle_name         VARCHAR2(4000 BYTE),
        address_line1       VARCHAR2(4000 BYTE),
        address_line2       VARCHAR2(4000 BYTE),
        city                VARCHAR2(4000 BYTE),
        state               VARCHAR2(4000 BYTE),
        zip                 VARCHAR2(4000 BYTE),
        mrns                XMLTYPE,
        ins_summary         XMLTYPE,
        local_address_line1 VARCHAR2(4000 BYTE),
        local_address_line2 VARCHAR2(4000 BYTE),
        localcity           VARCHAR2(4000 BYTE),
        localzip            VARCHAR2(4000 BYTE)
    );

    TYPE currobj IS TABLE OF rowobj1;

    allobj currobj;

    l_msg  VARCHAR2(255);
BEGIN
    -- Cursor for loops already do bulk operations behind the scenes.
    SELECT EXTRACT(xmlstr2, '/SiteDetailReply/@UID').getstringval() AS empi,
           EXTRACT(xmlstr2, '//SiteDetailReply/PID/@Gender').getstringval() AS gender,
           EXTRACT(xmlstr2, '//SiteDetailReply/PID/@DOB').getstringval() AS dob,
           EXTRACT(xmlstr2, '//SiteDetailReply/PID/Name/@Last').getstringval() AS last_name,
           EXTRACT(xmlstr2, '//SiteDetailReply/PID/Name/@First').getstringval() AS first_name,
           EXTRACT(xmlstr2, '//SiteDetailReply/PID/Name/@MI').getstringval() AS middle_name,
           EXTRACT(xmlstr2, '//SiteDetailReply/PID/Address/@Line1').getstringval() AS address_line1,
           EXTRACT(xmlstr2, '//SiteDetailReply/PID/Address/@Line2').getstringval() AS address_line2,
           EXTRACT(xmlstr2, '//SiteDetailReply/PID/Address/@City').getstringval() AS address_city,
           EXTRACT(xmlstr2, '//SiteDetailReply/PID/Address/@State').getstringval() AS address_state,
           SUBSTR(EXTRACT(xmlstr2, '//SiteDetailReply/PID/Address/@Zip'), 1, 5) AS address_zip,
           EXTRACT(xmlstr2, '//SiteDetailReply/PID/MRNs') AS mrns,
           EXTRACT(xmlstr2, '//SiteDetailReply/IN') AS ins_summary,
           '' AS local_address_line1,
           '' AS local_address_line2,
           '' AS localcity,
           EXTRACT(xmlstr2, '//SiteDetailReply/PID/Address/@Zip').getstringval() AS localzip
      BULK COLLECT INTO allobj
      FROM (SELECT xmltype(xmlstr) xmlstr2 FROM empi_detail);

    FORALL i IN INDICES OF allobj SAVE EXCEPTIONS
        INSERT INTO empi_detail_extract_stv1
        VALUES allobj(i);
EXCEPTION
    WHEN dml_errors
    THEN
        FOR i IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
        LOOP
            l_msg :=
                   'EXCEPTION-'
                || SQL%BULK_EXCEPTIONS(i).ERROR_CODE
                || '-'
                || SQLERRM(-(SQL%BULK_EXCEPTIONS(i).ERROR_CODE));

            INSERT INTO empi_detail_error
            VALUES ('BCBS', l_msg);
        END LOOP;

        COMMIT;
END empi_detail_extract;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
svkworldAuthor Commented:
Thank You @sdstuber. Both of them worked very well.
0
sdstuberCommented:
Glad I could help.

Don't accept your own comment and the questions will close immediately.
Of course, if your comment is actually part of the solution, then do so; but that's not the case here.
0
svkworldAuthor Commented:
Very innovative. Learned something new.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.