Solved

ORA-19025 EXTRACTVALUE returns value of only one node

Posted on 2006-07-05
6
8,223 Views
Last Modified: 2010-05-18
This is a followup to a previous question, my code was working fine until I got a new sample   PIN_PRICE_EVENT_MSG =file
I now get the following error, is there a way around this. My package is shown below. Thanks in advance.
ORA-19025 EXTRACTVALUE returns value of only one node
ORA-06512 at "ADMIN.PARSE_XML_PKG_2", line 78
ORA-06512 at line 15

DECLARE
  PIN_PRICE_EVENT_MSG CLOB;
  POUT_ERROR NUMBER;
  POUT_ERROR_MSG VARCHAR2(200);
  POUT_TIME_STAMP VARCHAR2(200);
  POUT_SOURCE_ID VARCHAR2(200);

BEGIN
  PIN_PRICE_EVENT_MSG :='<?xml version="1.0"?><PDATA><HDR>20060703190133~RMS</HDR><EVH>31~00006~204745175~P~Y~N~20060704000100~20060712235900~Prom 1 - Laks</EVH><EVD>204745175~04608437~14.99~A~A</EVD><EVH>31~00006~204745175~P~Y~N~20060704000100~20060712235900~Prom 1 - Laks</EVH><EVD>204745175~04608455~8.99~A~A</EVD><EVH>31~00006~204745176~P~Y~N~20060704000100~20060712235900~Prm 2 - Laks</EVH><EVD>204745176~04622331~14.49~A~S</EVD><EVH>31~00006~204745176~P~Y~N~20060704000100~20060712235900~Prm 2 - Laks</EVH><EVD>204745176~04622439~13.49~A~S</EVD><EVH>31~00010~204745175~P~Y~N~20060704000100~20060712235900~Prom 1 - Laks</EVH><EVD>204745175~04608437~14.99~A~A</EVD><EVH>31~00010~204745175~P~Y~N~20060704000100~20060712235900~Prom 1 - Laks</EVH><EVD>204745175~04608455~8.99~A~A</EVD><EVH>31~00010~204745176~P~Y~N~20060704000100~20060712235900~Prm 2 - Laks</EVH><EVD>204745176~04622331~14.49~A~S</EVD><EVH>31~00010~204745176~P~Y~N~20060704000100~20060712235900~Prm 2 - Laks</EVH><EVD>204745176~04622439~13.49~A~S</EVD></PDATA>';
  POUT_ERROR := NULL;
  POUT_ERROR_MSG := NULL;
  POUT_TIME_STAMP := NULL;
  POUT_SOURCE_ID := NULL;

  ADMIN.PARSE_XML_PKG_2.PARSE_XML_IMPORT_PRC ( PIN_PRICE_EVENT_MSG, POUT_ERROR, POUT_ERROR_MSG, POUT_TIME_STAMP, POUT_SOURCE_ID );
  COMMIT;
END;

CREATE OR REPLACE PACKAGE parse_xml_pkg_2
AS
   FUNCTION parse_str_fnc (p_str IN VARCHAR2, delim IN VARCHAR2)
      RETURN str_tab;

   PROCEDURE parse_xml_import_prc (
      pin_price_event_msg   IN       CLOB,
      pout_error            OUT      NUMBER,
      pout_error_msg        OUT      VARCHAR,
      pout_time_stamp       OUT      VARCHAR,
      pout_source_id        OUT      VARCHAR
   );
END parse_xml_pkg_2;
/

