Caution: FOR loop and CONTINUE in Oracle 11g

Written by Paweł Barut
Friend of mine showed me interesting issue of using CONTINUE Statement. CONTINUE is an new statement in Oracle 11; it allow to skip processing of current iteration of look, and go to begging of next iteration. So here is working sample:

SQL> set serveroutput on
SQL> begin
  2    dbms_output.put_line('=== START');
  3    for i in 1..15 loop
  4      dbms_output.put_line('before:'||i);
  5      if mod(i, 5) = 0 then
  6        dbms_output.put_line('CONTINUE');
  7        continue;
  8      end if;
  9      dbms_output.put_line('after:'||i);
 10    end loop;
 11    dbms_output.put_line('=== STOP');
 12  end;
 13  /
=== START
before:1
after:1
before:2
after:2
before:3
after:3
before:4
after:4
before:5
CONTINUE
before:6
after:6
before:7
after:7
before:8
after:8
before:9
after:9
before:10
CONTINUE
before:11
after:11
before:12
after:12
before:13
after:13
before:14
after:14
before:15
CONTINUE
=== STOP

But when we use CONTINUE in loop, that is based on implicit cursor, it gives wrong results:

SQL> begin
  2    dbms_output.put_line('=== START');
  3    for r in (select level num from dual connect by level <= 115) loop
  4      dbms_output.put_line('before:'||r.num);
  5      if mod(r.num, 5) = 0 then
  6        dbms_output.put_line('CONTINUE');
  7        continue;
  8      end if;
  9      dbms_output.put_line('after:'||r.num);
 10    end loop;
 11    dbms_output.put_line('=== STOP');
 12  end;
 13  /
=== START
before:1
after:1
before:2
after:2
before:3
after:3
before:4
after:4
before:5
CONTINUE
before:101
after:101
before:102
after:102
before:103
after:103
before:104
after:104
before:105
CONTINUE
=== STOP

In that case CONTINUE statement goes to iteration no 101 instead of going to iteration 6, as expected!!
To make the long story short, I've experiment with few setting and found that setting PLSQL_OPTIMIZE_LEVEL to 1 this block runs as expected:

SQL> ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 1;
SQL> begin
  2    dbms_output.put_line('=== START');
  3     for r in (select level num from dual connect by level <= 115) loop
  4       dbms_output.put_line('before:'||r.num);
  5       if mod(r.num, 5) = 0 then
  6         dbms_output.put_line('CONTINUE');
  7         continue;
  8       end if;
  9       dbms_output.put_line('after:'||r.num);
 10     end loop;
 11     dbms_output.put_line('=== STOP');
 12  end;
 13  /
=== START
before:1
after:1
before:2
after:2
before:3
after:3
before:4
after:4
before:5
CONTINUE
before:6
after:6
[... many lines cutted out from output...]
after:99
before:100
CONTINUE
before:101
after:101
before:102
after:102
before:103
after:103
before:104
after:104
before:105
CONTINUE
before:106
[... many lines cutted out from output...]
after:114
before:115
CONTINUE
=== STOP

For me it looks as bug in PL/SQL Optimizer on level 2. In that case PL/SQL does bulk collect for 100 rows when FOR LOOP with cursor is used. Seems that CONTINUE in that case causes to fetch next portion of records instead of taking next record from buffer. I've reported bug to Oracle Support, but until it will get fixed, be aware of possible wrong results.
This was tested on Oracle 11.1.0.6 and 11.1.0.7 Enterprise Edition.

Keep reading,
Paweł

--
Related Articles on Paweł Barut blog:

• Comments in SQL*Plus
• Dynamically set name of spool file in SQL*Plus
• Table that cannot be deleted

• ORA-03297 on empty datafile
• Flashback Table and Materialized View – not working together

Horrible practice of storing users passwords

Written by Paweł Barut
I've read today terrifying article about un-ethical storage of users password. It is often common practice that users passwords are stored unencrypted in database. But this case is even more dreadful. It about software, G-Archiver, that is available to everyone. If you ever used this software, you should change your G-mail password right now.
So what is the problem:

• G-Archiver has build in user-name and password for one of g-mail accounts.
• Whenever someone uses G-Archiver and provides his credentials for g-mail, user-name and password is send to author of this software
• Additionally, any one who finds out the user-name and password stored in G-Archiver, can get passwords of thousand of previous users.

