Home » SQL & PL/SQL » SQL & PL/SQL » ORA-19025 on Binary XML Table Unique Constraint (merged by CM) (Oracle 11g, Release 11.1.0.6, Red Hat Linux 5.2)
ORA-19025 on Binary XML Table Unique Constraint (merged by CM) [message #441199] Fri, 29 January 2010 07:01 Go to next message
calluru
Messages: 17
Registered: July 2009
Location: Austin, TX, USA
Junior Member
Hi,

I am trying to setup a Binary XML table. After several iterations I have finished creating the XSD, Register it in the database, create a binary XML table, insert XML messages into the table and retrieve them in a table format.

My XML messages are coming in the below sample format:

<messages>
   <message>
      <ID>1</ID>
      <VALUE>Test1</VALUE>
   <message>
   <message>
      <ID>2</ID>
      <VALUE>Test1</VALUE>
   <message>
</messages>


Then I came across the VIRTUAL COLUMNS option in the Binary XML table. So I created an ID virtual column and added the unique key constraint. Here's the table and constraint DDL.

CREATE TABLE xmltable_bin_vc OF XMLType
XMLTYPE STORE AS SECUREFILE BINARY XML (COMPRESS CACHE)
XMLSCHEMA "http://localhost/public/xsd/xmltable.xsd"
ELEMENT "messages"
VIRTUAL COLUMNS
    (id AS (extractValue(OBJECT_VALUE, '/messages/message/ID')));


ALTER TABLE xmltable_bin_vc ADD CONSTRAINT uk_ xmltable_bin_vc UNIQUE (id);


The constraint is working as expected when I tested with one message per XML file. But I am getting "ORA-19025" when I have more than one message in the XML file. I am thinking I should be using TABLE() and/or XMLSEQUENCE() functions in the constraint specification.

Is it possible to create such a constraint? Has anyone did this before? Any pointers will be helpful.

Thanks,
Balaji
Re: ORA-19025 [message #441200 is a reply to message #441199] Fri, 29 January 2010 07:03 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
ORA-19025:	EXTRACTVALUE returns value of only one node
Cause:	Given XPath points to more than one node.
Action:	Rewrite the query so that exactly one node is returned.


Work around for ORA-19025

sriram Smile
Re: ORA-19025 [message #441205 is a reply to message #441200] Fri, 29 January 2010 07:11 Go to previous messageGo to next message
calluru
Messages: 17
Registered: July 2009
Location: Austin, TX, USA
Junior Member
I can read the ora errors. If you could read my post I was asking for suggestions to rewrite.

-- Balaji
Re: ORA-19025 [message #441206 is a reply to message #441200] Fri, 29 January 2010 07:11 Go to previous messageGo to next message
calluru
Messages: 17
Registered: July 2009
Location: Austin, TX, USA
Junior Member
Sorry I didn't see your work around link
Re: ORA-19025 [message #441207 is a reply to message #441200] Fri, 29 January 2010 07:16 Go to previous messageGo to next message
calluru
Messages: 17
Registered: July 2009
Location: Austin, TX, USA
Junior Member
I have written those select statements and actually created views using that to convert XML messages to Column-Row format. I think I might have come across that link too.

But I am not able to rewrite it for constraint.

I need help with writing the unique constraint.

Thanks
Balaji
Re: ORA-19025 on Binary XML Table Unique Constraint (merged by CM) [message #441223 is a reply to message #441199] Fri, 29 January 2010 09:48 Go to previous messageGo to next message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
I don't think your problem is with the constraint. I think the issue is with your virtual column definition:

extractValue(OBJECT_VALUE, '/messages/message/ID'

If you post code for duplicating everything, I might be able to try it myself. Without it I am only guessing. I am guessing that the xml you use inserts into the table as one row but the extract expression could return more than one value.

Kevin
Re: ORA-19025 on Binary XML Table Unique Constraint (merged by CM) [message #441240 is a reply to message #441223] Fri, 29 January 2010 11:41 Go to previous messageGo to next message
calluru
Messages: 17
Registered: July 2009
Location: Austin, TX, USA
Junior Member
Hi,

Thanks for the response. I agree with you. I need to find the correct definition for my virtual columns. Here's the working example that I have been testing.


/*************** BinaryXMLTableTest.xsd ***************

<?xml version="1.0" encoding="UTF-8" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
            targetNamespace="http://www.example.com"
            xmlns="http://www.example.com"
            elementFormDefault="qualified">
  <xsd:element name="messages">
    <xsd:annotation>
      <xsd:documentation>
        A sample element
      </xsd:documentation>
    </xsd:annotation>
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element name="message" maxOccurs="unbounded">
          <xsd:complexType>
            <xsd:sequence>
              <xsd:element name="ID"/>
              <xsd:element name="VALUE"/>
            </xsd:sequence>
          </xsd:complexType>
        </xsd:element>
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>

***************/

DROP USER xmltest CASCADE;

CREATE USER xmltest IDENTIFIED BY xmltest;

GRANT dba, xdbadmin TO xmltest;

CONNECT xmltest/xmltest

CREATE DIRECTORY XMLTESTDIR AS '/tmp';

DECLARE
   XMLdoc XMLType;
   l_targetresource VARCHAR2(100) := '/public/BinaryXMLTableTest.xsd';
BEGIN
   XMLdoc := (XMLTYPE(bfilename('XMLTESTDIR', 'BinaryXMLTableTest.xsd'), NLS_CHARSET_ID('AL32UTF8')));

   IF DBMS_XDB.EXISTSRESOURCE(l_targetresource) THEN
      DBMS_XDB.DELETERESOURCE(l_targetresource, DBMS_XDB.DELETE_RECURSIVE);
      DBMS_OUTPUT.PUT_LINE('Resource is dropped');
   END IF;

   IF DBMS_XDB.CREATERESOURCE(l_targetresource, XMLdoc)
   THEN
      DBMS_OUTPUT.PUT_LINE('Resource is created');
   ELSE
      DBMS_OUTPUT.PUT_LINE('Cannot create resource');
   END IF;

   COMMIT;
END;
/

SET LONG 100000000 PAGESIZE 500

SELECT xdbURIType('/public/BinaryXMLTableTest.xsd').getClob() FROM dual;

BEGIN
   DBMS_XMLSCHEMA.DELETESCHEMA('http://www.example.com/public/xsd/BinaryXMLTableTest.xsd');
END;
/

BEGIN
   DBMS_XMLSCHEMA.REGISTERSCHEMA(SCHEMAURL => 'http://www.example.com/public/xsd/BinaryXMLTableTest.xsd',
                                 SCHEMADOC => xdbURIType('/public/BinaryXMLTableTest.xsd').getClob(),
                                 LOCAL     => FALSE,  -- local
                                 GENTYPES  => FALSE,  -- generate object types
                                 GENBEAN   => FALSE,  -- no java beans
                                 GENTABLES => FALSE,  -- generate object tables
                                 FORCE     => FALSE,
                                 OPTIONS   => DBMS_XMLSCHEMA.REGISTER_BINARYXML,
                                 OWNER     => USER);
END;
/

CREATE TABLE BinaryXMLTableTest OF XMLType
XMLTYPE STORE AS SECUREFILE BINARY XML
XMLSCHEMA "http://www.example.com/public/xsd/BinaryXMLTableTest.xsd"
ELEMENT "messages"
VIRTUAL COLUMNS
    (id    AS (extractValue(OBJECT_VALUE, '/messages/message/ID'))
    ,value AS (extractValue(OBJECT_VALUE, '/messages/message/VALUE')));

ALTER TABLE BinaryXMLTableTest ADD CONSTRAINT uk_BinaryXMLTableTest UNIQUE (id);

--- Testing Single Message

DECLARE
   l_xml_message VARCHAR2(2000) := '<?xml version="1.0" encoding="UTF-8"?> ' ||
                                   '<messages xmlns="http://www.example.com">' ||
                                   '<message>' ||
                                   '<ID>1111</ID>' ||
                                   '<VALUE>1111</VALUE>' ||
                                   '</message>' ||
                                   '</messages>';
BEGIN
   dbms_output.put_line(l_xml_message);
   INSERT INTO BinaryXMLTableTest VALUES (XMLType(l_xml_message));
   COMMIT;
END;
/

SELECT * FROM BinaryXMLTableTest;

--- Testing Multiple Messages

DECLARE
   l_xml_message VARCHAR2(2000) := '<?xml version="1.0" encoding="UTF-8"?> ' ||
                                   '<messages xmlns="http://www.example.com">' ||
                                   '<message>' ||
                                   '<ID>2222</ID>' ||
                                   '<VALUE>2222</VALUE>' ||
                                   '</message>' ||
                                   '<message>' ||
                                   '<ID>3333</ID>' ||
                                   '<VALUE>3333</VALUE>' ||
                                   '</message>' ||
                                   '</messages>';
BEGIN
   dbms_output.put_line(l_xml_message);
   INSERT INTO BinaryXMLTableTest VALUES (XMLType(l_xml_message));
   COMMIT;
END;
/




Thanks,
Balaji
Re: ORA-19025 on Binary XML Table Unique Constraint (merged by CM) [message #441246 is a reply to message #441199] Fri, 29 January 2010 13:28 Go to previous messageGo to next message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
Yes but when execute that insert statement, it creates only one row not two right?

The extractvalue expects only one hit but since the xml you store is one row not two (one for each message) you get two hits.

I think what you might need to do is split the xml up when you insert it so that you create two rows, one for each message. Then the extract will return only one value per row.

Check out these two reads for some ideas. The first one is most relevant to you I think. It will show you how to unpack your xml and insert two rows, one per message. Then your extractvalue should work.

Easy XML - a Programming Oriented Approach

Easy XML - Let the Database do the Work

Alternatively, maybe you can figure out how to remap the data as some kind of nested table type wherein the nested objects are the unpacked single messages. Then you can create a unique index on the nested table inside your column.

Kevin
Re: ORA-19025 on Binary XML Table Unique Constraint (merged by CM) [message #441253 is a reply to message #441246] Fri, 29 January 2010 17:45 Go to previous messageGo to next message
calluru
Messages: 17
Registered: July 2009
Location: Austin, TX, USA
Junior Member
Hi Kevin,

I read both your links. They are great resources. I have bookmarked them. After reading them and your response, I re-wrote my test case as below. It works great. Now I can receive XML data with as many messages in there, below test case splits them and inserts them individually. Hence the unique key constraint is enforced. Here's my new code:

CREATE TABLE BinaryXMLTableTest OF XMLType
XMLTYPE STORE AS SECUREFILE BINARY XML
VIRTUAL COLUMNS
    (id    AS (extractValue(OBJECT_VALUE, '/message/ID'))
    ,value AS (extractValue(OBJECT_VALUE, '/message/VALUE')));

ALTER TABLE BinaryXMLTableTest ADD CONSTRAINT uk_BinaryXMLTableTest UNIQUE (id);

--- Testing Single Message

DECLARE
   l_xml_message VARCHAR2(2000) := '<message>' ||
                                   '<ID>1111</ID>' ||
                                   '<VALUE>1111</VALUE>' ||
                                   '</message>';
BEGIN
   dbms_output.put_line(l_xml_message);
   INSERT INTO BinaryXMLTableTest VALUES (XMLType(l_xml_message));
   COMMIT;
END;
/

SELECT * FROM BinaryXMLTableTest;

COLUMN id FORMAT 999999
COLUMN value FORMAT A15

SELECT TO_NUMBER(id) id, value FROM BinaryXMLTableTest;

--- Testing Multiple Messages

INSERT INTO BinaryXMLTableTest
WITH xml_data AS
   (SELECT XMLTYPE('<messages>' ||
                   '<message>' ||
                   '<ID>2222</ID>' ||
                   '<VALUE>2222</VALUE>' ||
                   '</message>' ||
                   '<message>' ||
                   '<ID>3333</ID>' ||
                   '<VALUE>3333</VALUE>' ||
                   '</message>' ||
                   '</messages>') xml_data
    FROM DUAL)
SELECT x.column_value
FROM   xml_data
      ,TABLE(XMLSEQUENCE(EXTRACT(xml_data.xml_data, '/messages/message'))) x;

--- Testing Unique Key Constraint

INSERT INTO BinaryXMLTableTest
WITH xml_data AS
   (SELECT XMLTYPE('<messages>' ||
                   '<message>' ||
                   '<ID>4444</ID>' ||
                   '<VALUE>4444</VALUE>' ||
                   '</message>' ||
                   '<message>' ||
                   '<ID>3333</ID>' ||
                   '<VALUE>3333</VALUE>' ||
                   '</message>' ||
                   '</messages>') xml_data
    FROM DUAL)
SELECT x.column_value
FROM   xml_data
      ,TABLE(XMLSEQUENCE(EXTRACT(xml_data.xml_data, '/messages/message'))) x;


Thank you very much,
Balaji
Re: ORA-19025 on Binary XML Table Unique Constraint (merged by CM) [message #441254 is a reply to message #441199] Fri, 29 January 2010 18:38 Go to previous message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
WAY TO GO Balaji!

It is a great pleasure to help someone who is willing to put in the effort to help themselves. You clearly are a hard working and knowlegeable Oracle developer.

It occurrs to me that someone of your caliber will have several special experiences in their work history. Maybe you would consider writing an article about one of them for OraFaq? We are always on the lookout for a well thought out discussion of an unusual or interesting topic.

Congratutlations on your success.

And thank you for taking the time to post your solution so that others can benefit from it.

Best Regards, Kevin
Previous Topic: Converting columns into rows
Next Topic: Calculation of percentage based on total
Goto Forum:
  


Current Time: Wed Sep 28 10:56:24 CDT 2016

Total time taken to generate the page: 0.13231 seconds