I am currently involved in the migration of a pretty substantial application, developed with Oracle Designer’s Web Server Generator, to the Oracle ADF technology stack, using JHeadstart. You know I am a big fan of JHeadstart for a number of reasons, but this particular application is ideally suited for JHeadstart (or I should say the other way round of course). JHeadstart and ADF can do everything Web PL/SQL Toolkit and the Web Server Generator can do – and much, much more. So both end users and developers of this application stand to gain from the migration.
However , due to special technical limitations in the Web Server Generators and the inventive hand-crafted work-arounds, I can use the JHeadstart Designer Generator migration tool only for th 60% really simple maintenance modules. The other 40% is better off with building from scratch.
There is one piece of information in Oracle Designer that is quite useful to me – that is the Domain Definitions. The sets of allowable values that power the dropdownlists and radio groups is needed in JHeadstart’s Application Definition file, and the current edition of the JHeadstart Designer Generator does not allow the migration of Domains (only complete modules are supported). There are two approaches to getting the domain definitions out of Designer:
Well, if you count simply typing the definitions in JHeadstart’s plug in editor there are three approaches…
The first more or less automated one is creating a Table Definition in Designer with as many columns as there are domains you want to migrate. Then create a dummy module with a Module Component that has a base table usage of that table and everyone of its columns. Then use the JHeadstart Designer Generator to migrate that particular module. The JDG will migrate all required domains – along with a table and module you do not need. You can copy the domain definitions from the migration result and paste them into the JHeadstart Application Definition file.
Using SQL/XML to extract the domain definitions
The other – and much better approach – is the following: use a simple SQL query against the Oracle Designer Repository – in the schema of the Repository Owner or one of the Oracle Designer users – to retrieve the domain definitions and turn them into the piece of XML.
Here is a simple query to create domain definitions for the JHeadstart Application Definition, based on existing Domain definitions in Oracle Designer:
begin
jr_context.set_workarea('MY_WORKAREA');
end;
/
set pagesize 400
set linesize 200
select xmlelement("Domains"
, (select xmlagg
( xmlelement
("Domain"
, xmlattributes
( name as "name"
, 'static' as "type"
)
, ( select xmlagg
( xmlelement
( "AllowableValue"
, xmlattributes
( atv.meaning as "meaning"
, atv.low_value as "value"
)
)
)
from ci_attribute_values atv
where atv.domain_reference = dom.id
)
)
)
from ci_domains dom
where dom.name like 'WHATEVER_NAME_YOU_LIKE%'
)
)
from dual
/
This query produces XML output that you can copy and paste to the JHeadstart Application Definition file. Open the file in XML editor by double clicking the file in the navigator. Before you start editing the XML file directly, make sure you close the JHeadstart Application Definition Editor.
When you next open the Application Definition File editor, you will see the Domains like you are used to:
Extracting Domain Definitions from the CG_REF_CODES table
If you have generated your domains into the database – using the infamous CG_REF_CODES table – you can extract the domain definition from that table, using the following query:
select xmlelement
( "Domains"
, ( select xmlagg
( xmlelement
( "Domain"
, xmlattributes
( dom.domain as "name"
, 'static' as "type"
)
, ( select xmlagg
( xmlelement
( "AllowableValue"
, xmlattributes
( atv.rv_meaning as "meaning"
, atv.rv_low_value as "value"
)
)
)
from cg_ref_codes atv
where atv.rv_domain = dom.domain
)
)
)
from ( select distinct
rv_domain domain
from cg_ref_codes dom
where dom.rv_domain like 'A%'
) dom
)
)
from dual
It is very much along the same lines as the earlier query against the Designer API.
Extracting Domains from normal (lookup) tables
It is also possible the values for a domain are in normal database tables. You can of course define a dynamic domain in JHeadstart, that will always read from the table to have the latest set of values. However, perhaps the table is not one that is accessible to your application, or for some other reason you want to turn the values in the table into a Domain Definition. The next query demonstrates this for a domain that will consist of all Job values present in the EMP table (the SCOTT schema, remember):
select xmlelement
( "Domains"
, ( select xmlagg
( xmlelement
( "Domain"
, xmlattributes
( 'Job' as "name"
, 'static' as "type"
)
, ( select xmlagg
( xmlelement
( "AllowableValue"
, xmlattributes
( jobs.job as "meaning"
, jobs.job as "value"
)
)
)
from ( select distinct
job
from emp
) jobs
)
)
)
from dual
)
)
from dual
/
The distinct values populate a very simple domain:
<Domains>
<Domain name="Job" type="static">
<AllowableValue meaning="ANALYST" value="ANALYST"></AllowableValue>
<AllowableValue meaning="CLERK" value="CLERK"></AllowableValue>
<AllowableValue meaning="MANAGER" value="MANAGER"></AllowableValue>
<AllowableValue meaning="PRESIDENT" value="PRESIDENT"></AllowableValue>
<AllowableValue meaning="SALESMAN" value="SALESMAN"></AllowableValue>
</Domain>
</Domains>