CREATE OR REPLACE PACKAGE BODY parse_xml_pkg_2
AS
   evd_sub_val_tab       str_tab;
   evd_val_tab           str_tab;
   hdr_val_tab           str_tab;
   evh_val_tab           str_tab;
   DATA                  VARCHAR2 (2000);


   --TYPE mytable IS TABLE OF VARCHAR2 (200);
   FUNCTION parse_str_fnc (p_str IN VARCHAR2, delim IN VARCHAR2)
      RETURN str_tab
   AS
      l_str            VARCHAR2 (2000) DEFAULT p_str || delim;
      l_i              NUMBER;
      l_tbl            str_tab         := str_tab ();
      l_len_of_delim   NUMBER;
   BEGIN
      l_len_of_delim := LENGTH (delim) - 1;

      --DBMS_OUTPUT.put_line ('parse_str_fnc p_str : ' || p_str);
      LOOP
         l_i := INSTR (l_str, delim);
         EXIT WHEN (NVL (l_i, 0) = 0);
         l_tbl.EXTEND;
         l_tbl (l_tbl.COUNT) := SUBSTR (l_str, 1, l_i - 1);
         --Add length of delimiter to account correct value retrieved in substr function.
         l_str := SUBSTR (l_str, l_i + 1 + l_len_of_delim);
      --DBMS_OUTPUT.put_line ('l_i INSTR fnc: ' || l_i||' l_str: '||l_str||' l_len_of_delim '||l_len_of_delim );
      END LOOP;

      RETURN l_tbl;
   END parse_str_fnc;

   PROCEDURE parse_xml_import_prc (
      pin_price_event_msg   IN       CLOB,
      pout_error            OUT      NUMBER,
      pout_error_msg        OUT      VARCHAR,
      pout_time_stamp       OUT      VARCHAR,
      pout_source_id        OUT      VARCHAR
   )
   AS
      xmldata   XMLTYPE;
      DATA      VARCHAR2 (2000);
   BEGIN
      -- convert clob into xmltype instance
      xmldata := XMLTYPE.createxml (pin_price_event_msg);

      --get "HDR" data
      BEGIN
         SELECT EXTRACTVALUE (xmldata, '/PDATA/HDR') "REFERENCE"
           INTO DATA
           FROM DUAL
          WHERE EXISTSNODE (xmldata, '/PDATA/HDR') = 1;

         DBMS_OUTPUT.put_line ('HDR Data: ' || DATA);
         hdr_val_tab := parse_str_fnc (DATA, '~');

         FOR j IN 1 .. hdr_val_tab.COUNT
         LOOP
            DBMS_OUTPUT.put_line ('--HDR Value: ' || hdr_val_tab (j));
         END LOOP;
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            ROLLBACK;
            pout_error := 1;
            pout_error_msg := 'Header tag (<HDR>) is missing';
            pout_time_stamp := NULL;
            pout_source_id := NULL;
            --pOutEndHdrPos     := NULL;
            DBMS_OUTPUT.put_line ('NO Data: ' || DATA);
            RETURN;
      END;

      --get "EVH" data
      BEGIN
         SELECT EXTRACTVALUE (xmldata, '/PDATA/EVH') "REFERENCE"
           INTO DATA
           FROM DUAL
          WHERE EXISTSNODE (xmldata, '/PDATA/EVH') = 1;

         DBMS_OUTPUT.put_line ('EVH Data: ' || DATA);
         evh_val_tab := parse_str_fnc (DATA, '~');

         FOR j IN 1 .. evh_val_tab.COUNT
         LOOP
            DBMS_OUTPUT.put_line ('--EVH Value: ' || evh_val_tab (j));
         END LOOP;
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            ROLLBACK;
            pout_error := 1;
            pout_error_msg := 'Evh tag (<EVH>) is missing';
            pout_time_stamp := NULL;
            pout_source_id := NULL;
            --pOutEndHdrPos     := NULL;
            DBMS_OUTPUT.put_line ('NO Evh Data: ' || DATA);
            RETURN;
      END;

      --get "EVH" data
      BEGIN
         SELECT EXTRACTVALUE (xmldata, '/PDATA/EVD') "REFERENCE"
           INTO DATA
           FROM DUAL
          WHERE EXISTSNODE (xmldata, '/PDATA/EVD') = 1;

         DBMS_OUTPUT.put_line ('EVD_DATA =>EVD Data: ' || DATA);
         evd_val_tab := parse_str_fnc (DATA, '||');

         FOR j IN 1 .. evd_val_tab.COUNT
         LOOP
            DBMS_OUTPUT.put_line ('--EVD Value: ' || evd_val_tab (j));
            evd_sub_val_tab := parse_str_fnc (evd_val_tab (j), '~');

            FOR i IN 1 .. evd_sub_val_tab.COUNT
            LOOP
               DBMS_OUTPUT.put_line ('--EVD Subvalue: ' || evd_sub_val_tab (i)||' i= '||i);
            END LOOP;
         END LOOP;
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            ROLLBACK;
            pout_error := 1;
            pout_error_msg := 'Evh tag (<EVD>) is missing';
            pout_time_stamp := NULL;
            pout_source_id := NULL;
            --pOutEndHdrPos     := NULL;
            DBMS_OUTPUT.put_line ('EVD_DATA EVD Data: ' || DATA);
            RETURN;
      END;
        
        if evh_val_tab(1) = 18 then
        DBMS_OUTPUT.put_line ('EVH_DATA EVD Data: ' || evh_val_tab(1)||' sub 3 '||evd_sub_val_tab(3));
        end if;
        
        
   END parse_xml_import_prc;
