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ł

Links 2006-11-20

Links 2006-11-20:

• Tutorial for Oracle HA
• Easy XML - Let the Database do the Work
• Mastering XML Generation in Oracle Database 10g Release 2
• Extracting Master-Detail data from an XMLType with a single SQL statement

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ł

The Palm: The Eighth Wonder of World

You must definitely see The Palm: The Eighth Wonder of World. Incredible...

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ł

SQL Developer 1.1 preview

Today I give a try to new SQL Developer 1.1. Installation on Windows 2000 goes smoothly without any problems. Users interface is almost the same as in previous version. One change that I noticed at one is search plug in, that allows user to search Oracle documentation and also Ask Tom site. It integrated well with my Firefox . There are 2 new features that was missing in previous version:

• XMLType support - now XML data is displayed as query results, so you can open pop-up and see all details
• Display for Date type - you can define in preferences how dates should be displayed

What I'm still missing is possibility to update more then one row in Index Organized Tables (IOT) in single transaction. At the moment when you change one row you must commit transaction. If you do not do that, whole transaction will be forced to be rolled back.
Changes goes in good directions. Might be in few months i will completely stop to use TOAD...
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ł