Example Oracle Designer API script – query and update all Item prompts that end with a colon

For the Designer 10g/WebForms 10g project I am involved with, we had a rather unappealing task to allocate. It involved an unknown number of items prompts that ended with a colon where that colon should be removed. So the task consisted of two parts: find all those prompt and then change them. All our Forms have been generated from Oracle Designer, so it was not useful to use the JDAPI to search and replace in all FMB files, which was our first inclination. Instead, it had to be done in the Designer Repository. I volunteered, assuming that at least the search part should be fairly easy to automate against the API. That turned out to be true – and then I found 136 prompts that needed updating. At that stage I decided to also automate the update part of the task, as I did not feel comfortable opening and chekcing out 34 modules, locating 136 items and updating them and afterwards checking the modules back in.

This post describes the code I wrote against the Designer API to perform these steps. With small modifcations you should be able to make use of this code for your own operations in Oracle Designer.

First step: finding the prompts that end with ‘:’

I looked at the Prompt Definition in all Columns as well as the Prompt in all Items. The latter were important for now, the first would have an influence on new modules in the future.

The query for the columns:

exec jr_context.set_workarea(‘AGS_WA’)

select tbl.name “table”
,      col.name “column”
,      col.prompt
from   ci_relation_definitions tbl
,      ci_columns col
where  tbl.id = col.table_reference
and    regexp_like(prompt,’:$’) — or:  col.prompt like ‘%:’  — or: and regexp_like(prompt,’:$’)
order
by     1,2
/

The query for the Module Items:

select mde.name “module”
,      mde.id mde_id
,      mde.ivid mde_ivid
,      mco.name “module component”
,      itm.name “item”
,      itm.prompt
,      itm.item_type
,      itm.id itm_id
from   ci_general_modules mde
,      ci_module_components mco
,      ci_items  itm
where  mde.id = mco.general_module_reference
and    mco.id = itm.module_unit_reference
and    regexp_like(itm.prompt,’:$’)  — itm.prompt like ‘%:’  — or:
order
by     1,2
/

Note: I rely on the fact that we did not use Reusable Module Components. Those do not have a general_module_reference set and would necessitate the use of the Module_Inclusions view.

Second step: updating – through the Designer API – all Item Prompts

The PL/SQL script for updating the prompts look somewhat daunting at first. You should realize though that only a few parts are specific to my prompt updating challenge; the remainder is fairly standard code that would be used in every API DML script to write. It is really only the itm cursor and the if-statement starting with if itm.item_type =’DBI’ that are specific to this problem. To run the script:

set serveroutput on size 99999
exec prompt_mgt.update_prompts
The whole block of code to create the package prompt_mgt:

create or replace
package prompt_mgt
is
procedure update_prompts;
end;
/
for the Specification and:

create or replace
package body prompt_mgt
is

act_status VARCHAR2 (1); — Activity status
act_warnings VARCHAR2 (1); — Activity warning flag

procedure instantiate_messages;
procedure handle_exception;

procedure update_prompts
is
l_mde_id number(38):=-1;
l_ivid   number(38);
l_checked_out_by_me boolean;

l_dbi     ciodata_bound_item.data;
l_ubi     ciounbound_item.data;

begin
l_dbi.i.prompt:= true;
l_ubi.i.prompt:= true;
— Set the context workarea
jr_context.set_workarea(‘AGS_WA’);
if cdapi.initialized = FALSE
then
— Initialize the API global items
cdapi.initialize;
end if;
jr_context.set_working_folder
( folder_name => ‘AGS’
);
— Open a new activity
cdapi.open_activity;
dbms_output.put_line
( ‘Opened Activity’);