Go and read. Hope there are not too many of software that works like this one. It is really terrifying, that some people are so irresponsible. It could be well planed action to collect gmail users and passwords, or it could be just lack of imagination what are the consequences. I would like to believe that it was the second one.

But also big corporations do not care for security enough. It is common practice, that users password is stored internally as plain text. It can be easily found out by using "I forgot my password" feature. If in return you will get email with your original password, then it is a crappy web side. And you should never reuse password passed to such web side as this is big risk for you. Take a look at Password Security: It’s Not That Hard (But You Still Can’t Get It Right) for further explanation and examples.

Cheers Paweł

--
Related Articles on Paweł Barut blog:

• Security: getting DBA rights quite easy
• „Do not use Linux, please, pirate our software” - Microsoft
• Spam, Spam filters, Being Spammer, Being Filtered-out ...
• Do not trust Google Maps

Using Named Parameters within SQL

One of the Oracle 11g new feature that I like is ability to use Named Parameter syntax in functions calls inside SQL. For example it is possible to run such query:

select DBMS_METADATA.get_xml(
          object_type     => object_type,
          name            => object_name) 
     from user_objects
where object_type in ('TABLE', 'VIEW');

It was not possible in previous Oracle versions (before 11g). It was only possible to specify parameters by position:

select DBMS_METADATA.get_xml(object_type, object_name) from user_objects
where object_type in ('TABLE', 'VIEW');

This is great feature as now SQL is more compatible with PL/SQL. And Named Parameters syntax is my favorite method of calling functions, as it gives me direct knowledge what value is assigned to parameters. Specifying parameters by position requires to remember sequence of parameters in function definition. Additionally named parameters syntax allows programmer to skip any of parameter that has default value, or to change order of parameters in function call. It is also possible to mix those 2 notations in single statement:

select DBMS_METADATA.get_xml(object_type, name => object_name) from user_objects
where object_type in ('TABLE', 'VIEW');

Hope you like it

Cheers, Paweł

Oracle 11g DRCP: Functionality Test

This post is continuation of my series of post about Database Resident Connection Pooling.
Let start with session and processes. My test shows that Pooled Servers are shown in v$session view only when there is user connected, that means session is busy. For inactive servers only process in v$process view is reported. To identify this processes we can run query like that:

select * from v$process
where program like '%(L0%)';

If our Connection pooling is started, at least one process will be reported. This is so called Connection Broker process that is authenticating connections and assigning them to another server for processing. According to my tests Connection Broker this process usually have highest number in brackets (ex. L003 if you set up max number of processes to 4). To see busy servers run this query:

select * from v$session
where program like '%(L0%)';

Note that Connection Broker is never reported in v$session view.

In next test I've tested if global variables in packages are stored between connections. My test confirms that packages are always initialized after new connection is taken from pool. That's very good, and is what I was expecting. This is advantage over traditional connection pooling at client side like in Java, where environment is totally shared between consecutive use of the same connection. (This is true when only process if reused, when PURITY=NEW)

My next test was concerning usage of context. I've created sample CONTEXT, initialized it in session and reconnected. I've ensured that session was assigned to the same process (pooled session) and read context. My test also confirmed that context is reinitialized between connections. In traditional connection pooling it will not happen. Also when using PHP and persistent connection you have to take care of clearing global variables and context after acquiring session from pool (or before giving it back to pool). Database Resident Connection Pooling will make programmers life easier, at least on that field. (This is true when only process if reused, when PURITY=NEW)

My last test for today was performance test. Test was run on rather poor desktop machine with single processor.

Testcase	Normal Connection	Pooled Connection	%Improvement
Connect and Disconnect 1000 times	226 sec	154 sec	31%
Connect, Insert one row, Disconnect 1000 times	309 sec	178 sec	42%

This results are very promising. First we see 30% improvement of time to connect to DB. What is more important we can see 40% less time on repeating the same simple statement after establishing each connection. The only explanation is that Pooled connection can reuse already parsed statement. That is good news if we think about scalability.

You may be also interested in Oracle Official White-paper on DRCP.

