The Repository Reports in the Oracle Designer web-interface Repository Object Browser provide an overview of the most useful properties of the most important components of the elements in the Oracle Designer Repository, such as Entities, Tables, Domains and Module Definitions. It was by design that not all properties of all elements were included, to prevent cluttering up the report. As a result, the reports are conscise, to the point and quite usable. However, they do not provide all details of each element.
From the Repository Report, the user can invoke the Object Browser, a RON (Repository Object Navigator) like tool that displays property palettes for all secondary access element under the chosen Entity, Table or Module Definition. So there is a way to learn all properties for all elements. In this article I will demonstrate how you link the Property Palette to each (secondary) element in the Reports.
It turns out to be very simple to provide a simple shortcut to this property palette. With just a very tiny change in the code of the Repository Object Browser, we can provide a direct link to a popup window with the property palette for any element, primary or secondary, in the Report.
It is subtle, but in this screenshot, the names of the Attributes are turned into hyperlinks. If you click on one of these hyperlinks, you are taken to the Property Palette for that Attribute. So here is the same report, this time with hyperlinks:
And here is the result of clicking one of those hyperlinks: the Property Palette opens. Note: in this picture, the properties in the property palette can be edited and the changes can be saved. That will not (yet) be the case in your own environment. Being able to manipulate data in Designer through the RON is functionality that I am currently working on and hope to publish before too long. For now, when you apply the changes described in the remainder of this post, the property palette will be read-only.
How to implement this Property Palette Shortcut
All code for displaying the property palette is already available in the ROB, so we only need to write the code that creates the hyperlink that will open the popup window and link it to the property palette for the current element.
We add two functions to the package odwaprop. You can either make these changes directly in the database, through TOAD, PL/SQL Developer, JDeveloper or some similar tool, or you can locate the source of this package – ORACLE_10gds_or_9ids_HOMErepadm61robddlodwaprop.pks and .pkb.
The modifications in the odwaprop package specification are these:
/** * This function creates a JavaScript function that processes calls from hyperlinks created by the function palette_link. * The JavaScript function is capable of opening a popup window that contains the property palette for a specific element in * the Designer Repository. **/ procedure add_palette_link_function ( p_workarea_irid in number , p_session_id in number ); /** * This function creates a hyperlink that opens the property palette for a specific object. * The object is identified by an identifier - IRID, IVID or SAC_IRID - and a Type Id. * If the ivid is provided, that value is used for linking to the property palette. If no IVID * is available, the IVID needs to be derived from either the PAC irid or the SAC irid **/ function palette_link ( p_irid in number default null -- in case of a PAC , p_ivid in number default null -- in case of a PAC or PAC , p_sac_irid in number default null , p_label in varchar2 , p_type_id in number ) return varchar2 ;
The corresponding implementation of the functions are to go into the odwaprop package body – any location will do, near the top or near the bottom is probably easiest:
/** * This function creates a JavaScript function that processes calls from hyperlinks created by the function palette_link. * The JavaScript function is capable of opening a popup window that contains the property palette for a specific element in * the Designer Repository. **/ procedure add_palette_link_function ( p_workarea_irid in number , p_session_id in number ) is begin htp.p(' <SCRIPT LANGUAGE="JavaScript"><!-- function palette(ivid, type_id) { paletteWindow=open ( ''odwaprop.property_palette?p_session_id='||p_session_id||'&p_ivid=''+ivid+''&p_type_id=''+type_id+''&p_classification={TY=''+type_id+''}{WA_IRID='||p_workarea_irid||'}&p_print_if_null=N'', ''paletteWindow'' , ''toolbar=no,scrollbars=yes,location=no,directories=no'' +'',status=yes,menubar=no,resizable=yes'' +'',height=600,width=850'' ); paletteWindow.moveTo(520,300); paletteWindow.focus(); } //--></SCRIPT> '); end; /** * This function creates a hyperlink that opens the property palette for a specific object. * The object is identified by an identifier - IRID, IVID or SAC_IRID - and a Type Id. * If the ivid is provided, that value is used for linking to the property palette. If no IVID * is available, the IVID needs to be derived from either the PAC irid or the SAC irid **/ function palette_link ( p_irid in number default null -- in case of a PAC , p_ivid in number default null -- in case of a PAC or PAC , p_sac_irid in number default null , p_label in varchar2 , p_type_id in number ) return varchar2 is l_ivid number(38):= p_ivid; begin if l_ivid is null then if p_irid is not null then l_ivid := cdwpbase.get_best_ivid(p_irid); else l_ivid:= cdwpbase.get_sac_ivid(p_irid => p_sac_irid , p_type_id => p_type_id); end if; end if; return htf.anchor('javascript:palette('''||l_ivid||''','||p_type_id||')',p_label); end palette_link;
We now have the foundation to build on. We can add shortcuts – hyperlinks – to the Property Palette in any of the Repository Reports (the cdwp_ELEMENT_SHORTNAME packages such as cdwp_ent, cdwp_fun) by doing two things:
- Add one and only one call to odwaprop.add_palette_link_function from the Report, ideally between the htp.headOpen and htp.headClose statement. This ensures that the JavaScript function palette is added to the HTML page
- For every shortcut or hyperlink, you need to add a call to odwaprop.palette_link. This creates a hyperlink that when clicked will invoke the palette function
As an example, lets implement the property palette short-cut in the Entity Report – just like you say in the screenshots. We get to the source of the cdwp_ent package body. In the list_entities procedure that sets up the HTML report, we locate htp.headOpen and htp.headClose. Somewhere in between we determine a good spot for the first call:
... htp.headOpen; cdwp.include_report_styles; cdwp.write_about(package_name, revision_label); odwaprop.add_palette_link_function ( p_workarea_irid =>odwactxt.get_workarea_irid , p_session_id => p_session_id ); if odwactxt.get_one_file = 'Y' then htp.title ...
Now every element that we need to provide a property palette shortcut for must be located in the cdwp_ent package body and wrapped somehow in a palette_link. Let’s start with Attributes. These are included in the report in the list_attributes procedure. Inside that procedure, we find the place where the attribute name is written in the report. Instead of just writing the name, let’s write the name as a hyperlink to the property palette:
-- Change for link to property palette cdwp.TableDataValue ( odwaprop.palette_link ( p_sac_irid => r_att.att_id , p_label => r_att.att_name , p_type_id => 5010 ) , 1); -- End Change for property palette
Alternatively, instead of turning a text into a hyperlink, we can also add an image to a text, with the image functioning as shortcut. Any image in the ROB_IMAGES directory on the server – the directory that contains all normal gifs used by the ROB – can be used. The code required for this is the following in this case inside the procedure cdwp_ent.list_relend :
... end if; cdwp.tableRowOpen; cdwp.TableDataHeading(null); -- Change for link to property palette cdwp.TableDataValue ( l_relend ||odwaprop.palette_link ( p_sac_irid => r_relend.relend_id , p_label => CDWP.add_images('{project$c.gif}') , p_type_id => 5025 ) , p_colspan => 2 ); -- End Change for property palette cdwp.TableDataValue ( cdwp.report_link -- 2.2 ( p_el_id => r_relend.to_entity_reference ...
The effect is seen in this screenshot: note the little icons behind the relationship name:
If you wonder how to find the value of the type_id, identifier of the type of element, such as Table, Entity or Module, you may want to use something like the following query (this one I used to find the type id for Relationship(ends):
select et.short_name , et.id , et.NAME from rm_element_types et where et.short_name like 'REL%' /
Next Steps
This post can lead to several things. First of all, you can decide to go into all the cdwp_element_shortname packages and add property palette shortcuts for all secondary elements.
Another thing that could be done is get rid of the popup window (JavaScript openWindow) and instead make use of a DIV element inside the Report and AJAX technology to populate the DIV contents – see for more on AJAX for example Enhancing Web Applications with AJAX, XMLHttpRequest, UIX – Report from An AMIS Ten Minute TechTalk.
The last thing I can think of moving forward is adding editing capabilities to the ROB. The ROB would then not only be a R(ead)O(nly)B(rowser), but would become even more useful, allowing users without Designer Clients installed – and licenses paid or training received – to make simple or even advanced changes to Designer elements. I am almost at a point where I can publish the Update capability – insert and delete are somewhat harder it seems.
I’ve since had Oracle support ocnfirm that I can make these changes to the ROB code without affecting our support agreement so my client is likely to implement the approach now.
Thanks for your help on this page Lucas.
More details…I’ve actually managed to code around it now by adding exception handling to ODWAPROP.PROPERTY_PALETTE since it seems to be constructing a piece of SQL which looks like “SELECT column1, column2 etc… FROM “…i.e. the name of the TABLE to select from is missing and when it runs it with the EXECUTE IMMEDIATE it falls over and fails the property palette page. Adding exception handling with this:
exception
when others then
htp.htmlopen;
htp.headopen;
htp.title(Rob_msg.GetMsg(Rob_msg.CAP213_ODWAPROP_PROPPAL,”,”,”,”)||l_label);
htp.headclose;
htp.bodyopen;
htp.p(‘An error occurred during processing of this page’);
htp.p(‘SQLERRM: ‘||SQLERRM);
htp.bodyclose;
htp.htmlclose;
allows it to work albeit displaying the error encountered on the resulting property palette webpage.
I’ve no idea why it does this but I have my workaround so I’m happy for now.
I’ve tried to get this working on a 10g (9.0.4) designer install running on a 10.2.0.2.0 database all on Windows XP and I’m falling at the final hurdle.
I’ve added all the code for the entity/attribute link as above and it shows the name of the attribute as a hyperlink now. But when I click on the link it errors out with a 404 error:
The requested URL /pls/rob/odwaprop.property_palette was not found on this server.
I don’t suppose you know whether it should/not work on the above versions ? …just in case I’m beating my head against the wall trying to work out what the problem is…