for itm in (
select mde.name “module”
,      mde.id mde_id
,      mde.ivid mde_ivid
,      mco.name “module component”
,      itm.name “item”
,      itm.prompt
,      itm.item_type
,      itm.id itm_id
from   ci_general_modules mde
,      ci_module_components mco
,      ci_items  itm
where  mde.id = mco.general_module_reference
and    mco.id = itm.module_unit_reference
and    regexp_like(itm.prompt,’:$’)  — itm.prompt like ‘%:’  — or:
order
by     1,2) loop
if l_mde_id <> itm.mde_id
then
— first deal with the left overs
if l_mde_id > 0 and l_checked_out_by_me
then
l_ivid:= jr_version.CHECK_IN
( i_irid => itm.mde_id
, i_ivid=>
l_ivid
&nbs
p;              , i_vlabel => jr_version.get_next_vlabel(itm.mde_id, i_ivid=> l_ivid)
, I_NOTES => ‘Checked out in order to update the prompt property for all items that contain a colon; the colon is removed.’
);
dbms_output.put_line(‘Checked back in module ‘||itm.”module”);
end if;
l_checked_out_by_me := false;
— verify whether module is currently checked out in this workarea
— if not, attempt to check it out
— note: if we check it out, also check it back in at the end of the processing
if jr_version.checked_out_in_workarea( i_irid => itm.mde_id) = 0
then
l_ivid:= jr_version.CHECK_OUT
( i_irid => itm.mde_id
, i_ivid=> itm.mde_ivid
, I_LOCK => false
, I_NOTES => ‘Checked out in order to update the prompt property for all items that contain a colon; the colon is removed.’
);
l_checked_out_by_me := true;
dbms_output.put_line(‘Checked out module ‘||itm.”module”);
end if;
end if; — start of new new module
if itm.item_type =’DBI’
then
l_dbi.v.prompt:= substr(itm.prompt, 1, length(itm.prompt)-1);
dbms_output.put_line(‘- updated prompt: ‘||itm.prompt ||’ to ‘||l_dbi.v.prompt);
ciodata_bound_item.upd(itm.itm_id, l_dbi);
elsif itm.item_type =’UBI’
then
l_ubi.v.prompt:= substr(itm.prompt, 1, length(itm.prompt)-1);
dbms_output.put_line(‘- updated UBI prompt: ‘||l_ubi.v.prompt);
ciounbound_item.upd(itm.itm_id, l_ubi);
end if;

end loop; — itm
if l_mde_id > 0 and l_checked_out_by_me
then
l_ivid:= jr_version.CHECK_IN
( i_irid => l_mde_id
, i_ivid=> l_ivid
, i_vlabel => jr_version.get_next_vlabel(l_mde_id, i_ivid=> l_ivid)
, I_NOTES => ‘Checked out in order to update the prompt property for all items that contain a colon; the colon is removed.’
);
dbms_output.put_line(‘Checked back in module.’);
end if;

— now that you have done your thing, validate and commit
— Validate the activity
cdapi.validate_activity (act_status, act_warnings);
— Display all violations and other messages regardless of the activity
— warnings flag
instantiate_messages;

— Attempt to close the activity
cdapi.close_activity (act_status);

— If the activity did not close successfully, roll back all changes made
— during the activity
if act_status != ‘Y’
then
cdapi.abort_activity;
dbms_output.put_line (‘Activity aborted with constraint violations’);
— Otherwise, we’re done
else
dbms_output.put_line (‘Activity closed successfully’);
end if;

EXCEPTION
WHEN OTHERS
THEN
handle_exception;
raise;

end; — update_prompts

procedure instantiate_messages is
m_facility VARCHAR2 (3);
m_code NUMBER;
arg1 VARCHAR2 (240);
arg2 VARCHAR2 (64);
arg3 VARCHAR2 (64);
arg4 VARCHAR2 (64);
arg5 VARCHAR2 (20);
arg6 VARCHAR2 (20);
arg7 VARCHAR2 (20);
arg8 VARCHAR2 (20);

begin
— Report all violations regardless of the activity status

for viol in (select * from CI_VIOLATIONS) loop
dbms_output.put_line (cdapi.instantiate_message (
viol.facility, viol.code,
viol.p0, viol.p1, viol.p2, viol.p3, viol.p4, viol.p5, viol.p6, viol.p7));
end loop;

— Pop messages off the stack and format them into a single text string

while cdapi.stacksize > 0 loop
rmmes.pop (m_facility, m_code, arg1, arg2, arg3, arg4, arg5, arg6, arg7, arg8
);
dbms_output.put_line (cdapi.instantiate_message (m_facility, m_code,
arg1, arg2, arg3, arg4, arg5, arg6, arg7, arg8));
end loop;
end;

procedure handle_exception
is
begin
— If any messages have been posted on the stack, then print them now
— and then roll back all changes made during the activity

if cdapi.stacksize > 0 then

— Print all messages on the API stack

while cdapi.stacksize > 0 loop
dbms_output.put_line (cdapi.pop_instantiated_message);
end loop;

if cdapi.activity is not NULL then
cdapi.abort_activity;
dbms_output.put_lin

e (‘Activity aborted with API errors’);

else
dbms_output.put_line (‘API Session aborted with API errors’);
end if;

— Otherwise, this must have been an ORACLE SQL or internal error so
— roll back all changes made during the activity and re-raise the
— exception

else

if cdapi.activity is not NULL then
cdapi.abort_activity;
dbms_output.put_line (‘Activity aborted with ORACLE internal errors’);
else
dbms_output.put_line (‘API Session aborted with ORACLE internal errors’);
end if;

end if;
end; — handle_exception

end prompt_mgt;
/
for the body.