Oracle Designer 2.1.2. API


Fast Display Setting for Columns with Allowable Values

By Hervé Deschamps.




In every system there are a number of columns (table columns or view columns) that have a restricted number of allowable values. Example: project_type, country_code, indicator. When the number of values for one column is small (ten or less), it is common to present the values to the user with a POP-LIST, RADIO BUTTON or CHECK BOX. However, by default Designer sets the display datatype of these columns to TEXT.

This utility looks for all columns based on a domain with a list of allowable values and with a display datatype set to TEXT. It then replaces TEXT with POP-LIST.
Et voila! No need for time consuming tedious and prone-to-error manual work.

Note: I have left out allowable values defined at the column level directly. I only consider those defined in a domain. Those who do not use domains systematically will sweat and they deserve it.
 
 

set verify off
set serveroutput on

prompt 
prompt 
prompt 
prompt * *****************************************************
prompt * 
prompt * This utility sets the display of all columns based on 
prompt * a domain with allowable values to 'POP-LIST', if their 
prompt * is set to 'TEXT'.
prompt *
prompt * This has been programmed for Designer 2.1.2.
prompt * 
rem    * 
rem    * @e:\hdd\perso\utilities\upd_disp_col_domains.sql
rem    * 
rem    * Written by: H. Deschamps, 2/03/99.
prompt * ******************************************************
prompt 
prompt 

accept app_name char prompt 'Application name: '
accept app_version number prompt 'Application version: '

declare
  col ciocolumn.data; 
  act_status varchar2(1);     -- Activity status
  act_warnings varchar2(1);   -- Activity warning flag

  v_app_id number(38);
  cursor get_application_id (p_app_name  varchar2,
                             p_app_version  number) is
     select id
     from ci_application_systems
     where name = p_app_name and
           version = p_app_version;
 

  unknown_application_system exception;
  pragma exception_init(unknown_application_system, -20103);

  cursor candidate_cols (p_app_id number) is
    select rel.name rel_name, 
           col.id   col_id,
           col.name col_name
    from ci_relation_definitions rel,
         ci_columns col
    where rel.application_system_owned_by = p_app_id and
          col.table_reference = rel.id and
          col.default_display_type = 'TEXT' and
          exists (select null
                  from ci_attribute_values alv
                  where alv.domain_reference = col.domain_reference)
    order by rel.name, col.name;

  cur_candidate_col candidate_cols%rowtype;
 
 

  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;

begin

  -- Get Application ID
  open get_application_id(upper('&app_name'),&app_version);
  fetch get_application_id into v_app_id;
  if get_application_id%notfound then
    raise_application_error(-20103,
         'Sorry, the application that you have entered is unknown.');
  end if;
  close get_application_id;
 

  -- Initialize API if not already done
  if cdapi.initialized = false then
    -- Initialize the API globals
    cdapi.initialize(upper('&app_name'), &app_version);
  end if;

  -- Set DBMS Output Buffer to Max Size
  dbms_output.enable(1000000);

  for cur_candidate_col in candidate_cols(v_app_id) loop
    cdapi.open_activity;
 
    -- next char domain
    ciocolumn.sel(cur_candidate_col.col_id , col);

    -- Set the display type
    col.v.default_display_type := 'POP-LIST';
    col.i.default_display_type := TRUE;
 
    -- Update the column
    ciocolumn.upd(cur_candidate_col.col_id, col);

    -- Validate the update
    cdapi.validate_activity(act_status,act_warnings);

    -- Get feedback
    instantiate_messages;

    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');
    else
      dbms_output.put_line(cur_candidate_col.rel_name||', '||cur_candidate_col.col_name);
    end if;
  end loop;
exception
  when unknown_application_system then
    dbms_output.put_line('Sorry, the application that you have entered is unknown.');
    cdapi.abort_activity;
  when others then
    -- 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_line('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;
      raise;
    end if;
END;
/


 
 

Please send me comments or suggestions:  herve@iherve.com
 Take a look at my HomePage