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;
/