END parse_xml_pkg_2;
/
0
Comment
Question by:dplinnane
[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
  • 3
  • 2
6 Comments
 
LVL 14

Accepted Solution

by:
sathyagiri earned 500 total points
ID: 17044651
EXTRACTVALUE() can return only one node. But your XML has multiple nodes for /PDATA/EVH. Try using extract() instead.

refer http://www.utexas.edu/its/unix/reference/oracledocs/v92/B10501_01/appdev.920/a96620/xdb03usg.htm
0
 

Author Comment

by:dplinnane
ID: 17045352
If I do the following  

         SELECT EXTRACTVALUE (xmldata, '/PDATA/EVH[1]') "REFERENCE"
           INTO DATA
           FROM DUAL
          WHERE EXISTSNODE (xmldata, '/PDATA/EVH') = 1;

where I set PDATA/EVH[1] this will grab the first node, I would like to be able to iterate through or some how do a count of the number of the EVH nodes.

I tried EXTRACT but got an error
LS-00801: internal error [*** ASSERT at file pdw4.c, line  793; Cannot coerce between type 43 and type 30; PARSE_XML_PKG_2__ADMIN__B__14258[94, 10]]
0
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17046253
SELECT EXTRACT(xmldata, '/PDATA/EVH[1]').getStringval "REFERENCE"
           INTO DATA from DUAL
WHERE EXISTSNODE (xmldata, '/PDATA/EVH') = 1;


Refer to this post
http://forums.oracle.com/forums/thread.jspa?forumID=34&messageID=975305&threadID=301520&q=
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

Author Comment

by:dplinnane
ID: 17047083
I cam across something similar but I do not want to creat a table for performance reasons. I want to be able to do something like this.
When I run the following block of code I get a count of zero. I wondering could I use a regular expression to count certain types of tags. It seems odd that this functionality in xml is not available.

DECLARE
n_count  varchar2(1000) :=0;
  PIN_PRICE_EVENT_MSG CLOB;
      xmldata   XMLTYPE;
      DATA      VARCHAR2 (2000);

BEGIN
PIN_PRICE_EVENT_MSG :='<?xml version="1.0"?><PDATA><HDR>20060703190133~RMS</HDR><EVH>31~00006~204745175~P~Y~N~20060704000100~20060712235900~Prom 1 - Laks</EVH><EVD>204745175~04608437~14.99~A~A</EVD><EVH>18~00006~204745175~P~Y~N~20060704000100~20060712235900~Prom 1 - Laks</EVH><EVD>18-204745175~04608455~8.99~A~A</EVD><EVH>31~00006~204745176~P~Y~N~20060704000100~20060712235900~Prm 2 - Laks</EVH><EVD>204745176~04622331~14.49~A~S</EVD><EVH>31~00006~204745176~P~Y~N~20060704000100~20060712235900~Prm 2 - Laks</EVH><EVD>204745176~04622439~13.49~A~S</EVD><EVH>31~00010~204745175~P~Y~N~20060704000100~20060712235900~Prom 1 - Laks</EVH><EVD>204745175~04608437~14.99~A~A</EVD><EVH>31~00010~204745175~P~Y~N~20060704000100~20060712235900~Prom 1 - Laks</EVH><EVD>204745175~04608455~8.99~A~A</EVD><EVH>31~00010~204745176~P~Y~N~20060704000100~20060712235900~Prm 2 - Laks</EVH><EVD>204745176~04622331~14.49~A~S</EVD><EVH>31~00010~204745176~P~Y~N~20060704000100~20060712235900~Prm 2 - Laks</EVH><EVD>204745176~04622439~13.49~A~S</EVD></PDATA>';

xmldata := XMLTYPE.createxml (pin_price_event_msg);
SELECT COUNT(EXTRACT  (xmldata, '/PDATA/HDR/EVH').getStringVal())
           INTO n_count
           FROM DUAL
           WHERE EXISTSNODE (xmldata, '/PDATA/HDR/EVH') = 1;
              DBMS_OUTPUT.put_line ('n_count 1 :' || n_count);
END;
0
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17047778
To print all values for  /PDATA/EVH try this example
  DECLARE
   n_count  varchar2(1000) :=0;
    PIN_PRICE_EVENT_MSG CLOB;
        xmldata   XMLTYPE;
         DATA      VARCHAR2 (2000);
cursor c is SELECT extractvalue(value(v),'/EVH') a  from table(XMLSequence(extract(xmldata,'/PDATA/EVH'))) v;
   BEGIN
   PIN_PRICE_EVENT_MSG :='<?xml version="1.0"?><PDATA><HDR>20060703190133~RMS</HDR><EVH>31~00006~204745175~P~Y~N~20060704000100~20060712235900~Prom 1 - La
ks</EVH><EVD>204745175~04608437~14.99~A~A</EVD><EVH>18~00006~204745175~P~Y~N~20060704000100~20060712235900~Prom 1 - Laks</EVH><EVD>18-204745175~04608455~8.9
9~A~A</EVD><EVH>31~00006~204745176~P~Y~N~20060704000100~20060712235900~Prm 2 - Laks</EVH><EVD>204745176~04622331~14.49~A~S</EVD><EVH>31~00006~204745176~P~Y~
N~20060704000100~20060712235900~Prm 2 - Laks</EVH><EVD>204745176~04622439~13.49~A~S</EVD><EVH>31~00010~204745175~P~Y~N~20060704000100~20060712235900~Prom 1
- Laks</EVH><EVD>204745175~04608437~14.99~A~A</EVD><EVH>31~00010~204745175~P~Y~N~20060704000100~20060712235900~Prom 1 - Laks</EVH><EVD>204745175~04608455~8.
99~A~A</EVD><EVH>31~00010~204745176~P~Y~N~20060704000100~20060712235900~Prm 2 - Laks</EVH><EVD>204745176~04622331~14.49~A~S</EVD><EVH>31~00010~204745176~P~Y
~N~20060704000100~20060712235900~Prm 2 - Laks</EVH><EVD>204745176~04622439~13.49~A~S</EVD></PDATA>';
   xmldata := XMLTYPE.createxml (pin_price_event_msg);
for i in  c
loop
dbms_output.put_line(i.a);
end loop;
  END;
/
0
 
LVL 19

Expert Comment

by:actonwang
ID: 17047986
>>When I run the following block of code I get a count of zero. I wondering could I use a regular expression to count certain types of tags.

as I posted in other post, you could use transfrom and xsl stylesheet to do it clearly and easily:

//////////////////////////////
DECLARE
      n_count  varchar2(1000) :=0;
      textdata varchar2(1000) ;      

      PIN_PRICE_EVENT_MSG CLOB;
      xmldata   XMLTYPE;
      xsldata   XMLTYPE;
      resultdata XMLTYPE;

      DATA      VARCHAR2 (2000);
BEGIN
PIN_PRICE_EVENT_MSG :='<?xml version="1.0"?><PDATA><HDR>20060703190133~RMS</HDR><EVH>31~00006~204745175~P~Y~N~20060704000100~20060712235900~Prom 1 - Laks</EVH><EVD>204745175~04608437~14.99~A~A</EVD><EVH>18~00006~204745175~P~Y~N~20060704000100~20060712235900~Prom 1 - Laks</EVH><EVD>18-204745175~04608455~8.99~A~A</EVD><EVH>31~00006~204745176~P~Y~N~20060704000100~20060712235900~Prm 2 - Laks</EVH><EVD>204745176~04622331~14.49~A~S</EVD><EVH>31~00006~204745176~P~Y~N~20060704000100~20060712235900~Prm 2 - Laks</EVH><EVD>204745176~04622439~13.49~A~S</EVD><EVH>31~00010~204745175~P~Y~N~20060704000100~20060712235900~Prom 1 - Laks</EVH><EVD>204745175~04608437~14.99~A~A</EVD><EVH>31~00010~204745175~P~Y~N~20060704000100~20060712235900~Prom 1 - Laks</EVH><EVD>204745175~04608455~8.99~A~A</EVD><EVH>31~00010~204745176~P~Y~N~20060704000100~20060712235900~Prm 2 - Laks</EVH><EVD>204745176~04622331~14.49~A~S</EVD><EVH>31~00010~204745176~P~Y~N~20060704000100~20060712235900~Prm 2 - Laks</EVH><EVD>204745176~04622439~13.49~A~S</EVD></PDATA>';

xsldata := XMLTYPE.createxml('<?xml version="1.0"?>
<xsl:stylesheet version="1.0"
                xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:template match="/PDATA">
    <result><xsl:value-of select="count(//EVD)"/></result>
  </xsl:template>
</xsl:stylesheet>');

xmldata := XMLTYPE.createxml (pin_price_event_msg);

resultdata :=  xmldata.transform(xsldata);
n_count := resultdata.extract('/result/text()').getStringVal();

DBMS_OUTPUT.put_line ('n_count 1 :' || n_count);

END;
/
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
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

696 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