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

OK I spoke to soon...first problem is MS Livewriter can't access my new blog - good job there is no way to undo the upgrade from Blogger Beta back to the old Blogger ;-(

Guess I'll just need to figure it out...

Labels: blog

Just upgraded my blog to Blogger Beta. Seemed painless enough - just had to create a Google Account and upgrade the template...I've not changed anything other than choose a template - Stretched Denim Light - I'll tinker with it when I get five minutes but otherwise I just want to check out some of the new features...particularly images via flickr if I read it correctly...sounds cool.

Labels: blog

You may have noticed the reappearance of a STATCOUNTER link/graphic on the right hand menu - I'd not noticed it disappear in a template rejig I did a while back but when I went to check my stats and found it was showing that nobody was visiting I realised my mistake - doh!

If anyone views my stats and wonders why there is a big "hole" in Dec/Jan/Feb then that's why.

Back to normal now, i.e. both my readers (thanks mum, thanks dad) appear in my stats!

Labels: blog