At the end I would like to thank Christopher Jones as he was involved in DRCP development. I've mailed with him few times last year about problems with PHP-Oracle connectivity and lack of connection pooling.

Cheers, Paweł

Operating on XMLType containing & (Ampersands)

I was reading today Lewis post on How to Deal With Oracle XML and Ampersands ("&") in XML Documents. Personally I prefer different way. I use XMLElement function to generate XML documents. In my opinion this is more elegant way. The sample from Lewis post will look like that:

set define off
declare
  v_xml xmltype;
begin
  select xmlelement("ROWSET", 
           xmlelement("ROW", 
             xmlelement("COLA", 'lewis&me')))
    into v_xml 
    from dual;
end;
/

Disadvantage of this method is that you have to use SQL. But in most cases it's used while retrieving data from database, so it should not be a problem. Some time ago I was proposing to read document Mastering XML Generation in Oracle Database 10g Release 2. It is really good source of knowledge on XML manipulation inside Oracle database.
If you want to extract correctly ampersands and other special characters like < and > from XML document I suggest to read my post on differences between extract and extractvalue functions. If you want to manually convert &amp; to & you can use function dbms_xmlgen.convert with second parameter set to 1:

SQL> select dbms_xmlgen.convert('me&lewis', 1) from dual;

me&lewis

Cheers, Paweł

Alternative Syntax for Insert

Alternative Syntax for Insert

Let me show my idea of SQL improvement, that I would like to see. Traditional insert works like that:

insert into table_a
    (column_1, column_2, column_3, column_4)
  values
    (value_1, value_2, value_3, value_4);

What I would like to have is something like that:

insert into table_a
   set column_1 = value_1
      ,column_2 = value_2
      ,column_3 = value_3
      ,column_4 = value_4;

With this syntax code would be easier to maintain. Especially, when you have to add or delete, or find error in insert to table with many columns. Biggest advantage of this would be close relationship between column name and value that will be set for that column. In INSERT that we know it column name and value are quite far from each other, so even for table with 10 columns it might be hard to find corresponding entries.
In PL/SQL there is an workaround to get similar solution:

declare
    v_row table_a%rowtype;
begin
    v_row.column_1 := value_1;
    v_row.column_2 := value_2;
    v_row.column_3 := value_3;
    v_row.column_4 := value_4;
    insert into table_a values v_row;
end;

But it's PL/SQL not pure SQL. I really would like to have this syntax in next Oracle release...

Cheers, Paweł

Rows to single string or aggregating strings

In oracle there is no build-in group function that will concatenate strings, just like max summarizes all numbers. But there is quite easy way to do that in pure SQL. Assume we want to have get as an query result list of tables and column. But we want all columns to be concatenated and comma separated. Result should look

TABLE_NAME	COLUMN_LIST
BLOG_LABELS	BLL_BLG_ID, BLL_LABEL
BLOG_COMMENTS	BLC_BLG_ID, BLC_IDENTIFIER, BLC_PUBLISHED_TEXT, BLC_PUBLISHED, BLC_UPDATED_TEXT, BLC_UPDATED, BLC_CONTENT, BLC_URL, BLC_AUTHOR, BLC_AUTHOR_URI
BLOG_POSTS	BLG_ID, BLG_IDENTIFIER, BLG_TITLE, BLG_PUBLISHED_TEXT, BLG_PUBLISHED, BLG_UPDATED_TEXT, BLG_UPDATED, BLG_CONTENT, BLG_URL, BLG_BGH_ID, BLG_ENTRY
BLOGS	BGH_ID, BGH_NAME, BGH_FEED_URL
FEED_CACHE	CCH_URL, CCH_TIME, CCH_VALUE

Lets start with hierarchical query to build "tree" for each table. The only one branch of this tree will start with first column and end with last one. SYS_CONNECT_BY_PATH gives as option to track that branch:

SQL> select table_name, 
  2        SYS_CONNECT_BY_PATH(column_name,  ',') column_list
  3  from user_tab_columns
  4  start with column_id = 1
  5  connect by table_name = prior table_name
  6             and column_id = prior column_id +1
  7  ;

TABLE_NAME	COLUMN_LIST
BLOGS	, BGH_ID
BLOGS	, BGH_ID, BGH_NAME
BLOGS	, BGH_ID, BGH_NAME, BGH_FEED_URL
...	...

