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;