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 ******************************************************
declare
vwcol 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 disp_vwcolumns (p_app_id number) is
select vw.name
vw_name,
vwcol.id
vwcol_id,
vwcol.name
vwcol_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 = vw.id
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
vw.id
= vwtab.view_reference and
vwtab.table_reference
= tabcol.table_reference and
tabcol.name
= vwcol.name;
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);
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_disp_vwcolumn in disp_vwcolumns(v_app_id) loop
cdapi.open_activity;
-- 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
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_disp_vwcolumn.vw_name||'.'||
cur_disp_vwcolumn.vwcol_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;
/ |