Designer 2000 API

Providing View Column Display Properties

By Hervé Deschamps.

There are several ways to define views in Designer 2000. You may create them using the Repository Object Navigator. You may choose the Data Schema Diagrammer. Or you may choose to create the view in the RDBMS first and then reverse engineer it.

Whichever way you choose, you may expect that Designer 2000 would default the following view column properties:

Why? Because most of the views’ columns are based on table’s columns. You have already defined that display stuff against the table columns, right? Why should you do it again if you want to use the view in a form?

Well, the bad news is that Designer 2000 can’t do it (up to 1.3.x at least). Most of you have spent some time defining it manually *again*. The good new is that we’ll show you an API script that can.

You can run this code from Sql*Plus without creating any object in the database.

set serveroutput on
set verify off
rem ******************************************************
rem * 
rem * This utility completes any missing display information
rem * against view columns with their corresponding table
rem * column.
rem *
rem * It can handle tables based on several tables. However
rem * the view column name must have the same name as its
rem * corresponding column.
rem *
rem * Written by: H. Deschamps, 7/16/97.
rem * Last Modified: 7/16/97.
rem ******************************************************

  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 disp_vwcolumns (p_app_id number) is
    select                     vw_name,
           tabcol.display_flag         tabcol_display_flag,
           tabcol.display_datatype     tabcol_display_datatype,
           tabcol.justification        tabcol_justification,
           tabcol.display_length       tabcol_display_length,
           tabcol.display_height       tabcol_display_height,
           tabcol.display_sequence     tabcol_display_sequence,
           tabcol.format_modifier      tabcol_format_modifier,
           tabcol.highlighting         tabcol_highlighting,
           tabcol.prompt               tabcol_prompt,
           tabcol.help_text            tabcol_help_text,
           tabcol.order_sequence       tabcol_order_sequence,
           tabcol.sorting_order        tabcol_sorting_order,
           tabcol.auto_generated       tabcol_auto_generated,
           tabcol.remark               tabcol_remark,
           tabcol.suggestion_list_flag tabcol_suggestion_list_flag,
           tabcol.domain_reference     tabcol_domain_reference,
           tabcol.uppercase            tabcol_uppercase,
           tabcol.default_value        tabcol_default_value
    from   ci_columns vwcol, 
           ci_view_definitions vw, 
           ci_relation_selections vwtab,
           ci_columns tabcol
    where  vwcol.table_reference =  and
           vw.application_system_owned_by = p_app_id  and 
           vwcol.display_flag is null or
           vwcol.display_datatype is null or
           vwcol.justification is null or
           vwcol.display_length is null or
           vwcol.display_height is null or
           vwcol.display_sequence is null or
           vwcol.format_modifier is null or
           vwcol.highlighting is null or
           vwcol.prompt is null or
           vwcol.help_text is null or
           vwcol.order_sequence is null or
           vwcol.sorting_order is null or
           vwcol.auto_generated is null or
           vwcol.remark is null or
           vwcol.suggestion_list_flag is null or
           vwcol.domain_reference is null or
           vwcol.uppercase is null or
           vwcol.default_value is null
           ) and
  = vwtab.view_reference and
           vwtab.table_reference = tabcol.table_reference and

  cur_disp_vwcolumn disp_vwcolumns%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);
    -- Report all violations regardless of the activity status
    for viol in (select * from ci_violations) loop
      dbms_output.put_line( cdapi.instantiate_message(
         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,
          ( m_facility,m_code,arg1,arg2,arg3,arg4,arg5,arg6,arg7,arg8));
    end loop;


  -- 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
         '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

  for cur_disp_vwcolumn in disp_vwcolumns(v_app_id) loop
    -- next vwcolumn
    ciocolumn.sel(cur_disp_vwcolumn.vwcol_id ,vwcol);

    -- Complete display info when necessary
    if vwcol.v.display_flag is null then
      vwcol.v.display_flag := cur_disp_vwcolumn.tabcol_display_flag;
      vwcol.i.display_flag := TRUE;
    end if;
    if vwcol.v.display_datatype is null then
      vwcol.v.display_datatype := cur_disp_vwcolumn.tabcol_display_datatype;
      vwcol.i.display_datatype := TRUE;
    end if;
    if vwcol.v.justification is null then
      vwcol.v.justification := cur_disp_vwcolumn.tabcol_justification;
      vwcol.i.justification := TRUE;
    end if;
    if vwcol.v.display_length is null then
      vwcol.v.display_length := cur_disp_vwcolumn.tabcol_display_length;
      vwcol.i.display_length := TRUE;
    end if;
    if vwcol.v.display_height is null then
      vwcol.v.display_height := cur_disp_vwcolumn.tabcol_display_height;
      vwcol.i.display_height := TRUE;
    end if;
    if vwcol.v.display_sequence is null then
      vwcol.v.display_sequence := cur_disp_vwcolumn.tabcol_display_sequence;
      vwcol.i.display_sequence := TRUE;
    end if;
    if vwcol.v.format_modifier is null then
      vwcol.v.format_modifier := cur_disp_vwcolumn.tabcol_format_modifier;
      vwcol.i.format_modifier := TRUE;
    end if;
    if vwcol.v.highlighting is null then
      vwcol.v.highlighting := cur_disp_vwcolumn.tabcol_highlighting;
      vwcol.i.highlighting := TRUE;
    end if;
    if vwcol.v.prompt is null then
      vwcol.v.prompt := cur_disp_vwcolumn.tabcol_prompt;
      vwcol.i.prompt := TRUE;
    end if;
    if vwcol.v.help_text is null then
      vwcol.v.help_text := cur_disp_vwcolumn.tabcol_help_text;
      vwcol.i.help_text := TRUE;
    end if;
    if vwcol.v.order_sequence is null then
      vwcol.v.order_sequence := cur_disp_vwcolumn.tabcol_order_sequence;
      vwcol.i.order_sequence := TRUE;
    end if;
    if vwcol.v.sorting_order is null then
      vwcol.v.sorting_order := cur_disp_vwcolumn.tabcol_sorting_order;
      vwcol.i.sorting_order := TRUE;
    end if;
    if vwcol.v.auto_generated is null then
      vwcol.v.auto_generated := cur_disp_vwcolumn.tabcol_auto_generated;
      vwcol.i.auto_generated := TRUE;
    end if;
    if vwcol.v.remark is null then
      vwcol.v.remark := cur_disp_vwcolumn.tabcol_remark;
      vwcol.i.remark := TRUE;
    end if;
    if vwcol.v.suggestion_list_flag is null then
      vwcol.v.suggestion_list_flag  := cur_disp_vwcolumn.tabcol_suggestion_list_flag ;
      vwcol.i.suggestion_list_flag  := TRUE;
    end if;
    if vwcol.v.domain_reference is null then
      vwcol.v.domain_reference := cur_disp_vwcolumn.tabcol_domain_reference;
      vwcol.i.domain_reference := TRUE;
    end if;
    if vwcol.v.uppercase is null then
      vwcol.v.uppercase := cur_disp_vwcolumn.tabcol_uppercase;
      vwcol.i.uppercase := TRUE;
    end if;
    if vwcol.v.default_value is null then
      vwcol.v.default_value := cur_disp_vwcolumn.tabcol_default_value;
      vwcol.i.default_value := TRUE;
    end if;
    -- Update the vwcolumn
    ciocolumn.upd(cur_disp_vwcolumn.vwcol_id, vwcol);

    -- Validate the update

    -- Get feedback


    -- If the activity did not close successfully, roll back
    -- all changes made during the activity
    if act_status != 'Y' then
      dbms_output.put_line('Activity aborted with constraint violations');
    end if;
  end loop;
  when unknown_application_system then
    dbms_output.put_line('Sorry, the application that you have entered is unknown.');
  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
      end loop;
      if cdapi.activity is not null then
        dbms_output.put_line('Activity aborted with API errors');
        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
      if cdapi.activity is not null then
        dbms_output.put_line('Activity aborted with ORACLE internal errors');
        dbms_output.put_line('API Session aborted with ORACLE internal errors');
      end if;
    end if;


Please send me comments or suggestions:

 Take a look at my HomePage