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;
/ |