Then I just take max path and truncating leading comma gives me expected result:

SQL> select table_name, 
  2        ltrim(max(SYS_CONNECT_BY_PATH(column_name,  ',')), ',') column_list
  3  from user_tab_columns
  4  start with column_id = 1
  5  connect by table_name = prior table_name
  6             and column_id = prior column_id +1
  7  group by table_name;

TABLE_NAME	COLUMN_LIST
BLOG_LABELS	BLL_BLG_ID, BLL_LABEL
BLOG_COMMENTS	BLC_BLG_ID, BLC_IDENTIFIER, BLC_PUBLISHED_TEXT, BLC_PUBLISHED, BLC_UPDATED_TEXT, BLC_UPDATED, BLC_CONTENT, BLC_URL, BLC_AUTHOR, BLC_AUTHOR_URI
BLOG_POSTS	BLG_ID, BLG_IDENTIFIER, BLG_TITLE, BLG_PUBLISHED_TEXT, BLG_PUBLISHED, BLG_UPDATED_TEXT, BLG_UPDATED, BLG_CONTENT, BLG_URL, BLG_BGH_ID, BLG_ENTRY
BLOGS	BGH_ID, BGH_NAME, BGH_FEED_URL
FEED_CACHE	CCH_URL, CCH_TIME, CCH_VALUE

Function Max works becouse if one string (ex.: "abc") is an prefix of the second one (ex: "abc123") than this second one is threated as bigger. I must warn that it might be not true for all NLS settings.

But what if we want columns to be ordered alphabetically? Than we have to use Row_Number function to calculate column position in table and make same changes to SQL:

SQL> select table_name, 
  2        ltrim(max(SYS_CONNECT_BY_PATH(column_name,  ',')), ',') column_list
  3  from (select table_name,  column_name,  ROW_NUMBER() OVER (partition by table_name ORDER BY column_name) AS curr  from user_tab_columns)
  4  start with curr = 1
  5  connect by table_name = prior table_name
  6             and curr = prior curr +1
  7  group by table_name;

TABLE_NAME	COLUMN_LIST
BLOG_LABELS	BLL_BLG_ID, BLL_LABEL
BLOG_COMMENTS	BLC_AUTHOR, BLC_AUTHOR_URI, BLC_BLG_ID, BLC_CONTENT, BLC_IDENTIFIER, BLC_PUBLISHED, BLC_PUBLISHED_TEXT, BLC_UPDATED, BLC_UPDATED_TEXT, BLC_URL
BLOG_POSTS	BLG_BGH_ID, BLG_CONTENT, BLG_ENTRY, BLG_ID, BLG_IDENTIFIER, BLG_PUBLISHED, BLG_PUBLISHED_TEXT, BLG_TITLE, BLG_UPDATED, BLG_UPDATED_TEXT, BLG_URL
BLOGS	BGH_FEED_URL, BGH_ID, BGH_NAME
FEED_CACHE	CCH_TIME, CCH_URL, CCH_VALUE

Hope you will find it useful and fun.
All samples where run on Oracle 10gR2 XE 10.2.0.1.
Schema used in this sample is described in Blogger Backup tool entry.

Paweł

Solving ORA-22905: cannot access rows from a non-nested table item

Some time ago i wrote about Binding list variable. In fact it is conversion of string to rows. My example works fine in SQL but when used in PL/SQL it might cause error ORA-22905: cannot access rows from a non-nested table item. I do know what are the necessary conditions for this error, as it does not always happen in PL/SQL. I faced it usually for quite complicated queries. To overcome this problem I just cast binding variable to varchar2 as shown below:

  procedure test_proc(p_list in varchar2)
  is
  begin
    for r in (select items.extract('/l/text()').getStringVal() item
              from table(xmlSequence(
                extract(XMLType(''||
                  replace(cast (p_list as varchar2(4000)),
                          ',','')||'')
                      ,'/all/l'))) items) loop
      null;
    end loop;
  end;

It worked for me in all situations where I faced ORA-22905 on Oracle 10gR2 (10.2.0.3)

Paweł

Oracle: Cursors, Bind Variables and Performance

