Oracle XMLType: exctractvalue vs. extract

I always thought that

exctarctvalue(xml, '/Node')

is equivalent to

xml.extract('/Node/text()').getstringval()

Usually it is true, but in some cases it is not. When node contains special characters like &, >, < etc... then results are different. Let me give an example:

SQL> set define off
SQL> select aa.a.extract('Node/text()').getStringVal() extr
  2       , extractvalue(aa.a, 'Node') extractval
  3    from (select XMLRoot(
  4              XMLElement("Node", 'test & < and >'),
  5             VERSION '1.0') a from dual) aa;

EXTR
----------------------------------------
EXTRACTVAL
----------------------------------------
test &amp; &lt; and &gt;
test & < and >

First look on documentation even confirms my expectations:
"extractValue – This takes an XPath expression and returns the corresponding leaf node. The XPath expression passed to extractValue should identify a single attribute or an element that has precisely one text node child. The result is returned in the appropriate SQL data type. Function extractValue is essentially a shortcut for extract plus either getStringVal() or getNumberVal()."
XML DB Developer's Guide - b14259.pdf page 1-17.

But there is small note on that:
"Note: Function extractValue and XMLType method getStringVal() differ in their treatment of entity encoding. Function extractValue unescapes any encoded entities; method getStringVal() returns the data with entity encoding intact."
XML DB Developer's Guide - b14259.pdf page 4-10.

Cheers, Paweł

RMAN connection to Catalog - updated

I must say that I was completely wrong in my post RMAN connection to Catalog. In fact when connection to Catalog fails, RMAN quits and rest of script is ignored. So I've changed this. RMAN connect only to Target Database during backup process, and after that RMAN is run second time. This time it connects to Target Database and Catalog Database, then run command RESYNC CATALOG to force synchronization of control file with Catalog. It should work now.

Cheers, Paweł

ORA-03297 on empty datafile

Today I've noticed strange behavior of Oracle 10gR2 (10.2.0.3) while shrinking datafile. I had empty datafile (no objects) and i couldn't resize it. I was getting ORA-03297: file contains used data beyond requested RESIZE value while DBA_EXTENTS show no rows for that file. I've discovered that there are objects in Recycle Bin that were located on that file. After purging recycle bin file was shirked successfully.
Let me make an example:

SQL> create tablespace tb_test
  2  datafile 'c:\temp\tb_test.dbf' size 100 K autoextend on;
Tablespace created.
SQL> select count(*) from dba_extents where tablespace_name='TB_TEST';
  COUNT(*)                                                                      
----------                                                                      
         0                                                                      
SQL> select bytes from v$datafile where name = 'C:\TEMP\TB_TEST.DBF';
     BYTES                                                                      
----------                                                                      
    106496                                                                      
SQL> create table t_big tablespace tb_test as
  2  select a.* from all_objects a, dba_users for_cartesian;
Table created.
SQL> select count(*) from dba_extents where tablespace_name='TB_TEST';
  COUNT(*)                                                                      
----------                                                                      
        34                                                                      
SQL> select bytes from v$datafile where name = 'C:\TEMP\TB_TEST.DBF';
     BYTES                                                                      
----------                                                                      
  19963904

Now we have datafile quite big. Let's drop table and shrink file:

SQL> drop table t_big;
Table dropped.
SQL> select count(*) from dba_extents where tablespace_name='TB_TEST';
  COUNT(*)                                                                      
----------                                                                      
         0                                                                      
SQL> alter database datafile 'c:\temp\tb_test.dbf' resize 100 K;
alter database datafile 'c:\temp\tb_test.dbf' resize 100 K
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value 

So. even there is nothing reported in DBA_EXTENT, file cannot be shinked. Let's purge recyclebin and try again:

SQL> purge recyclebin;
Recyclebin purged.
SQL> alter database datafile 'c:\temp\tb_test.dbf' resize 100 K;
Database altered.

Now works fine! Don't forget to clear after tests :)

SQL> drop tablespace tb_test including contents and datafiles;
Tablespace dropped.

Cheers, Paweł

Security: getting DBA rights quite easy

I've stepped today at ORASEC blog by Paul Wright. I've found this issue a big problem. Anybody that is using Oracle client on windows can quite easy get DBA rights by editing client9.dll file. I've to check if this is still problem with latest Oracle 10.2.0.3 release.

Cheers, Paweł

Applying Oracle 10.2.0.3 patch

Today I've installed patch 10.2.0.3 for Oracle on Windows on top of 10.2.0.2 database. I've noticed that my spool file generated by upgrade script was 33 MB big. It occurred that source of all oracle build-in packages were spooled. It was terrible experience to search for errors in this log file, as searching for 'ORA-' give hundreds results and I had to figure out if it is in comment of package, or real error.
Fortunately patch was installed successfully and everything runs fine.

Cheers, Paweł

UTL_MATCH - String Similarity in Oracle

You can compare string using equality operator (=), or using similarity operator (LIKE). But there are cases where it is not enough. You Can use UTL_MATCH package to calculate string similarity index. This packages offers 4 functions that take two strings as parameters:

