rem rem $Header: L:\\\\model\\repman40\\api\\RCS\\cicol.hpb 1.4 1998/10/12 15:04:48 cheaps Exp $ rem Rem Copyright (c) 1994 by Oracle Corporation Rem NAME Rem cicol.hpb - Rem DESCRIPTION Rem Rem RETURNS Rem Rem NOTES Rem Rem MODIFIED (MM/DD/YY) Rem stlyon 25/07/03 - B1740625 : Rem stlyon 22/01/03 - B2670691 : Rem jwetherb 10/08/94 - Creation REM *************************************************************************** REM Hand-carved package body for COLUMN created on 05-OCT-94 REM *************************************************************************** create or replace PACKAGE BODY cihcol IS -- Generic post-process routines PROCEDURE check_delete(id number); sac_type constant varchar2(6) := 'COL'; --======================== PRE-PROCESS (INS,UPD) ============================-- PROCEDURE pre_process( operation varchar2, id number, pl in out NOCOPY ciocolumn.data ) IS pac_reference number; oracle_sub_type varchar2(6); -- The ORT_TYPE for a referenced object_type (referenced or embedded). pac_type varchar2(3); table_type varchar2(6); -- The TABLE_TYPE for a referenced object_type . cursor get_column_props (column_id number) is select domain_reference, -- domain datatype, -- scalar.datatype average_length, -- scalar.average length maximum_length, -- scalar.maximum length decimal_places, -- scalar.decimal places oracle_type_reference, -- object.oracle type object_type_inclusion, -- object.object type inclusion scope_table_reference, -- object.scope table store_ref_with_rowid_flag, -- object.store ref with rowid? null_indicator, -- definition.optional? uppercase, -- definition.uppercase? default_value, -- definition.default_value default_value_type, -- definition.default_value_type --B1382297 sequence_reference, -- definition.sequence display_flag, -- display.display? default_implementation_item, -- display.template/library object default_display_type, -- display.display type justification, -- display.alignment display_length, -- display.display length display_height, -- display.display height display_sequence, -- display.display sequence descriptor_column, -- display.descriptor sequence format_modifier, -- display.format mask highlighting, -- display.formatting prompt, -- display.prompt order_sequence, -- ordering.order by sequence sorting_order, -- ordering.sort order help_text, -- help.hint remark -- help.comment --table_type from ci_columns col where col.id = column_id; colp get_column_props%rowtype; BEGIN pac_reference := nvl(pl.v.oracle_object_type_reference, pl.v.table_reference); -- B1740625 : Set pac_type for INS and UPD as pac_type is referenced outside INS only branch --if operation = 'INS' then -- if pl.i.oracle_object_type_reference then -- B1740625 : only set if value has changed if (pl.v.oracle_object_type_reference is not null) then pac_type := 'OOT'; else pac_type := 'TAB'; end if; --end if; -- Access rights for column derive from the table -- Access rights checking performed by triggers in config 4.0.9 -- ciiacc.check_access_rights(pac_reference, pac_type, 'UPD', 'SHR'); -- Validate user-defined properties if cieval.is_extended then cieval.check_element(id,pl.v.types,(operation='UPD') ,pl.v.user_defined_property_0,pl.i.user_defined_property_0 ,pl.v.user_defined_property_1,pl.i.user_defined_property_1 ,pl.v.user_defined_property_2,pl.i.user_defined_property_2 ,pl.v.user_defined_property_3,pl.i.user_defined_property_3 ,pl.v.user_defined_property_4,pl.i.user_defined_property_4 ,pl.v.user_defined_property_5,pl.i.user_defined_property_5 ,pl.v.user_defined_property_6,pl.i.user_defined_property_6 ,pl.v.user_defined_property_7,pl.i.user_defined_property_7 ,pl.v.user_defined_property_8,pl.i.user_defined_property_8 ,pl.v.user_defined_property_9,pl.i.user_defined_property_9 ,pl.v.user_defined_property_10,pl.i.user_defined_property_10 ,pl.v.user_defined_property_11,pl.i.user_defined_property_11 ,pl.v.user_defined_property_12,pl.i.user_defined_property_12 ,pl.v.user_defined_property_13,pl.i.user_defined_property_13 ,pl.v.user_defined_property_14,pl.i.user_defined_property_14 ,pl.v.user_defined_property_15,pl.i.user_defined_property_15 ,pl.v.user_defined_property_16,pl.i.user_defined_property_16 ,pl.v.user_defined_property_17,pl.i.user_defined_property_17 ,pl.v.user_defined_property_18,pl.i.user_defined_property_18 ,pl.v.user_defined_property_19,pl.i.user_defined_property_19 ); end if; -- Initialize unassigned properties with default values on INSERT if operation = 'INS' then pl.v.element_type_name := sac_type; pl.i.element_type_name := true; pl.v.column_for := pac_type; pl.i.column_for := true; if pl.v.oracle_type_reference is not null then pl.v.datatype := null; -- overwrite model based default value end if; if pl.v.default_display_type in ('TIME','EDATE','JDATE') and (pl.i.default_display_type or not pl.i.display_length) then pl.v.display_length := 8; pl.i.display_length := true; elsif pl.v.default_display_type = 'DATE' and (pl.i.default_display_type or not pl.i.display_length) then pl.v.display_length := 11; pl.i.display_length := true; end if; if pl.i.prompt = false then pl.v.prompt := initcap(translate(pl.v.name, '_',' ')); pl.i.prompt := true; end if; if upper(pl.v.datatype) = 'VARCHAR2' and pl.i.maximum_length = false then -- B2670691 : Add upper() if pl.v.maximum_length is null then -- B1672934, B1703391, set if not already set pl.v.maximum_length := 240; end if; pl.i.maximum_length := true; end if; end if; ciiutl.adjust_datatype_sizes( pl.v.id, 'COL' , pl.v.datatype, pl.v.maximum_length, pl.v.decimal_places, 'DATATYPE', -- B2670691 : DATA_TYPE -> DATATYPE pl.v.average_length ); if pl.i.name then -- replace spaces with underscores cihtbl.remove_spaces(pl.v.name); --B631806 end if; if pac_type = 'TAB' then select table_type into pl.v.relation_type from ci_relation_definitions where id = pac_reference; else pl.v.relation_type := 'OOT'; end if; pl.i.relation_type := true; -- Make sure seq is unique for Views and Snapshots #1216416 if operation = 'INS' and pl.v.relation_type in ('VIEW', 'SNAP') then -- I'm mis-using is_seq_unique() here. It assumes it will be running after -- the row is inserted, but I'm calling it before it has, so I reverse the logic. while cihcol.is_seq_unique(pl.v.sequence_number, pl.v.table_reference) loop pl.v.sequence_number := nvl(pl.v.sequence_number, 0) +1; end loop; pl.i.sequence_number := true; end if; -- B467799 Default alias, but not the whole story if operation = 'INS' and pl.v.relation_type = 'VIEW' and pl.i.relation_selection_reference = false and pl.i.base_column_reference and not cdapi.load_mode then -- B637926 select min(vwtab.id) into pl.v.relation_selection_reference from ci_relation_selections vwtab, ci_columns col2 where view_reference = pl.v.table_reference and col2.table_reference = vwtab.table_reference and col2.id = pl.v.base_column_reference; pl.i.relation_selection_reference := true; end if; -- If COL references an oracle_collection_type, then it should -- be embedded, rather than referenced. (USBug 569297) -- Should only perform test if oracle_type_reference is not null(Bugs 609455,609459) if pl.i.oracle_type_reference and pl.v.oracle_type_reference is not null and (NVL(pl.v.object_type_inclusion,'NULL') != 'EMBED') then select ort_type into oracle_sub_type from ci_oracle_types ort where ort.id = pl.v.oracle_type_reference; if oracle_sub_type = 'OCT' then pl.v.object_type_inclusion := 'EMBED'; pl.i.object_type_inclusion := true; end if; end if; -- If COL is based on a domain, and any of the properties defined by a domain are changed, then -- set domain_reference to null. i.e., column no longer based on domain (B418510) -- Added qualifier "operation = 'UPD'" is response to B623052 /* Code dropped after rumpus over B623052 - now we do nothing to maintain connection between Cols and Doms if operation = 'UPD' and pl.v.domain_reference is not null and ( pl.i.average_length or pl.i.datatype or pl.i.decimal_places or pl.i.default_value or pl.i.maximum_length or pl.i.soft_lov ) then pl.v.domain_reference := null; pl.i.domain_reference := true; end if; */ -- 04Feb98 CvE 510491 et al Removed great chunk of code supporting MLT for Views -- Default view column null indicator from base column (USBug 554451) /** b710124, b374133, b393161, b693026, b512333, b404830 **/ /** jkc 15sep98 - cascading properties when view or snapshot **/ if operation = 'INS' and pac_type = 'TAB' and (pl.v.relation_type = 'VIEW' or pl.v.relation_type = 'SNAP') then open get_column_props(pl.v.base_column_reference); fetch get_column_props into colp; close get_column_props; if pl.v.domain_reference is null then pl.v.domain_reference := colp.domain_reference; pl.i.domain_reference := true; end if; if pl.v.datatype is null then pl.v.datatype := colp.datatype; pl.i.datatype := true; end if; if pl.v.average_length is null then pl.v.average_length := colp.average_length; pl.i.average_length := true; end if; if pl.v.maximum_length is null then pl.v.maximum_length := colp.maximum_length; pl.i.maximum_length := true; end if; if pl.v.decimal_places is null then pl.v.decimal_places := colp.decimal_places; pl.i.decimal_places := true; end if; if pl.v.oracle_type_reference is null then pl.v.oracle_type_reference := colp.oracle_type_reference; pl.i.oracle_type_reference := true; end if; if pl.v.object_type_inclusion is null then pl.v.object_type_inclusion := colp.object_type_inclusion; pl.i.object_type_inclusion := true; end if; if pl.v.scope_table_reference is null then pl.v.scope_table_reference := colp.scope_table_reference; pl.i.scope_table_reference := true; end if; if pl.v.store_ref_with_rowid_flag is null then pl.v.store_ref_with_rowid_flag := colp.store_ref_with_rowid_flag; pl.i.store_ref_with_rowid_flag := true; end if; if pl.v.null_indicator is null then pl.v.null_indicator := colp.null_indicator; pl.i.null_indicator := true; end if; if pl.v.uppercase is null then pl.v.uppercase := colp.uppercase; pl.i.uppercase := true; end if; if pl.v.default_value is null then pl.v.default_value := colp.default_value; pl.i.default_value := true; /**** Bug 1382297 :: keep type in step with value ****/ if colp.default_value_type is not null -- check fetch returned a row then pl.v.default_value_type := colp.default_value_type; pl.i.default_value_type := true; end if; end if; if pl.v.sequence_reference is null then pl.v.sequence_reference := colp.sequence_reference; pl.i.sequence_reference := true; end if; if pl.v.display_flag is null then pl.v.display_flag := colp.display_flag; pl.i.display_flag := true; end if; if pl.v.default_implementation_item is null then pl.v.default_implementation_item := colp.default_implementation_item; pl.i.default_implementation_item := true; end if; if pl.v.default_display_type is null then pl.v.default_display_type := colp.default_display_type; pl.i.default_display_type := true; end if; if pl.v.justification is null then pl.v.justification := colp.justification; pl.i.justification := true; end if; if pl.v.display_length is null then pl.v.display_length := colp.display_length; pl.i.display_length := true; end if; if pl.v.display_height is null then pl.v.display_height := colp.display_height; pl.i.display_height := true; end if; if pl.v.display_sequence is null then pl.v.display_sequence := colp.display_sequence; pl.i.display_sequence := true; end if; if pl.v.descriptor_column is null then pl.v.descriptor_column := colp.descriptor_column; pl.i.descriptor_column := true; end if; if pl.v.format_modifier is null then pl.v.format_modifier := colp.format_modifier; pl.i.format_modifier := true; end if; if pl.v.highlighting is null then pl.v.highlighting := colp.highlighting; pl.i.highlighting := true; end if; if pl.v.prompt is null then pl.v.prompt := colp.prompt; pl.i.prompt := true; end if; if pl.v.order_sequence is null then pl.v.order_sequence := colp.order_sequence; pl.i.order_sequence := true; end if; if pl.v.sorting_order is null then pl.v.sorting_order := colp.sorting_order; pl.i.sorting_order := true; end if; if pl.v.help_text is null then pl.v.help_text := colp.help_text; pl.i.help_text := true; end if; if pl.v.remark is null then pl.v.remark := colp.remark; pl.i.remark := true; end if; end if; /** jkc end b710124, b374133, b393161, b693026, b512333, b404830 **/ -- Assign additional defaults if pl.v.null_indicator = 'NOT NULL' and pl.i.null_indicator then pl.v.final_volume := 100; pl.v.initial_volume := 100; pl.i.final_volume := true; pl.i.initial_volume := true; end if; --Default display width to value of maximum length if specified if operation in ('UPD','INS') then if pl.i.maximum_length and pl.i.display_length = false then pl.v.display_length := pl.v.maximum_length; end if; end if; /** b567996 jkc 10sep98 **/ if (operation = 'UPD') then if (pl.i.datatype = true) and (pl.v.datatype is not null) then pl.v.oracle_type_reference := null; pl.i.oracle_type_reference := true; pl.v.object_type_inclusion := null; pl.i.object_type_inclusion := true; pl.v.scope_table_reference := null; pl.i.scope_table_reference := true; elsif (pl.i.oracle_type_reference = true) and (pl.v.oracle_type_reference is not null) then pl.v.datatype := null; pl.i.datatype := true; pl.v.maximum_length := null; pl.i.maximum_length := true; pl.v.decimal_places := null; pl.i.decimal_places := true; -- elsif (pl.v.oracle_type_reference is not null and pl.i.oracle_type_reference = false) and -- (pl.v.object_type_inclusion is null and pl.i.object_type_inclusion = true) -- then -- /** b656912 jkc 17sep98 - default object type inclusion to embed when user set to null **/ -- pl.v.object_type_inclusion := 'EMBED'; end if; end if; -- B1272781 - DE DOES NOT SET BASE RELATION PROPERTY FOR THE VIEW COLUMN -- Proviously only set this for insert. Oddly the relation_type at update -- time is 'OOT' as opposed to being 'VIEW' as it is on insert. if operation = 'UPD' and pl.v.relation_type = 'OOT' and pl.i.relation_selection_reference = false and pl.i.base_column_reference and not cdapi.load_mode then -- B637926 select table_type into table_type from ci_relation_definitions tabs where tabs.id = pl.v.table_reference; if table_type = 'VIEW' then select min(vwtab.id) into pl.v.relation_selection_reference from ci_relation_selections vwtab, ci_columns col2 where view_reference = pl.v.table_reference and col2.table_reference = vwtab.table_reference and col2.id = pl.v.base_column_reference; pl.i.relation_selection_reference := true; end if; end if; END pre_process; -- (INS,UPD) --======================== PRE-PROCESS (DEL,SEL) ============================-- PROCEDURE pre_process( operation varchar2, id number ) IS BEGIN -- Access rights for column derive from the table -- Access rights checking performed by triggers in config 4.0.9 -- ciiacc.check_access_rights(id,sac_type,operation,'SAC'); -- if operation is 'DELETE', make sure no non-cascading references exist if operation = 'DEL' then check_delete(id); -- 04Feb98 CvE 510491 et al Removed great chunk of code supporting MLT for Views end if; END; --===================== POST-PROCESS (INS,UPD,SEL) ==========================-- PROCEDURE post_process(operation varchar2,id number, pl ciocolumn.data) IS is_object number; oracle_obj_type_ref number; select_text_type varchar2(1); -- Owning TABs select text type (Y/N) ccd_pl ciocluster_column.data; -- Associated Cluster column save_csynch boolean; BEGIN if operation = 'SEL' then return; end if; /** b658998, b656171 jkc 16sep98 **/ if (pl.v.oracle_object_type_reference is not null) then if upper(pl.v.datatype) in ('NCHAR','NCLOB') -- B2670691: Add upper() then ciierr.fatal(2004,ciiutl.identify(pl.v.id, pl.v.types),pl.v.datatype); end if; end if; if operation = 'INS' then -- Find the oracle_object_type_ref of the parent table or view of this column select (max(tab.oracle_object_type_ref)) into oracle_obj_type_ref from sdd_col col, sdd_tab tab where col.irid = pl.v.id and tab.irid = col.table_ref; -- If the parent table or view is an object table or view then -- don't allow column to be added if oracle_obj_type_ref is not null then ciierr.fatal(1195,ciiutl.identify(pl.v.id, pl.v.types)); end if; end if; if pl.i.name then -- check name is a valid ORACLE name if cihtbl.is_name_valid(pl.v.name) then ciierr.fatal(1003,ciiutl.identify(pl.v.id, pl.v.types),pl.v.name); end if; end if; -- Update attribute_values if uppercase = 'Y' if pl.i.uppercase and pl.v.uppercase = 'Y' then if not cdapi.load_mode then update ci_attribute_values set low_value = nls_upper(low_value), high_value = nls_upper(high_value), abbreviation = nls_upper(abbreviation) where column_reference = post_process.id; end if; end if; -- 04Feb98 CvE 510491 et al Removed great chunk of code supporting MLT for Views -- Code to update cluster columns if not cdapi.load_mode then if pl.i.cluster_column_reference and pl.v.cluster_column_reference is not null then select datatype, length, scale into ccd_pl.v.datatype ,ccd_pl.v.length ,ccd_pl.v.scale from ci_cluster_columns where id = pl.v.cluster_column_reference; if ccd_pl.v.datatype is null and ccd_pl.v.length is null and ccd_pl.v.scale is null then ccd_pl.v.datatype := pl.v.datatype; ccd_pl.v.length := pl.v.maximum_length; ccd_pl.v.scale := pl.v.decimal_places; ccd_pl.i.datatype := true; ccd_pl.i.length := true; ccd_pl.i.scale := true; begin save_csynch := rm.do_csynch; rm.do_csynch := true; ciocluster_column.upd(pl.v.cluster_column_reference, ccd_pl); rm.do_csynch := save_csynch; exception -- In case upd call fails, must not be left in system mode when others then rm.do_csynch := save_csynch; raise; end; end if; end if; end if; END post_process; -- (INS,UPD,SEL) --========================= POST PROCESS (DEL) ==============================-- PROCEDURE post_process(operation varchar2,id number) IS BEGIN if operation = 'DEL' then -- Delete the column and all it's related sub-bits ciiutl.delete_element(id,sac_type); -- Delete foreign key constraints which are referenced via EL_NJ_ID delete SDD_OKY where foreign_column_ref = post_process.id; end if; END; --============================= CHECK DELETE ================================-- PROCEDURE check_delete(id number) IS dummy number; error_count number := 0; BEGIN null; -- 4.0.12 Removed all simple delete checking -- Check if any MCIs are connected to me -- Check if any ITMs are connected to me -- Check if any TRGCOLs are connected to me -- Check if any TABENTs are connected to me -- Check if any OKYs are connected to me -- Check if any other COLs are connected to me due to view columns or -- denormailsed columns END; --============================= IS SEQ UNIQUE ===============================-- FUNCTION is_seq_unique(seq number, table_reference number) RETURN boolean IS dummy number := 0; BEGIN select count(*) into dummy from ci_columns c where c.table_reference = is_seq_unique.table_reference and c.sequence_number = is_seq_unique.seq; if dummy = 1 then return true; else return false; end if; END; --================================== END ====================================-- -- -- Package instantiation block -- BEGIN is_installed := true; END; /