I’ve mentioned Lucas Jellema before when talking about Oracle Designer – he’s helped me out again today via this post on the AMIS blog.

What I wanted, was to be able to extract, using a SQL query against the designer repository, the “Description” attribute from our Tables so that I could create table comments in the database which had the description from the table in Designer.

Extracting scalar attributes is simple enough, however, description is a multi row text property so it needed a bit more thought…and rather than reinvent the wheel, I had a look at Lucas’ stuff and sure enough found the post above. It was almost what I wanted, only I had to change it to look for table stuff rather than entities and attributes.

I used the same TYPE and sum_string_table function as Lucas so if you’re trying to use this stuff you’ll probably need to read Lucas’ article first.

The query I ended up with is below…it’s been “sanitized” somewhat, but I’m sure you’d get the picture, if retrieving stuff out of the designer repository is a requirement of yours.

WITH dsc AS
(
SELECT txt.txt_text
,      txt.txt_ref
FROM   cdi_text txt
WHERE  txt.txt_type = 'CDIDSC'
)
, add_cast AS
(
SELECT appsys.name application_system_name
,      b.name table_name
,      b.alias
,      CAST(COLLECT(dsc.txt_text) AS string_table) tab_description
FROM   dsc
,      designer.ci_application_systems appsys
With the intake of capsules the massage of Mast Mood oil is very fruitful to solve the purpose of impotence or erectile cheap tadalafil pills  dysfunction in all the men. cheapest cialis without prescription Simmering arguments, poor communication, betrayal of trust, and other barriers to intimacy can turn off your sex drive. Diabetic  discount viagra men are more prone to impotence than non-diabetic men. There are certain side effects that get cialis online  the pill holds. ,      designer.ci_app_sys_tables a
,      designer.ci_table_definitions b
WHERE  dsc.txt_ref = a.table_reference
AND    b.irid = a.table_reference
AND    a.parent_ivid = appsys.ivid
GROUP BY appsys.name 
,        b.name
,        b.alias
)
SELECT application_system_name
,      table_name
,      alias
,      sum_string_table(tab_description)
FROM   add_cast
WHERE  application_system_name = 'MY_APP_SYS'
and    table_name = 'MY_TABLE_NAME'
/

Thanks Lucas!

On another note, regular visitors may realise I’ve now got my own oramoss dot com domain and that my blogger blog is published on that domain now.

Thanks to Andreas Viklund for the template.

If anyone sees anything untoward with the new site please feel free to drop me a note. It’s a bit thin on content but I’ll work on that over time.