I've found today very good document on Efficient use of bind variables, cursor_sharing and related cursor parameters dated 2002. Document is related to Oracle 9i, but also applies to Oracle 10g.

Paweł

Monitor progress of long running processes

This time I want to share how I use v$session_longops to monitor Oracle long running queries but also how to create your own entries in this view. When monitoring long lasting operation I'm interested what is running now, and what was finished lately. For this purposes I use query:

SELECT sid,
  serial#,
  opname,
  target_desc,
  percent,
  sofar,
  totalwork,
  to_char(start_time,   'hh24:mi:ss') start_time,
  to_char(efin,   'hh24:mi:ss') estimate_fin,
  case when sofar <> totalwork and last_update_time < sysdate-1/10000 then '*' else null end broken
FROM
  (SELECT sid,
     serial#,
     opname,
     target_desc,
     sofar,
     totalwork,
     to_char(CASE
             WHEN totalwork = 0 THEN 1
             ELSE sofar / totalwork
             END *100,    '990') percent,
     start_time,
     last_update_time,
     start_time +((elapsed_seconds + time_remaining) / 86400) efin
   FROM v$session_longops
   ORDER BY  CASE
             WHEN sofar = totalwork 
                THEN 1
                ELSE 0 END,
          efin DESC)
WHERE sofar <> totalwork or rownum <= 20;

It lists all currently running operations and up to twenty lately finished. The most important columns are estimate_finish – it predicts time of process end, and broken – if contains star (*) it is very possible that process was terminated or hung. Constant 1/10000 (about 8 seconds) in comparison

efin < sysdate-1/10000

is to avoid false alerts. If single step of yours process takes more then 8 seconds than you should alter this value to meet your needs. You can find more information on v$session_longops view in Oracle documentation.
Sample output:

Lets take a look how to write your own entries to long operations view. As a samle I'll just use simple loop that for each record in ALL_OBJECTS process will sleep for 0.1 second:

declare
  v_rindex pls_integer;
  v_slno   pls_integer;
begin
  v_rindex := dbms_application_info.set_session_longops_nohint;
  for r_qry in (select t.*, rownum rn, count(*) over () cnt 
                  from ALL_OBJECTS t ) loop
    dbms_application_info.set_session_longops
      ( rindex =>  v_rindex,
        slno   =>  v_slno,
        op_name => 'ALL_OBJECTS processing',
        target  =>  0,
        target_desc => 'ALL_OBJECTS',
        context     => 0,
        sofar       => r_qry.rn,
        totalwork   => r_qry.cnt,
        units       => 'loops'
      );
    dbms_lock.sleep(0.1);
  end loop;
end;

So what is important here:

• rindex – for first call it must be ste to dbms_application_info.set_session_longops_nohint – it means add new row to v$session_longops view
• rownum is used to get row number; it is passed to sofar parameter
• analytical function (count(*) over () cnt) is used to calculate all rows (steps) in that process; it is passed to totalwork parameter
• rindex, slno – should be assigned always to the same variable; it is needed to pass information about row that should be changed

Additionaly you can add when others handler to set progress to 100%, but you do have to do that. Broken flag described above should work fine, and you know if process ended normally or with errors. Full specification of dbms_application_info.set_session_longops

Paweł

Yet another tokenizer in Oracle

I was in need to have function that transforms string into table of words. Also words can be separated by many different chars. For this purpose i've created function:

create or replace function tokenizer
  (p_string in varchar2
  ,p_separators in varchar2
  ) return dbms_sql.varchar2s
is
  v_strs dbms_sql.varchar2s;
begin
  with sel_string as 
      (select p_string fullstring 
         from dual)
  select substr(fullstring, beg+1, end_p-beg-1) token
   bulk collect into v_strs
   from (select beg, lead(beg) over (order by beg) end_p, fullstring
           from (select beg, fullstring
                   from (select level beg, fullstring
                           from sel_string
                         connect by level <= length(fullstring))
                   where instr(p_separators ,substr(fullstring,beg,1)) >0
                 union all
                 select 0, fullstring from sel_string
                 union all
                 select length(fullstring)+1, fullstring from sel_string))
    where end_p is not null
      and end_p>beg+1;
  return v_strs;
end;

