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ł

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ł

Empty String IS NOT NULL?

Today friend of mine show me interesting case:

declare
  var varchar2(3);
  chr char(3);
begin
  var := ''; -- Empty string here
  chr := var;
  if var is null then
    dbms_output.put_line('VAR IS NULL');
  end if;
  if chr is null then
    dbms_output.put_line('CHR IS NULL');
  else
    dbms_output.put_line('CHR IS NOT NULL');
  end if;
end;
/
VAR IS NULL
CHR IS NOT NULL

I must say that I was expecting that both variables will be null. It might be my lack of experience with CHAR datatype as I usually use VARCHAR2 even when it is expected that string will have constant length.

declare
  var varchar2(3);
  chr char(3);
begin
  var := null;
  chr := var;
  if var is null then
    dbms_output.put_line('VAR IS NULL');
  end if;
  if chr is null then
    dbms_output.put_line('CHR IS NULL');
  else
    dbms_output.put_line('CHR IS NOT NULL');
  end if;
end;
/
VAR IS NULL
CHR IS NULL

Just as expected. So it seems that in some cases NULL and empty string is not equivalent. But this difference can be only seen in PL/SQL. When I tried to reproduce this in pure SQL it was always NULL:

SQL> create table a(v1 varchar2(3), v2 varchar2(3), c1 char(3), c2 char(3));
Table created.
SQL> insert into a values (null, '', null, '');
1 row created.
SQL> select dump(v1) v1,dump(v2) v2,dump(c1)c1,dump(c2) c2 from a;
V1    V2    C1    C2
----- ----- ----- -----
NULL  NULL  NULL  NULL
SQL> update a set c1 = v1, c2 = v2;
1 row updated.
SQL> select dump(v1) v1,dump(v2) v2,dump(c1)c1,dump(c2) c2 from a;
V1    V2    C1    C2
----- ----- ----- -----
NULL  NULL  NULL  NULL

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ł