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
,      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.

Labels: blog, code, designer