Solved

ORA-19025 EXTRACTVALUE returns value of only one node

Posted on 2006-07-05
6
6,118 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Oracle 12c 10 100
Need help on Index -Oracle -Challenging question 5 44
oracle query help 36 66
query returning everything 11 67
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to recover a database from a user managed backup

707 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now