Oracle 11g BUG when using deferred constraints

Found a BUG when using deferred constraints on Oracle DB version Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production.

Here is the test case:
First we create two tables. Table P1 is the parent table, which has a primary key on column ID. The second table C1 is a table (child) with a foreign key constraint which is deferrable and references the parent table.

Create the two tables:

CREATE TABLE P1 (ID NUMBER);
CREATE TABLE C1 (ID NUMBER, X VARCHAR2(2));

Create the primary key and foreign key constraints:

ALTER TABLE P1 ADD CONSTRAINT PK_ID PRIMARY KEY (ID);
ALTER TABLE C1 ADD CONSTRAINT FK_P1_ID FOREIGN KEY (ID) REFERENCES P1 (ID) DEFERRABLE;

We insert same test data into the two tables:

INSERT INTO P1 VALUES (1);
INSERT INTO C1 VALUES (1,'X1');
INSERT INTO C1 VALUES (1,'X2');

Now we create the test to delete some data, while switching the constraints to deferred. We try to delete the "parent" record, and one delete one of the child records in the child table. We leave on child record. After the delete statements we switch back the constraints to immediate:

SET CONSTRAINTS ALL DEFERRED;

DELETE P1 WHERE ID = 1;
DELETE C1 WHERE X = 'X1';

SET CONSTRAINTS ALL IMMEDIATE;

When we execute the final statement (SET CONSTRAINTS ALL IMMEDIATE) we get an error ORA-02292: integrity constraint (schema.FK_P1_ID) violated - child record found.

This is what we expected and is of course correct.

But here is the trick. If we add an index to the table C1 on column ID something weird happens.
Lets create an index:

CREATE INDEX IND_1 ON C1 (ID);

Now we try the test again:

SET CONSTRAINTS ALL DEFERRED;

DELETE P1 WHERE ID = 1;
DELETE C1 WHERE X = 'X1';

SET CONSTRAINTS ALL IMMEDIATE;

After executing the last statement we would expect the same error as we got before, but we didn't receive any error. We corrupted the data and now have corrupt data in the database. If we now look at the data in the two tables, we can see that the parent record is deleted, and we also deleted one child record, but the second child record is in the database and the table has a foreign key which references to the parent table:

SELECT * FROM P1;
SELECT * FROM C1;

Cloud computing?

First non technical post from me. Cloud computing isn't a new idea, but I found video (audio), which I totaly agree with about cloud computing. Thought I was the only one who thinks like this, apparently not :)

Creating materialized views

Materialized views are a great way of keeping the same data at two different locations (databases).

At the "source" database you have to create a materialized view log on the desired table. You do this with the following command:

create materialized view log on TEST_TABLE with sequence, primary key including new values;

The above command is used when the table has primary key.

When you execute the above command two system tables are generated:
- mlog$_test_table - log table for the changes made to the source table
- rupd$_test_table - log table for controlling the correct order of changes, when a table has primary key

The "destination" database has to have select privileges on the source table (test_table) and the log table (mlog$_test_table).
At the "destination" database execute the following command:

create materialized view TEST_TABLE refresh fast on demand enable query rewrite as select * from TEST_TABLE@SOURCE_DB_LINK ;

You have different options to control writing the changes to the destination, either the refresh fast or refress complete. More on this can be found here.

To manualy copy the changes from the source to the destination database, execute the following command on the destination database:

begin
dbms_mview.refresh('TEST_TABLE');
end;

UTL_MATCH

Came across an interesting Oracle supplied package called UTL_MATCH. It is used for comparing string values. If for example you want the user to be 90% exact when typing a certain string value you can use the function edit_distance_similarity and embed it into a program logic.

select UTL_MATCH.edit_distance_similarity ('programmer', 'programer')
from dual;

Detailed description of the package can be found here

Generating documentation from the database

Made a relatively simple function that generates HTML output of all the database objects which can be used as a database documentation if used correctly.

The whole function can be downloaded here.

Working with Oracle object types based on XSD schemas

Attended the SIOUG (Slovenian Oracle User Group) Conference 2010, where I had a lecture "Working with Oracle object types based on XSD schemas".

The power point presentation can be downloaded here (some text is in slovenian language!):
- PPTX format
- PPT format

Reading XML data directly from web

The following select statement reads the data directly from the specified URL and displayes the result as an SQL result:

select to_date(a.extract('tecajnica/@datum','xmlns="http://www.bsi.si"').getstringval(), 'YYYY-MM-DD') as datum
, b.extract('tecaj/@oznaka','xmlns="http://www.bsi.si"').getstringval() as oznaka
, b.extract('tecaj/@sifra','xmlns="http://www.bsi.si"').getstringval() as sifra
, to_number(b.extract('tecaj/text()','xmlns="http://www.bsi.si"').getstringval(),'999999999999999D9999999999', 'NLS_NUMERIC_CHARACTERS=''.,''') as tecaj
from table(xmlsequence(xmltype(urifactory.getUri('http://www.bsi.si/_data/tecajnice/dtecbs-l.xml').getclob()
).extract('DtecBS/tecajnica','xmlns="http://www.bsi.si"')
)
) a
, table(xmlsequence(a.extract('tecajnica/tecaj','xmlns="http://www.bsi.si"'))) b
;

Reading files from directory

First we need to create a directory in oracle that will point to a location on the disk.

CREATE OR REPLACE DIRECTORY ctemp AS 'c:\test';

We can see the directory in the system view dba_directories.

This procedure reads the file and stores it as a blob in a table test.

create or replace procedure read_blob(p_dir varchar2, p_file_name varchar2) is
  v_blob         blob;
  v_bfile        bfile;
  v_dest_offset  pls_integer;
  v_src_offset   pls_integer;
  v_name varchar2(100) := p_file_name;
begin
      dbms_lob.createtemporary(v_blob, true, dbms_lob.session);
      v_bfile := bfilename(p_dir, v_name);
      dbms_lob.fileopen(v_bfile, dbms_lob.file_readonly);
      v_dest_offset  := 1;
      v_src_offset   := 1;      
      dbms_lob.loadblobfromfile(v_blob, v_bfile, dbms_lob.lobmaxsize, v_dest_offset, v_src_offset);
      
      insert into test
        (name, content)
      values
        (v_name, v_blob);      

      dbms_lob.fileclose(v_bfile);
      dbms_lob.freetemporary(v_blob);
end read_blob;

Oracle BUG when using xmltype() function

I found a bug on Oracle DB when using types. The problem occours when you try to make an XML data using xmltype function from types that have more than 3 subtypes.
Here is an example of the test code:

CREATE OR REPLACE TYPE D_T AS OBJECT(F varchar2(1),G varchar2(2))
/
CREATE OR REPLACE TYPE C_T AS OBJECT (D1 D_T, D2 D_T)
/
CREATE OR REPLACE TYPE B_T AS OBJECT(C C_T)
/
CREATE OR REPLACE TYPE A_T AS OBJECT(B B_T)
/
declare
 v_a a_t;
begin                       
   v_a := a_t(
              b_t(
                  c_t(
                      null,
                      d_t('1','2')
                      )
                  )
              );
   dbms_output.put_line(xmltype(v_a).extract('*').getClobVal);
   v_a := a_t(
              b_t(
                  c_t(
                      d_t(null,null),
                      d_t('1','2')
                      )
                  )
              );
   dbms_output.put_line(xmltype(v_a).extract('*').getClobVal);
end;
/

The first dbms_output prints out:

<A_T>
  <B/>
</A_T>

The second one prints out:

<A_T>
  <B>
    <C>
      <D1/>
      <D2>
        <F>1</F>
        <G>2</G>
      </D2>
    </C>
  </B>
</A_T>

The output on both should be the almost the same with one difference. We can see that in the first output, the data from c_t type gets lost. The data is stored in the v_a variable but xmltype doesn't show it. The correct output from the first dbms_output should be (with C element that also contains D2 and is without D1 element):

<A_T>
  <B>
    <C>
      <D2>
        <F>1</F>
        <G>2</G>
      </D2>
    </C>
  </B>
</A_T>

The problem was tested both on 10g and 11g version of DB.

Simple blob to clob conversion

Function converts blob to clob.

create or replace function blob_to_clob(p_blob blob) return clob is
declare 
 v_blob blob; 
 v_clob clob := 'x'; 
 v_dest_offsset integer := 1; 
 v_src_offsset integer := 1; 
 v_lang_context integer := dbms_lob.default_lang_ctx;
 v_warning integer; 
