• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 9740
  • Last Modified:

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;
/
0
dplinnane
Asked:
dplinnane
  • 3
  • 2
1 Solution
 
sathyagiriCommented:
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
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now