Software Engineer Thoughts

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ł

Anonymous said...

Nice post... only downside with this approach is that if you have a large number of columns, you may end up with :

SQL> select table_name,
2 ltrim(max(SYS_CONNECT_BY_PATH(column_name, ',')), ',') column_list
3 from user_tab_columns
4 where table_name = 'LARGE_TABLE'
5 start with column_id = 1
6 connect by table_name = prior table_name
7 and column_id = prior column_id +1
8 group by table_name
9 /
from user_tab_columns
*
ERROR at line 3:
ORA-01489: result of string concatenation is too long

05 June, 2007 09:03

Paweł Barut said...

Thats right. The limit for string length in SQL is 4000 characters.

05 June, 2007 09:58

Anonymous said...

This comment has been removed by a blog administrator.

05 June, 2007 15:48