Oracle Blog Aggregators and Bloggers Community

Today I've noticed that my blog became aggregated on official Oracle Blogs site. For me it is really good news as it can bring me more readers. My blog is also aggregated at OraNA.info by Eddie Awad (Oracle blogger of the year 2006) and on orablogs.com by Brian Duff (Oracle employee). Unfortunately I cannot find any of my post on OTN Semantic Web. I hope it will change in near future, as this is still beta release...
For me it seems that latest discussions about Oracle Bloggers community have positive impact. Official list of Oracle blogs not maintained by Oracle employees is more complete now. Also The Pythian Group is doing great job for Oracle community by publishing series of Log Buffer.

Cheers, 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ł

OraNA.info upgraded

Eddie Awad just announced that he has improved functionality of Oracle Blogs aggregator OraNA.info. Now it shows post from long history, not only last 20. It is now really usable.
And if you are Oracle Blogger, go and join OraBloggers group on Google groups.

Cheers, Paweł

Zend Core for Oracle v 2.0.1

Zend has announced new version of Zend Core for Oracle.

Key features contained in all Zend Core V2.0 releases include:

• Improved Windows Performance and Reliability – Up to 200-300%
• PHP 5.2.1 enhanced with several key security and bug fixes
• Unified "stack" installer - Bundled copies of Apache and MySQL for quick and easy install and configuration
• Bundled Zend Framework for fast and easy application development
• Universal Database Support - drivers provided for most popular databases.
• Mac OS X Support
• Zend Core Updater - Automatically download Zend Network updates

Give it a try...

Cheers, Paweł

Moving domain index to another tablespace

It seems to be very easy task that can be accomplished using rebuild option:

ALTER INDEX MY_INDEX REBUILD 
TABLESPACE NEW_TBS;

We can even do it online for most indexes:

ALTER INDEX MY_INDEX REBUILD 
TABLESPACE NEW_TBS ONLINE;

But trying this for domain index (ex. Spatial Index) causes error:

ALTER INDEX MY_GEOMETRY rebuild tablespace users;
ORA-29871: invalid alter option for a domain index

What is the reson and how to overcome this? Domain index is in fact set of other objects. For spatial index it is table (MDRT_*). Spatial Index can be moved quite easy with

ALTER INDEX MY_GEOMETRY 
REBUILD PARAMETERS('TABLESPACE=NEW_TBS');

Oracle Text CONTEXT index is set of tables:

• DR$[index_name]$I
• DR$[index_name]$K
• DR$[index_name]$N
• DR$[index_name]$R

Unfortunately to move context index you have to drop and recreate text index. But first storage parameters have to be defined:

begin
   ctx_ddl.create_preference('MY_TEXT_STORE', 'BASIC_STORAGE');
   ctx_ddl.set_attribute('MY_TEXT_STORE', 'I_TABLE_CLAUSE',
       'tablespace NEW_TBS');
   ctx_ddl.set_attribute('MY_TEXT_STORE', 'K_TABLE_CLAUSE',
       'tablespace NEW_TBS');
   ctx_ddl.set_attribute('MY_TEXT_STORE', 'R_TABLE_CLAUSE',
       'tablespace NEW_TBS');
   ctx_ddl.set_attribute('MY_TEXT_STORE', 'N_TABLE_CLAUSE',
       'tablespace NEW_TBS');
   ctx_ddl.set_attribute('MY_TEXT_STORE', 'I_INDEX_CLAUSE',
       'tablespace NEW_TBS COMPRESS 2');
   ctx_ddl.set_attribute('MY_TEXT_STORE', 'P_TABLE_CLAUSE',
       'tablespace NEW_TBS');
end;
/

and then just drop and create index with changed parameters

create index MY_TEXT_I on MY_TAB(text_column) 
indextype is ctxsys.context parameters(
'storage MY_TEXT_STORE');

For more information go to Oracle Text Application Developer's Guide

Cheers, Paweł