Usage is very simple. First parameter is string that should be tokenized, second parameter is string with characters that should be treated as word separators. Example:

SQL> set serveroutput on
SQL> declare v_strs dbms_sql.varchar2s;
  2  begin
  3    v_strs := tokenizer('I was in need to have function that transforms string into table of words.'
  4        , ' ,:;.<>[]{}()');
  5    for i in v_strs.first..v_strs.last loop
  6      dbms_output.put_line(v_strs(i));
  7    end loop;
  8  end;
  9  /
I                                                                               
was                                                                             
in                                                                              
need                                                                            
to                                                                              
have                                                                            
function                                                                        
that                                                                            
transforms                                                                      
string                                                                          
into                                                                            
table                                                                           
of                                                                              
words                                                                           

PL/SQL procedure successfully completed.

Let me explain some elements

  with sel_string as 
      (select p_string fullstring 
         from dual)

is just to bind string parameter into SQL just once. Otherwise it will have to be binded in many places.

                 select beg, fullstring
                   from (select level beg, fullstring
                           from sel_string
                         connect by level <= length(fullstring))
                   where instr(p_separators ,substr(fullstring,beg,1)) >0

Internal select generates numbers from 1 to length of string. Outer select chooses only these rows (numbers) that are separators in string.

                 union all
                 select 0, fullstring from sel_string
                 union all
                 select length(fullstring)+1, fullstring from sel_string))

adds separator at beginning and end of string, so first and last work can be recognized.

 select beg, lead(beg) over (order by beg) end_p

gives as positions of two consecutive separators, and finally

  select substr(fullstring, beg+1, end_p-beg-1) token
   bulk collect into v_strs
   ...
    where end_p is not null
      and end_p>beg+1;

select words.

Might be you find it useful,
Paweł

Interesting posts 2007-02-06

Here are some interesting posts I've read recently:

• User Interface Design: Complexity vs. Flexibility - Rob Walling is traing to find aswer to question: can a design be simple and flexible at the same time?
• How to scale... Tom Kyte post about scalability of middleware and database. Do not forget to read Tim Hall response Middle-tier diet….
• Difference between Jeff Bezos and Bill Gates? by Kathy Sierra - very interesting story about top CEO's attitude to users.

Cheers, Paweł

Restore CONTROLFILE and/or SPFILE from autobackup

As I mentioned earlier in post about setting up autobackup, its time to describe how to restore CONTROLFILE and SPFILE from autobackups. If your database is still running and what You want is just get historical version of SPFILE or CONTROLFILE then it is easy task. Just start RMAN:

RMAN TARGET=/

And enter command to restore CONTROLFILE:

RMAN> restore until time 'sysdate-3'
2> CONTROLFILE to 'c:\temp\cfile' from autobackup;
Starting restore at 01-FEB-07
using channel ORA_DISK_1
recovery area destination: C:\oracle\product\10.2.0/flash_recovery_area
database name (or database unique name) used for search: BAR
channel ORA_DISK_1: no autobackups found in the recovery area
channel ORA_DISK_1: looking for autobackup on day: 20070129
channel ORA_DISK_1: autobackup found: C:\Backup\c-669001291-20070129-03
channel ORA_DISK_1: control file restore from autobackup complete
Finished restore at 01-FEB-07

The until time clause sets time of validity for CONTROLFILE. By default RAM looks for autobackups for seven days into past starting from that time. It can happen that for several days there were no changes, and autobackup was not invoked. In that case add MAXDAYS clause:

RMAN> restore until time 'sysdate-3'
2> CONTROLFILE to 'c:\temp\cfile' from autobackup maxdays 100;

It will force RMAN to look for copies for more days into past. Similarly You can restore SPFILE:

RMAN> restore until time 'sysdate-3'
2> SPFILE to 'c:\temp\SPFILE' from autobackup maxdays 100;
Starting restore at 01-FEB-07
using channel ORA_DISK_1
recovery area destination: C:\oracle\product\10.2.0/flash_recovery_area
database name (or database unique name) used for search: BAR
channel ORA_DISK_1: no autobackups found in the recovery area
channel ORA_DISK_1: looking for autobackup on day: 20070129
channel ORA_DISK_1: autobackup found: C:\Backup\c-669001291-20070129-03
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 01-FEB-07

