Designer 2000 API


Replacing CHAR with VARCHAR2

By Hervé Deschamps.


This is a very handy utility to take care of all instances of format CHAR. It simply changes the format to VARCHAR2.

Under most circumstances, it is wise to use varchar2 instead of char. The object of this article is not to get into a debate about it. If you have a large repository full of CHAR things, you are welcome to use the utilities that follow. There is one for domains, one for attributes and one for columns.

You do not necessarily have to run the utility for attributes. The database design wizard takes care of this for you, i.e. all CHAR attributes will give birth to VARCHAR2 columns. There is one exception though. An attributes defined by a domain whose column format is CHAR results in a CHAR column.

The code that follows can simply be run from SQL*Plus.

 Domains SQL*Plus Script
 Attributes SQL*Plus Script
 Columns SQL*Plus Script

set serveroutput on 
set verify off

rem ******************************************************
rem * 
rem * This utility replaces CHAR with VARCHAR2 in all domains
rem * of an application in Designer 2000.
rem *
rem * Written by: H. Deschamps, 7/3/97.
rem * Last Modified: 7/3/97.
rem ******************************************************

declare
  dom ciodomain.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 char_domains (p_app_id number) is
    select dom.id dom_id,
           dom.name dom_name
    from   ci_domains dom
    where  dom.application_system_owned_by = p_app_id and
           (dom.format = 'CHAR' or
            dom.datatype = 'CHAR');
  cur_char_domain char_domains%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_char_domain in char_domains(v_app_id) loop
    cdapi.open_activity;
    
    -- next char domain
    ciodomain.sel(cur_char_domain.dom_id ,dom);

    -- Change it to varchar2
    dom.v.format := 'VARCHAR2';
    dom.i.format := TRUE;
    dom.v.datatype := 'VARCHAR2';
    dom.i.datatype := TRUE;
    
    -- Update the domain
    ciodomain.upd(cur_char_domain.dom_id, dom);

    -- 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_char_domain.dom_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;
/

set serveroutput on
set verify off
rem ******************************************************
rem * 
rem * This utility replaces CHAR with VARCHAR2 in all attributes
rem * of an application in Designer 2000.
rem *
rem * Written by: H. Deschamps, 7/1/97.
rem * Last Modified: 7/1/97.
rem ******************************************************

declare
  att cioattribute.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 char_attributes (p_app_id number) is
    select att.id att_id,
           att.name att_name,
           ent.name ent_name
    from   ci_attributes att, 
           ci_entities ent
    where  ent.application_system_owned_by = p_app_id and
           ent.id = att.entity_reference and
           att.format = 'CHAR';
  cur_char_attribute char_attributes%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_char_attribute in char_attributes(v_app_id) loop
    cdapi.open_activity;
    
    -- next char attribute
    cioattribute.sel(cur_char_attribute.att_id ,att);

    -- Change it to varchar2
    att.v.format := 'VARCHAR2';
    att.i.format := TRUE;
    
    -- Update the attribute
    cioattribute.upd(cur_char_attribute.att_id, att);

    -- 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_char_attribute.ent_name||'.'||
                           cur_char_attribute.att_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;
/
 

 

set serveroutput on
set verify off
rem ******************************************************
rem * 
rem * This utility replaces CHAR with VARCHAR2 in all columns
rem * of an application in Designer 2000.
rem *
rem * Written by: H. Deschamps, 7/3/97.
rem * Last Modified: 7/3/97.
rem ******************************************************

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 char_columns (p_app_id number) is
    select tab.name tab_name, 
           col.id col_id,
           col.name col_name
    from   ci_columns col,
           ci_table_definitions tab
    where  col.table_reference = tab.id and
           tab.application_system_owned_by = p_app_id and
           col.datatype = 'CHAR';
  cur_char_column char_columns%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_char_column in char_columns(v_app_id) loop
    cdapi.open_activity;
    
    -- next char column
    ciocolumn.sel(cur_char_column.col_id ,col);

    -- Change it to varchar2
    col.v.datatype := 'VARCHAR2';
    col.i.datatype := TRUE;
    
    -- Update the column
    ciocolumn.upd(cur_char_column.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_char_column.tab_name||'.'||
                           cur_char_column.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