• edit_distance - algorithm by Levenshtein - returns number of edits that must be done to change one string into second,
• edit_distance_similarity - normalized results of edit_distance in percents - integer values,
• jaro_winkler - returns similarity based on Jaro-Winkler distance algorithm,
• jaro_winkler_similarity - same as above but presented as integer in range 0-100.

Lets teke a look how it works. First we can create sample table and insert some data:

create table countries(name varchar2(15) not null);
insert into countries values ('Poland');
insert into countries values ('Germany');
insert into countries values ('United States');
insert into countries values ('Portugal');
insert into countries values ('Czech Republic');
insert into countries values ('China');
insert into countries values ('Slovakia');
insert into countries values ('Slovenia');
commit;

Now we can take a look at results. Lets compare to miss-spelled country name: 'Slovnia'

select name
 ,to_char(utl_match.edit_distance(name, 'Slovnia'),'999') edit_dist
 ,to_char(utl_match.edit_distance_similarity(name, 'Slovnia'),'999') edit_dist_sim
 ,to_char(utl_match.jaro_winkler(name, 'Slovnia'),'999d9999') jaro_winkler
 ,to_char(utl_match.jaro_winkler_similarity(name, 'Slovnia'),'999') jaro_winkler_sim
from countries
order by jaro_winkler_sim desc;

NAME                 EDIT EDIT JARO_WINK JARO
-------------------- ---- ---- --------- ----
Slovenia                1   88     .9750   97
Slovakia                2   75     .8881   88
China                   5   29     .5619   56
United States          12    8     .5531   55
Poland                  6   15     .5317   53
Portugal                7   13     .5119   51
Germany                 7    0     .3571   35
Czech Republic         13    8     .0000    0

Above we can observe differences in algorithms.
Lets test it on NULLs:

SQL> select to_char(utl_match.edit_distance('test', NULL),'999')
  2      edit_dist
  3   ,to_char(utl_match.edit_distance_similarity('test', NULL),'999')
  4      edit_dist_sim
  5   ,to_char(utl_match.jaro_winkler('test', NULL),'999d9999')
  6      jaro_winkler
  7   ,to_char(utl_match.jaro_winkler_similarity('test', NULL),'999')
  8      jaro_winkler_sim
  9  from dual;

EDIT EDIT JARO_WINK JARO
---- ---- --------- ----
  -1  125     .0000    0

We can see that using edit_distance on NULLs migth be dengerous.
All samples were run on Oracle 10g XE

Hope You find it usefull.
Paweł

MERGE and SEQUENCE

Last days I was working on code and I was using merges often. And i've notice some side-effect. When you use sequence in WHEN NOT FOUND clause then next sequence value is gathered for all rows processed in query. I've observed it on 10g R2 EE (10.2.0.2) and on Oracle XE. Let me show test case. First create sequence:

SQL> create sequence a_sq start with 1 increment by 1 nocache;
Sequence created.

This sequence generator starts with 1 with step 1. Lets taka look how many rows we have in USER_OBJECTS view:

SQL> select count(*) from user_objects;
 COUNT(*)
----------
 46

Lets create sample table and populate it with 10 rows:

SQL> create table a_tab as
 2 select object_id o_id, object_name o_name, a_sq.nextval o_sq
 3 from user_objects where rownum <=10;
Table created.

So we have table with 10 rows, and last number generated by sequence is also 10:

SQL> select count(*), max(o_sq) from a_tab;
 COUNT(*) MAX(O_SQ)
---------- ----------
 10 10

Now we run merge:

SQL> merge into a_tab
 2 using (select object_id , object_name
 3 from user_objects )
 4 on (object_id = o_id)
 5 when matched then
 6 update set o_name = object_name
 7 when not matched then
 8 insert (o_id, o_name, o_sq)
 9 values (object_id, object_name, a_sq.nextval);
47 rows merged.

47 rows were merged, but 10 rows was updated and 37 inserted. Lets check it:

SQL> select count(*), max(o_sq) from a_tab;
 COUNT(*) MAX(O_SQ)
---------- ----------
 47 57

It seems that sequence generated 47 new values, but only 37 rws were inserted. Lets run the same merge one more time. This time rows will be only updated, as all rows are already in a_tab table.

SQL> merge into a_tab
 2 using (select object_id , object_name
 3 from user_objects )
 4 on (object_id = o_id)
 5 when matched then
 6 update set o_name = object_name
 7 when not matched then
 8 insert (o_id, o_name, o_sq)
 9 values (object_id, object_name, a_sq.nextval);
47 rows merged.

Now no new rows where created. But what happen to sequence:

SQL> select a_sq.nextval from dual;
 NEXTVAL
----------
 105

It generated another 47  values, that are useless. It can be proved:

SQL> select count(*), max(o_sq) from a_tab;
 COUNT(*) MAX(O_SQ)
---------- ----------
 47 57

This side effect might have impact on performance. Also you loose lot of numbers, when most of rows are updated. Cheers, Paweł