set serveroutput on
set verify off
rem ******************************************************
rem *
rem * This utility creates a domain for all attributes
rem * of an application in Designer 2000.
rem *
rem * For all domainless attributes:
rem * - if a domain with the same name exists,
rem * assign the attribute
to this domain
rem * - if not,
rem * create a domain
with the attribute name,
rem * assign the attribute
to this domain.
rem *
rem * Written by: H. Deschamps, 7/1/97.
rem * Last Modified: 7/1/97.
rem ******************************************************
declare
att cioattribute.data;
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 no_dom_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.domain_reference
is null;
cur_no_dom_attribute no_dom_attributes%rowtype;
cursor matching_domain (p_name varchar2,
p_app_id number) is
select dom.id dom_id,
dom.name dom_name,
dom.format dom_format,
dom.average_attribute_length dom_avg_att_len,
dom.maximum_attribute_length dom_max_att_len,
dom.attribute_precision dom_att_prec
from ci_domains dom
where dom.name = p_name and
dom.application_system_owned_by
= p_app_id;
cur_matching_dom matching_domain%rowtype;
matching_dom_id ci_domains.id%type;
domain_to_create varchar2(40);
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_no_dom_attribute in no_dom_attributes(v_app_id) loop
cdapi.open_activity;
-- next domainless attribute
cioattribute.sel(cur_no_dom_attribute.att_id
,att);
-- Look for a domain with the same name as the
attribute
open matching_domain(att.v.name,v_app_id);
fetch matching_domain into cur_matching_dom;
if matching_domain%found then
-- If there is a domain with the
same name as the attribute,
if cur_matching_dom.dom_format =
att.v.format and
nvl(cur_matching_dom.dom_avg_att_len,0)
= nvl(att.v.average_length,0) and
nvl(cur_matching_dom.dom_max_att_len,0)
= nvl(att.v.maximum_length,0) and
nvl(cur_matching_dom.dom_att_prec,0)
= nvl(att.v.precision,0) then
-- If the datatype
match is perfect, no new domain is
-- required.
domain_to_create
:= null;
matching_dom_id
:= cur_matching_dom.dom_id;
else
-- If the datatype
match is not perfect, we'll create a new
-- domain.
domain_to_create
:= cur_no_dom_attribute.att_name||'_'||
cur_no_dom_attribute.ent_name;
end if;
else
-- If there is no domain with the
same name as
-- the attribute, we'll create a
new domain.
domain_to_create := att.v.name;
end if;
close matching_domain;
if domain_to_create is not null then
dom.v.application_system_owned_by
:= v_app_id;
dom.i.application_system_owned_by
:= TRUE;
dom.v.name := substr(domain_to_create,1,40);
dom.i.name := TRUE;
dom.v.format := att.v.format;
dom.i.format := TRUE;
dom.v.maximum_attribute_length :=
att.v.maximum_length;
dom.i.maximum_attribute_length :=
TRUE;
dom.v.average_attribute_length :=
att.v.average_length;
dom.i.average_attribute_length :=
TRUE;
dom.v.attribute_precision := att.v.precision;
dom.i.attribute_precision := TRUE;
dom.v.description := att.v.notes;
dom.i.description := TRUE;
dom.v.datatype := att.v.format;
dom.i.datatype := TRUE;
dom.v.maximum_column_length := att.v.maximum_length;
dom.i.maximum_column_length := TRUE;
dom.v.average_column_length := att.v.average_length;
dom.i.average_column_length := TRUE;
dom.v.column_precision := att.v.precision;
dom.i.column_precision := TRUE;
ciodomain.ins(null,dom);
matching_dom_id := dom.v.id;
end if;
-- assign the attribute to the domain
att.v.domain_reference := matching_dom_id;
att.i.domain_reference := TRUE;
-- Update the attribute
cioattribute.upd(cur_no_dom_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_no_dom_attribute.ent_name||'.'||
cur_no_dom_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;
/ |