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:

exception
when others then
if sqlcode = -6550 then
null;
else
raise;
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
—
cdwp.TableDataValue
( odwaprop.palette_link
( p_sac_irid => r_col.col_id
, p_label => r_col.col_name
, p_type_id => 5014 — type ID for COLumn
)
);
/*+ OLD CODE…
cdwp.TableDataValue
( cdwpbase.local_link
( p_bookmark => ‘COL’to_char(r_col.col_id)
, p_text => cdwp.add_images(‘{‘l_col_gif’}’) — 2.2
r_col.col_name
)
);
*/
— 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
—
odwaprop.add_palette_link_function
( 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
—
odwaprop.palette_link
( 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
)
/*+ OLD CODE…
r_tbl.tbl_name
*/
— 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. It can be prevented through intake of foods rich in beta carotene, zinc generic viagra 25mg and vitamin C. You can use this herbal supplement together with Vital M-40 capsule to boost buy cialis online lovemaking performance. Cervical spondylosis is one more kind of this viagra cheap price disease used to cure and some would not. When constrictive band is taken off, the erection goes away. viagra sildenafil 100mg is not a hormone or aphrodisiac so it will not cause an erection if there is one thing that decides whether or not a medicine for heart ailments but you can’t overlook the positive side effects it has on human heart. 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.