Getting multi row text items from Designer repository

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.

SELECT txt.txt_text
,      txt.txt_ref
FROM   cdi_text txt
WHERE  txt.txt_type = 'CDIDSC'
, add_cast AS
SELECT application_system_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
,        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.

Extending Designer ROB to display User Extensibility attributes

Lucas Jellema from Amis wrote a nice blog page here about invoking the full property palette for secondary elements in the Repository Object Browser component of Oracle Designer. I mention it because we use Designer to store the physical model for our datawarehouse and we were a little disappointed to find that the ROB wasn’t showing us all the attributes from the repository – particularly the User Extensibility attributes we’d added to handle some warehousey type attributes that Designer doesn’t handle out of the box.

After reading the article Lucas had written I followed his instructions on a test repository installation and found that with the exception of one issue it worked perfectly. The issue I encountered was that when I tried to use the ROB to display the full property palette (for a Table Column) it was giving an error web page showing the message:

The requested URL /pls/rob/odwaprop.property_palette was not found on this server.

I traced through the code and eventually added some exception handling so that I could determine the error being raised which uncovered the following stack:

An error occurred during processing of this page SQLERRM:
ORA-06550: line 13, column 21: PL/SQL:
ORA-00903: invalid table name
ORA-06550: line 3, column 13:
PL/SQL: SQL Statement ignored

I couldn’t figure out where exactly in the bowels of the Designer code this was emanating from so I just added a simple exception handler to ignore this error:

when others then
if sqlcode = -6550 then
end if;

(I know this isn’t perfect before anyone points it out – but it works and saves me having to dig into the bowels of Oracle Designer code to determine exactly why this error is being raised in the first place and then potentially fixing it (if possible)…the ROB is read only so it’s not like anything is going to be harmed by using the above hack).

The article by Lucas shows an example of making Entity Attributes hyperlinks that bring up the full property palette for the attribute…I’ve used the same principles to turn the Column names into hyperlinks that bring up the full property palette for the column and the Table name into a hyperlink that brings up the palette for the Table – it works in the same way except that the package to modify is CDWP_TBL and the code added is:

First in summary_column_definitions I made the following change to turn the column names into hyperlinks:

— START******************************************************
— Jeff Moss Start Change for property palette

( odwaprop.palette_link
( p_sac_irid => r_col.col_id
, p_label => r_col.col_name
, p_type_id => 5014 — type ID for COLumn
( cdwpbase.local_link
( p_bookmark => ‘COL’to_char(r_col.col_id)
, p_text => cdwp.add_images(‘{‘l_col_gif’}’) — 2.2
— END********************************************************

…then in the list_table_definitions procedure just after the htp.headOpen call I added the call to create the palette function itself:

— START******************************************************
— Jeff Moss Start Change for property palette

( p_workarea_irid =>odwactxt.get_workarea_irid
, p_session_id => p_session_id
— END********************************************************

…and, finally, further down in the list_table_definitions procedure where the Table Name is displayed I made the following modification:

— START******************************************************
— Jeff Moss Start Change for property palette

( p_irid => r_tbl.tbl_irid
, p_ivid => r_tbl.tbl_ivid
, p_label => r_tbl.tbl_name
, p_type_id => 4974 — type ID for TaBLe
— END********************************************************

After all that it works fine and displays a full property palette for these elements – a palette which includes any User Extensibility attributes.

We store things like the following in our User Extensibility attributes:

Legacy Key Column Indicator
Legacy Source Table
Legacy Source Schema
Legacy Source Database

…we’ll probably use more over time but at least now the rich metadata we’re capturing in Designer is available for display via the ROB.

Incidently, changing Oracle supplied code is not normally allowed but I did raise an SR to ask for permission and support basically said that as long as you don’t expect to get support on code you have modified and you restrict the changes to the ROB packages which are in a read only area of the system then it is OK for us to do this. You’d obviously need to gain the appropriate agreement yourselves if you decide to pursue this approach.

What made it funny going through this stuff was that I wasn’t aware of who Lucas Jellema was – I’d heard his name and that of Amis but didn’t really know him. It didn’t take long for me to find his name was plastered liberally over the version control headers of the Oracle supplied ROB packages I was modifying before I realised he’d obviously had some past experience of this stuff whilst working at Oracle on the product. I figured he probably knows what he’s talking about on this front.

Thanks to Lucas for this one – it’s been very useful to us.