BEGIN 
 dbms_lob.converttoclob 
 ( dest_lob => v_clob 
 , src_blob => p_blob 
 , amount => dbms_lob.lobmaxsize 
 , dest_offset => v_dest_offsset 
 , src_offset => v_src_offsset 
 , blob_csid => dbms_lob.default_csid 
 , lang_context => v_lang_context 
 , warning => v_warning 
 ); 
 return v_clob;
END;
/

In case of using a different charset change the value of blob_csid to blob_csid => nls_charset_id('X') where X is a for example UTF8.

Writing XML data

Basic Oracle functions that allows you to manipulate xml data are:
- XMLAGG
- XMLCDATA
- XMLCOLATTVAL
- XMLCOMMENT
- XMLCONCAT
- XMLELEMENT
- XMLFOREST
- XMLPARSE
- XMLPI
- XMLQUERY
- XMLROOT
- XMLSEQUENCE
- XMLSERIALIZE
- XMLTABLE
- XMLTRANSFORM

Here is a simple example of an sql statement using xmlelement and xmlagg function:

select xmlelement
       ("People",
           (select xmlagg(xmlelement ("Person",
                          xmlelement("Id", p.id),
                          xmlelement("FirstName", p.firstname),
                          xmlelement("LastName", p.lastname))
                          )
            from person p)
       ).getClobVal()
 from dual

If you are using registered xsd schemas and types, you can just use the following function:

create or replace function from_people (p_people tro004_t) return clob is 
 v_schema_name varchar2(10) := 'People.xsd';
 v_schema_elem varchar2(10) := 'People';
begin
   RETURN xmltype(xmldata => p_people, schema => v_schema_name, element => v_schema_elem).getClobVal;
end;
/

Reading XML data

Here is a simple example of reading xml data with an SQL statement:

select extractvalue(value(a), '//Id'),
       extractvalue(value(a), '//FirstName'),
       extractvalue(value(a), '//LastName')
  from table(xmlsequence(extract(xmltype('<People>
                                           <Person>
                                            <Id>1</Id>
                                            <FirstName>First1</FirstName>
                                            <LastName>Last1</LastName>
                                           </Person>
                                           <Person>
                                            <Id>2</Id>
                                            <FirstName>First2</FirstName>
                                            <LastName>Last2</LastName>
                                           </Person>
                                          </People>')
                                          ,'//Person' ))) a

If you have more complex xml you can read data using XPath expressions.

For reading XML into objects of registered types I wrote a function that returns the registered object based on the input parameter. This function also validates the xml data against the registered schema.

create or replace function to_people(p_xml clob) return people_t is
 v_people people_t;
 v_xml xmltype;
 v_schema_name varchar2(10) := 'People.xsd';
 v_schema_elem varchar2(10) := 'People';
begin
 v_xml := xmltype(p_xml);
 IF v_xml.isSchemaValid(v_schema_name, v_schema_elem) = 1 THEN
  v_xml.extract('*').toobject(v_people,v_schema_name,v_schema_elem);
  dbms_output.put_line('Valid');
  return v_people;
 ELSE
  dbms_output.put_line('Invalid');
  return null; 
 END IF;
end;

If you have a table (e.g. person_table) that has an element (e.g. data) of type person_t, you can easily read data from the table using SQL statements.

select pe.ID, pe.FIRSTNAME, pe.LASTNAME 
 from people_table p,
      table(p.people.data) pe

Changing XML schema to "Oracle" XML schema

In order to have better control over the XMLschema (or types) which is registered in Oracle DB we have to make some changes to it.
First add a new namespace xmlns:xdb="http://xmlns.oracle.com/xdb", which is the Oracle-supplied XML DB namespace. This will let you use additional "features" in XML schema:
- xdb:SQLType
- xdb:SQLName
- xdb:SQLCollType
- xdb:maintainDOM
- xdb:maintainOrder
- ...
I only wrote the ones we will need in this post.

The SQLType, SQLName, SQLCollType is used to name types, elements and collections and to overide the names given in the xsd schema. I suggest you use uppercase characters (this way you will avoid using double quotes when reffering to the elements,type,... (e.g. "Person"."Id")).
The usege of this three atributes is recommended, because this way, the names of types don't get automaticaly generated and you can be sure the name stays the same, even if you reregister the schema.

The default value of maintainDom is true. It insures DOM fidelity. Document Object Model (DOM) fidelity is the concept of retaining the structure of a retrieved XML document, compared to the original XML document, for DOM traversals. DOM fidelity is needed to ensure the accuracy and integrity of XML documents stored in Oracle XML DB.
The default value automaticaly generates a SYS_XDBPD$ element where there is a complex type. This element is of type xdb.xdb$raw_list_t.

If the XML element is declared with attribute maxOccurs > 1, then it is mapped to a collection attribute in SQL. The collection could be a varray value (default) or nested table if the maintainOrder attribute is set to false.

If you will be using the registered schema for creating objects of generated types I suggest you set the value to false for both (maintainDom and maintainOrder). It will be easier to create and manipulate objects based on their types.

The schema from the first post would now look like this:

<?xml version="1.0" encoding="utf-8"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
            xmlns:xdb="http://xmlns.oracle.com/xdb">
<xsd:element name="Id">
  <xsd:simpleType>
   <xsd:restriction base="xsd:integer"></xsd:restriction></xsd:simpleType>
</xsd:element>
<xsd:element name="FirstName">
  <xsd:simpleType>
   <xsd:restriction base="xsd:string"><xsd:maxLength value="35" /></xsd:restriction>
  </xsd:simpleType>
</xsd:element>
<xsd:element name="LastName">
  <xsd:simpleType>
   <xsd:restriction base="xsd:string"><xsd:maxLength value="35" /></xsd:restriction>
  </xsd:simpleType>
</xsd:element>
<xsd:element name="Person" xdb:SQLType="PERSON_T" xdb:maintainDOM="false" xdb:maintainOrder="false">
 <xsd:complexType>
   <xsd:sequence>
     <xsd:element ref="Id" xdb:SQLName="ID"/>
     <xsd:element ref="FirstName" xdb:SQLName="FIRSTNAME"/>
     <xsd:element ref="LastName" xdb:SQLName="LASTNAME"/>
   </xsd:sequence>
 </xsd:complexType>
</xsd:element>
<!-- MAIN ELEMENT -->
<xsd:element name="People" xdb:SQLType="PEOPLE_T" xdb:maintainDOM="false" xdb:maintainOrder="false">
 <xsd:complexType>
   <xsd:sequence>
     <xsd:element ref="Person" minOccurs="0" maxOccurs="unbounded" xdb:SQLCollType="PERSON_C" xdb:SQLName="PERSON"/>
   </xsd:sequence>
 </xsd:complexType>
</xsd:element>
</xsd:schema>

You can get more information here.

Registering XML schema in Oracle

We would like to register the following XML schema in Oracle:

<?xml version="1.0" encoding="utf-8"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:element name="Id">
  <xsd:simpleType>
   <xsd:restriction base="xsd:integer"></xsd:restriction></xsd:simpleType>
</xsd:element>
<xsd:element name="FirstName">
  <xsd:simpleType>
   <xsd:restriction base="xsd:string"><xsd:maxLength value="35" /></xsd:restriction>
  </xsd:simpleType>
</xsd:element>
<xsd:element name="LastName">
  <xsd:simpleType>
   <xsd:restriction base="xsd:string"><xsd:maxLength value="35" /></xsd:restriction>
  </xsd:simpleType>
</xsd:element>
<xsd:element name="Person">
 <xsd:complexType>
   <xsd:sequence>
     <xsd:element ref="Id" />
     <xsd:element ref="FirstName" />
     <xsd:element ref="LastName" />
   </xsd:sequence>
 </xsd:complexType>
</xsd:element>
<!-- MAIN ELEMENT -->
<xsd:element name="People">
 <xsd:complexType>
   <xsd:sequence>
     <xsd:element ref="Person" minOccurs="0" maxOccurs="unbounded"/>
   </xsd:sequence>
 </xsd:complexType>
</xsd:element>
</xsd:schema>

To register it in the Oracle DB execute the following script:

begin
  dbms_xmlschema.registerSchema(
  'SchemaName.xsd',
  'insert schema here...',
  genTypes => true,
  genTables => false,
  local => true
  );
end;
/

The script registers the schema and also creates types based on the elements in the xsd. If we register xsd schemas this way, the genereted types are not good to use in programs (plsql procedure, function,...) because the names of types are generated internally (if we delete the schema and register the same one again the names are different!!!).

We can see all the registered schemas in oracle:
select * from user_xml_schemas

To delete any registered schema, execute this:

begin
 dbms_xmlschema.deleteSchema('People.xsd',dbms_xmlschema.DELETE_CASCADE);
end;
/

The proper way the xsd schema should look, will be discribed in the next post :)