After restoring SPFILE You can convert it to PFILE, so You can easily read settings:

RMAN> sql "create PFILE = ''c:\temp\PFILE'' from SPFILE = ''c:\temp\SPFILE''";
sql statement: create PFILE = ''c:\temp\PFILE'' from SPFILE = ''c:\temp\SPFILE''

Now more complex scenario: You have lost all database files including CONTROLFILE and SPFILE. You only have backups created by RMAN. In that case You have to eventually install Database Software. Then setup environment variables and start recovery. On Windows platform You have to create Service to be able to startup oracle. You probably remember instance name and SID. If You don't it can be usually found in some TNSNAMES files on client machines. But You probably do not know DBID. You can easily find DBID – it'is part of the name of autobackup file. Details in my previous post. So if autobackup file name is 'c-669001291-20070129-03' then DBID is 669001291. Now we can start recovery:

RMAN target=/

At the beginning set Database ID:

RMAN> SET DBID 669001291;

Then startup instance:

RMAN> startup force nomount;

and run recovery of SPFILE

RMAN> RUN {
2> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'C:\Backup\%F'; 
3> RESTORE SPFILE FROM AUTOBACKUP; }

I suggest also creating PFILE from SPFILE. Then You should check if all file locations are correct, all directories exists and Oracle have rights to write in them.

RMAN> shutdown;
RMAN> startup force nomount;
RMAN> RUN { 
2> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'C:\Backup\%F'; 
3> RESTORE CONTROLFILE FROM AUTOBACKUP; }

Now You can continue recovery of datafiles.

Paweł

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ł

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ł

Blogger Beta Backup tool

Finally i found time to prepare solution to backup my blog. I've decided to do that as I had problems to backup my blog using available tools. To archive that I use Blogger Beta Atom feeds, few tables in Oracle, and PL/SQL package to read blog posts, parse them using XML DB features and store in DB tables.

Requirements

• Oracle 10g (tested on Oracle XE on Windows and Oracle Enterprise 10.2.0.2 on Windows). Oracle9i should work also, but it's not tested.
• Blogs maintained by Beta Blogger and hosted on blogspot.com. You must allow to publish full content of your posts and comments.
• Direct connection to Internet

Installation

1. Download installation package
2. Create new Oracle schema and grant to it:
   1. roles CONNECT and RESOURCE
   2. EXECUTE on SYS.UTL_HTTP
3. run script install.sql

Usage

First You have to register blog you want to backup. Use BLOGSPOT_BACKUP.register_blog function:

begin
 dbms_output.put_line(
 BLOGSPOT_BACKUP.register_blog
 ('Software Engineer Thoughts', 'http://pbarut.blogspot.com/'));
 commit;
end;

To download all your posts with comments andl labels use this PL/SQL block:

begin 
 BLOGSPOT_BACKUP.synchronize_blog(null, false); 
 BLOGSPOT_BACKUP.synchronize_labels(null, null); 
 BLOGSPOT_BACKUP.synchronize_comments(null, null, false); 
 commit; 
end;

For more information take a look into BLOGSPOT_BACKUP specification.

Data storage

Table name	Desription
BLOGS	Blogs registered for backup.
BLOG_POSTS	Blog posts downloaded.
BLOG_LABELS	Labels associated to blog posts.
BLOG_COMMENTS	Comments related to blog posts.
FEED_CACHE	Used speed up tests. In tests phase feeds are read from this table instead of downloading every time.

End notes

I'm publishing this tool, as some of you might find it useful. It should work on all beta blogger blogs, but I do not give ant warranty. In case of any problems fill free to contact me.

Regards, Paweł

ORA-30625 and XMLType

I will present some differences in handling XMLType in SQL and PL/SQL by Oracle. Test case was done on Oracle 10.2.0.2. Lets define variable and fill it with sample data. It is important to have empty value in tag <empty>.

SQL> var xm varchar2(100);
SQL> exec :xm := '<root><empty></empty></root>';
PL/SQL procedure successfully completed.

Lets try to extract value of empty tag:

SQL> var xmv varchar2(100);
SQL> exec :xmv := XMLType(:xm).extract('//empty/text()').getstringval();
BEGIN :xmv := XMLType(:xm).extract('//empty/text()').getstringval(); END;
*
ERROR at line 1:
ORA-30625: method dispatch on NULL SELF argument is disallowed 
ORA-06512: at line 1 

Lets do the same with SQL:

SQL> select XMLType(:xm).extract('//empty/text()').getstringval() from dual;
XMLTYPE(:XM).EXTRACT('//EMPTY/TEXT()').GETSTRINGVAL()
--------------------------------------------------------------------------------

Now we get NULL. You can confirm this with that query:

SQL> select nvl(XMLType(:xm).extract('//empty/text()').getstringval(),
'(NULL)') from dual;
NVL(XMLTYPE(:XM).EXTRACT('//EMPTY/TEXT()').GETSTRINGVAL(),'(NULL)')
--------------------------------------------------------------------------------
(NULL)

So, be careful when you extract data from XMLType in PL/SQL. If you want NULL to be returned for empty tags then you should use function like this:

CREATE OR REPLACE
FUNCTION XML_EXTRACT_NO_EXCEPTION
( p_xml IN XMLTYPE
, p_xpath IN VARCHAR2
, p_namespace IN VARCHAR2 default NULL
) RETURN VARCHAR2 AS
BEGIN
return case when p_xml.extract(p_xpath, p_namespace) is not null
then p_xml.extract(p_xpath, p_namespace).getstringval()
else NULL
end;
END XML_EXTRACT_NO_EXCEPTION;

And then you can use it safe in PL/SQL:

SQL> exec :xmv := XML_EXTRACT_NO_EXCEPTION(XMLType(:xm), '//empt/text()');
PL/SQL procedure successfully completed.
SQL> print :xmv
XMV
--------------------------------------------------------------------------------

Another reason for ORA-30625 can be that you did not provide namespace in execution of extract. More on this in my post
XMLType and Namespace problem.

Paweł

XMLType and Namespace problem

I've run into some strange problem with XMLType and XML namespaces. I'll try to make a showcase. Lets define variable and simple XML content

SQL> var x varchar2(200);
SQL> exec :x := '<feed
xmlns="http://www.w3.org/2005/Atom"><id>test_id</id></feed>'
PL/SQL procedure successfully completed.

Try to find out what tag is on root:

SQL> select XMLType(:x).getRootElement() from dual;
XMLTYPE(:X).GETROOTELEMENT()
--------------------------------------------------------------------------------
feed

Lets find out the namespace:

SQL> select XMLType(:x).getNamespace() from dual;
XMLTYPE(:X).GETNAMESPACE()
--------------------------------------------------------------------------------

Seems to be no namespace. But there was namespace in XML definition. Lets try extract data:

SQL> select XMLType(:x).extract('/feed').getStringVal() from dual;
XMLTYPE(:X).EXTRACT('/FEED').GETSTRINGVAL()
--------------------------------------------------------------------------------

So nothing was extracted. It's not what I was expecting. Ok, lets try to force namespace:

SQL> select XMLType(:x).extract('/feed',
'xmlns="http://www.w3.org/2005/Atom"').getStringVal() from dual;
XMLTYPE(:X).EXTRACT('/FEED','XMLNS="HTTP://WWW.W3.ORG/2005/ATOM"').GETSTRINGVAL(
--------------------------------------------------------------------------------
<feed
xmlns="http://www.w3.org/2005/Atom"> <id>test_id</id>
</feed> 

Now works fine.
For me it is little confusing: there is no namespace, but you must provide namespace if you want to extract data.
Paweł

Oracle 11g new features, part 2

There are two new interesting sources for 11g new features.
Tim Hall post The Next-Generation Self-Managing Database (11g)
And Lewis Cunningham post OOW: Enhancements in Oracle 11g PL/SQL
Also these features or enhancements seems very promising

1. PL/SQL Native Compilation. There is no need for external C compiler, so it’s easier to make PL/SQL faster.
2. New Data Type: simple_integer. Another performance booster.
3. Intra-unit inlining. - this is feature that I was missing for years. Now I can create complex expressions and reuse it easily. I hope it will work also in SQL
4. Database Replay and SQL Replay - this is very interesting idea.

I'm looking forward to play with these features.

Cheers, Paweł