ORA-19025 EXTRACTVALUE returns value of only one node

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;
/
dplinnaneAsked:
Who is Participating?
 
sathyagiriConnect With a Mentor Commented:
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
 
dplinnaneAuthor Commented:
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
 
sathyagiriCommented:
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
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.

 
dplinnaneAuthor Commented:
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
 
sathyagiriCommented:
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
 
actonwangCommented:
>>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
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.

All Courses

From novice to tech pro — start learning today.