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

1

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.

Share.

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director for Fusion Middleware. Consultant, trainer and instructor on diverse areas including Oracle Database (SQL & PLSQL), Service Oriented Architecture, BPM, ADF, Java in various shapes and forms and many other things. Author of the Oracle Press book: Oracle SOA Suite 11g Handbook. Frequent presenter on conferences such as JavaOne, Oracle OpenWorld, ODTUG Kaleidoscope, Devoxx and OBUG. Presenter for Oracle University Celebrity specials.

1 Comment

  1. Hello,

    I’ve been throught a similar case, where i have to update the prompts for all the columns to be in Arabic rather than in English since we are developing an Arabic application.
    The Arabic prompts were input as description for each entity and so were transformed as help or notes by the Design Trasnformer. The prompts, as usual, were derived from the columns names.

    This task covers around 312 columns and so I decided to automate this task as it’s really unappealing. As I’m not an expert in Oracle Repository, I’ve gone through some Oracle reports in order to be able to build the update statement. I’ve decided to read the description from the attributes and place them as columns prompts. I’ve reached to the following:

    update ci_columns Y
    set prompt =
    (select SUBSTR(f.txt_text,1,131)
    from CI_folder_members a, sdd_folders b, ci_entities c, ci_attributes d, CDI_TEXT f, ci_columns col
    where a.folder_reference = b.IRID
    and b.name = ‘Folder Name’ — folder name for our project
    and a.MEMBER_OBJECT = c.ID
    and d.ENTITY_REFERENCE = c.ID
    and f.txt_ref = d.ID
    and col.SOURCE_ATTRIBUTE_REFERENCE = d.id
    and col.ID = Y.ID)
    WHERE Y.ID IN (SELECT COL1.ID
    FROM CI_folder_members a1,
    sdd_folders b1,
    ci_entities c1,
    ci_attributes d1,
    ci_columns col1
    WHERE a1.folder_reference = b1.IRID
    and b1.name = ‘Folder Name’ — folder name for our project
    and a1.MEMBER_OBJECT = c1.ID
    and d1.ENTITY_REFERENCE = c1.ID
    and col1.SOURCE_ATTRIBUTE_REFERENCE = d1.id);

    Still i haven’t run the statement, and I’m not sure if it has any drawbacks or if any other object should be updated too.

    what do u think? Do u support the F9/Commit on this query????

    Cheers,
    Amani