CREATE OR REPLACE PACKAGE BODY ckOVWutl AS -- Object VieW utility -- Creates Object view definitions based on existing -- Oracle Object Types which map to relational Table or View -- (TAB) definitions. -- (Related to ckOOTutl utility package) -- It is assumed that the Object Types passed in to this utility -- are mapped to only one relational table or view. -- However Object Types used to construct embedded types may be -- mapped to one or more relational tables or views. It is assumed -- that Object Views will not be created for constructor types. -- Change History: -- 25-Apr-00 : JS fixed us bug 1082440 -- Increased length of variables used to build up trigger -- body -- 22-Nov-99 : JS updated with model changes to TRANSFORMATION_MAPPING_SET -- Also removed cdapi error stack code as it was preventing the -- client side from picking up the errors and presenting them to -- the user in the standard error stack dialog. -- No other utilities have this -- 29-Jun-99 : Fixed bug 919699 Numeric/value error creating unique View, PL/SQL module name. -- A 14 character timestamp replaced use of ID (38). -- Related bug 905886 (in romotext) must also be fixed. -- 9-Jun-99 : Fixed bug 898608 -- 13-May-98 : Fixed bug 652083 Dereferencing Ref attribute in trigger needs ':NEW' -- 7-Apr-98 : Fixed bug 651972 Trigger code 'SELECT Deref' truncated -- 30-Mar-98 : Fixed bug 647898 Missing FROM clause on Make_ref in subquery -- 30-Mar-98 : Fixed bugs 648804, 648812 minor syntax errors -- 28-Mar-98 : Fixed bug 644613 (statement order sequence error in TRIGGER) -- 14-Mar-98 : Fixed bug 638129 (EXECUTE_TIME property set to 'INSTEAD OF') -- 3-Mar-98 : Fixed bugs 632499, 631825, 620363 -- 2-Mar-98 : Fixed bug 631827 -- 28-Feb-98 : Fixed bugs 631821, 607463 -- Replaced outer joins on get_type_att_col_maps -- cursor, with UNION queries -- 28-Jan-98 : Fixed bug 618400 -- 15-Jan-98 : Fixed bug 604062 -- 08-Jan-98 : Fixed bug 604067 -- 15-Dec-97 : Coding INSTEAD OF TRIGGER logic (on INSERT) act_status varchar2(100); act_warnings varchar2(100); line varchar2(240); -- Local defined Type declarations used to create -- INSTEAD OF triggers Type base_tab_typ Is Record ( id Number ,name Varchar2(30) ,alias Varchar2(30) ); Type nested_tab_items_rtyp Is Record ( tab_id Number ,tab_name Varchar2(30) ,fkey_id Number ,att_type Varchar2(30) ,oot_ref Number ,att_id Number ,att_name Varchar2(30) ); Type base_tab_ttyp Is Table of base_tab_typ Index By Binary_Integer; -- us bug 1082440 Type dclr_sctn_text_t Is Table of Varchar2(128) Index By Binary_Integer; Type proc_body_text_t Is Table Of Varchar2(128) Index By Binary_Integer; Type nested_tab_items_ttyp Is Table Of nested_tab_items_rtyp Index By Binary_Integer; -- BEGIN Local Defined Procedure Specifications -- Output diagnostic messages procedure o (text varchar2); Procedure create_FROM_clause ( i_view_id In Number ,i_baserel_id In Number ); -- Return TRUE if there is a FKey between the table mapped -- to nested object and the views base table Function fkey_id_between( i_nested_obj_id In Number ,i_base_tab_id In Number ,o_fkey_id Out Number ,o_nstd_tab_id Out Number ,o_nstd_tab_name Out Varchar2 ) Return Boolean ; Procedure create_SELECT_list( i_view_id In Number ,i_oot_reference In Number ,i_basetab_id In Number ,i_alias In Varchar2 ,io_trg_nstd_itms In Out nested_tab_items_ttyp ,i_rcrsv_lvl In Number ,i_txtstrm In RM.stream ); Procedure create_INSTEAD_OF_triggers ( i_oot_id In Number ,i_view_id In Number ,i_view_name In Varchar2 ,i_base_tab In base_tab_typ ,i_trg_nstd_itms In nested_tab_items_ttyp ); Procedure add_INSTEAD_OF_trigger_code ( i_oot_id In Number ,i_plsql_mod_id In Number ,i_base_tab In base_tab_typ ,i_trg_nstd_itms In nested_tab_items_ttyp ); Procedure assemble_plsql( io_ins_part1 In Out proc_body_text_t ,io_ins_part2 In Out proc_body_text_t ,io_proc_body In Out proc_body_text_t ); -- Add the item to text buffer and flush the buffer -- whenever the maximum length of a line is reached. Procedure add_to( io_txtbuf In Out Varchar2 ,i_item In Varchar2 ,i_txtstream In RM.stream ,i_view_id In Number ); Procedure add_makeref_to( io_select_list In Out Varchar2 ,i_seperator In Varchar2 ,i_oot_ref In Number ,i_fkey_id In Number ,i_alias In Varchar2 ,i_txtstream In RM.stream ,i_view_id In Number); Procedure add_subquery_to( i_fkey_id In Number ,i_att_name In Varchar2 ,i_oot_name In Varchar2 ,i_oot_ref In Number ,i_ref_flag In Varchar2 ,io_trg_nstd_itms In Out nested_tab_items_ttyp ,i_rcrsv_lvl In Number ,i_seperator In Varchar2 ,i_txtstream In RM.stream ,i_view_id In Number); -- END Local Defined Procedure Specifications Procedure create_OVW_4_OOT ( i_OOT_IDs In IDrefs -- Mandatory reference(ID) list of -- OOT's ,i_prfx In Varchar2 Default NULL -- Optional user supplied prefix -- for the View ,io_ovw_NAME In Out OVWnames -- Optional user supplied list of -- names for the Object Views else -- derived ,io_ovw_IDs In Out IDrefs -- Return reference(ID) list of the -- object views created ,i_sufx In Varchar2 Default '_V' -- Optional user supplied suffix -- for the View ) Is utility_name Varchar2(70) := 'Designer/2000 Object View Utility'; proc Varchar2(70) := 'CKOVWUTL.create_ovw_4_oot'; vw cioview_definition.data; col ciocolumn.data; tms ciotransformation_mapping_set.data; map ciomapping_element.data; OOT_reference ci_oracle_object_types.id%type; TAB_reference ci_relation_definitions.id%type; TAB_name ci_relation_definitions.name%type; TAB_alias ci_table_definitions.alias%type; base_tabs base_tab_ttyp; -- to store each Views base relation oot_name ci_oracle_object_types.name%type; constraint_ref ci_unique_key_constraints.id%type; oid_attributes ci_view_definitions.oid_attribute_list%type; ukey_id ci_unique_key_constraints.id%type; prev_ukey_id ci_unique_key_constraints.id%type; conflict_id ci_relation_definitions.id%type; rcrsv_lvl Number := 0; seperator Varchar2(2); -- seperator between items in list (NULL,',') txtstream rm.STREAM; trg_nested_tab_items nested_tab_items_ttyp; Cursor get_TAB_4_OOT_id Is SELECT t.id , t.name , nvl(t.alias, 'm0') FROM ci_mapping_elements m , ci_transformation_mapping_sets s , ci_table_definitions t WHERE m.target_cielement_reference = OOT_reference AND m.transformation_set_reference = s.id AND s.transformation_type = 'REL2OREL' AND m.source_cielement_reference = t.id UNION SELECT t.id , t.name , nvl(t.alias, 'm0') FROM ci_mapping_elements m , ci_transformation_mapping_sets s , ci_view_definitions t WHERE m.target_cielement_reference = OOT_reference AND m.transformation_set_reference = s.id AND s.transformation_type = 'REL2OREL' AND m.source_cielement_reference = t.id ; Cursor get_VW_properties Is SELECT v.id , v.name FROM ci_view_definitions v , ci_mapping_elements m , ci_transformation_mapping_sets s , ci_relation_selections r WHERE v.oracle_object_type_reference = OOT_reference --context TAB AND v.id = r.view_reference AND r.table_reference = m.source_cielement_reference AND m.target_cielement_reference =v.oracle_object_type_reference AND m.transformation_set_reference = s.id AND s.transformation_type = 'REL2OREL' ; -- to clear/delete relation selections when re-executing utility Cursor get_VWTAB_ids(view_id Number) Is SELECT rs.id id FROM ci_relation_selections rs WHERE rs.view_reference = view_id ; Cursor get_OOT_properties Is SELECT t.name FROM ci_oracle_object_types t WHERE t.id = OOT_reference -- context table/view ; -- Get column names of the Pkey for creating an OID attribute list Cursor get_pkey_colname_list Is SELECT c.name colname FROM ci_columns c , ci_key_components k WHERE c.table_reference = TAB_reference -- context table/view AND k.column_reference = c.id AND k.constraint_type = 'PRIMARY' ORDER BY k.sequence_number ; -- Get the first defined Unique Key ID for the given Table CURSOR get_ukey_ID Is SELECT min(k.id) FROM ci_unique_key_constraints k WHERE k.table_reference = TAB_reference -- context table/view ; -- Get column names of a Ukey for creating an OID attribute list. -- This cursor is activated only if table/view does not have a PKey. Cursor get_ukey_colname_list(constraint_ref Number) Is SELECT c.name colname FROM ci_columns c , ci_key_components k WHERE c.table_reference = TAB_reference -- context table/view AND k.column_reference = c.id AND k.constraint_type = 'UNIQUE' AND k.constraint_reference = constraint_ref ORDER BY k.sequence_number ; BEGIN o(proc ||' starting, ' || i_OOT_IDs.Count || ' Object Types to process.' ); ck_util.register (utility_name,'Init'); -- Check API initialized rmmes.clear; if not cdapi.initialized then ciierr.fatal(1); end if; -- Set these variables outside the Loop, as they remain -- constant for all inserts/updates vw.v.OVERRIDE_SELECT_TEXT_FLAG := 'Y'; vw.i.OVERRIDE_SELECT_TEXT_FLAG := true; vw.v.REMARK := 'Created by the Object View Utility'; vw.i.REMARK := true; For loop_idx In 1 .. i_OOT_IDs.Count Loop oot_reference := i_OOT_IDs(loop_idx); -- set context OOT ID -- Get the Table/View (ID) mapped to this OOT o('Get the Table/View mapped to this OOT: ' ||oot_reference); Open get_TAB_4_OOT_id; Fetch get_TAB_4_OOT_id Into TAB_reference, TAB_name, TAB_alias; Close get_TAB_4_OOT_id; -- Find if an Object View already exists for this OOT. -- If so, replace all but the View Name. o('Check if a View aleady exists'); vw.v.ID := NULL; vw.v.NAME := NULL; Open get_VW_properties; Fetch get_VW_properties Into vw.v.ID, vw.v.NAME; If vw.v.ID Is NOT NULL Then Fetch get_VW_properties Into vw.v.ID, vw.v.NAME; End If; If get_VW_properties%Found Then -- More than one view was found, based on this object type. -- Because of ambiguity, this type will be skipped. -- The View Id will be returned NULL as signal to UI. Close get_VW_properties; io_OVW_IDs(loop_idx) := NULL; io_OVW_NAME(loop_idx) := NULL; base_tabs(loop_idx).id := NULL; base_tabs(loop_idx).name := NULL; base_tabs(loop_idx).alias:= NULL; Else Close get_VW_properties; If vw.v.ID Is NOT NULL Then -- Remove existing RELATION SELECTIONS -- and MultiLine text properties o('Clearing RELATION SELECTIONs and TEXT'); For i in get_vwtab_ids(vw.v.ID) Loop ciorelation_selection.del(i.id); End Loop; -- remove existing 'Select List' text in the case of -- rerun/update rmotext.truncall(vw.v.ID, 'LGEXPR'); End If; -- Set the NAME property of the Object View -- 1) NAME may be passed in as a parameter -- 2) but if not it will be derived from the OOT -- 3) the derived name may have a pre+fix and suffix -- 4) Let the API determine if the NAME is unique, and if -- its not and was user supplied give a fatal error, -- otherwise -- try once more by implanting date timestamp (13chars) to -- the name o('View name is currently : ' || vw.v.NAME); Begin -- Ensure binary indexed table io_OVW_name has been initialized If io_OVW_name(loop_idx) Is NULL Or io_OVW_name(loop_idx) Is NOT NULL Then NULL; End If; Exception When NO_DATA_FOUND Then io_OVW_name(loop_idx) := NULL; When Others Then RAISE; End; If vw.v.ID Is NULL And io_OVW_name(loop_idx) Is NULL Then o('Fetching OOT properties NAME'); Open get_oot_properties; Fetch get_oot_properties Into oot_name; Close get_oot_properties; o('OOT property NAME : '|| oot_name); -- This is the first try for an Object View name if not supplied -- by the user io_OVW_name(loop_idx) := i_prfx ||substrb(oot_name, 1, 30 - nvl(lengthb(i_prfx),0) - nvl(lengthb(i_sufx),0)) ||i_sufx ; End If; -- io_OVW_name can only be NULL at this point if the view already -- exists -- If updating, this will preserve the original name, unless -- overriden by view name parameter vw.v.NAME := nvl(io_OVW_name(loop_idx), vw.v.NAME); if vw.v.NAME is not null then vw.i.NAME := true; end if; o('View name is : ' || vw.v.NAME); vw.v.ORACLE_OBJECT_TYPE_REFERENCE := oot_reference; vw.i.ORACLE_OBJECT_TYPE_REFERENCE := true; -- 4.2.2 Create an OID from the underlying tables primary/unique -- key. -- vw.v.OID_ATTRIBUTE_LIST := NULL; seperator := NULL; For i in get_pkey_colname_list Loop -- note: comma seperators are added by the cursor vw.v.OID_ATTRIBUTE_LIST := vw.v.OID_ATTRIBUTE_LIST || seperator || i.colname; seperator := ', '; End Loop; -- Second try, If there is no primary key use the first defined -- Unique key. If vw.v.OID_ATTRIBUTE_LIST Is NULL Then constraint_ref := NULL; Open get_ukey_id; Fetch get_ukey_id Into constraint_ref; Close get_ukey_id; If constraint_ref Is Not NULL Then For i in get_ukey_colname_list(constraint_ref) Loop -- note: comma seperators are added by the cursor vw.v.OID_ATTRIBUTE_LIST := vw.v.OID_ATTRIBUTE_LIST || seperator || i.colname; seperator := ', '; End Loop; End If; End If; o('OID Attribute List : ' || vw.v.OID_ATTRIBUTE_LIST ); -- Do the INSERT/UPDATE operation for the Object View If vw.v.ID is NULL Then o('Inserting a new Oracle Object View (TAB)'); cioview_definition.ins(null,VW); Else o('Updating the Oracle Object View Definition'); cioview_definition.upd(vw.v.ID,VW); End If; -- If the Object View NAME is not unique, try making it unique -- by including date timestamp and UPDating -- the newly inserted row. -- TNS/9-JUN-99 : Fix bug 898608 If not cimappcon.is_pac_name_unique(cdapi.app_sys_ref , vw.v.ID, vw.v.ELEMENT_TYPE_NAME , conflict_ID) Then o('Fetching OOT properties NAME'); Open get_oot_properties; Fetch get_oot_properties Into oot_name; Close get_oot_properties; io_OVW_name(loop_idx) := i_prfx ||substrb(oot_name, 1, 30 - nvl(lengthb(i_prfx),0) - nvl(lengthb(i_sufx),0) - lengthb(to_char(SYSDATE, 'DDMMYYYYHH24MISS'))) ||to_char(SYSDATE, 'DDMMYYYYHH24MISS') ||i_sufx ; vw.v.NAME := io_OVW_name(loop_idx); if vw.v.NAME is not null then vw.i.NAME := true; end if; -- Perform the UPDATE operation o('Updating the Oracle Object View with unique name'); cioview_definition.upd(vw.v.ID,VW); End If; io_OVW_IDs(loop_idx) := vw.v.ID ; io_OVW_name(loop_idx) := vw.v.NAME; base_tabs(loop_idx).id := TAB_reference; base_tabs(loop_idx).name := TAB_name; base_tabs(loop_idx).alias := TAB_alias; -- 4.2.3.2 Add to FROM clause -- Creates the declarative from clause for the view. create_FROM_clause(vw.v.ID, TAB_reference); End If; End Loop; -- Once all the Views have been created, add a SELECT list clause -- and INSTEAD OF triggers for each View. This has to be done in -- this order so that REF attributes can resolve to the appropriate -- View using the MAKE_REF Function -- Note io_OVW_IDS(loop_idx) may be NULL when more than one View is -- mapped to the Object Type (ambiguous mapping). For loop_idx in 1..i_OOT_IDs.Count Loop If io_OVW_IDs(loop_idx) Is NOT NULL Then -- 4.2.3.1 Add to SELECT list -- Create the view select list based on the Type Attributes -- mapping to columns. -- Gather the information on nested tables to create -- INSTEAD OF trgger PL/SQL blocks (trg_nested_tab_items) -- Open a text output stream for view, for storing -- the Select List. rmotext.open(io_OVW_IDs(loop_idx), 'LGEXPR', 'VW', 'W', txtstream); trg_nested_tab_items.DELETE; -- Note, 'create_select_list' may recurse within 'add_subquery_to' create_SELECT_list( io_OVW_IDs(loop_idx) ,i_OOT_IDs(loop_idx) ,base_tabs(loop_idx).id ,base_tabs(loop_idx).alias ,trg_nested_tab_items ,rcrsv_lvl ,txtstream); rmotext.close(io_OVW_IDs(loop_idx), txtstream); -- 4.2.4 Create INSTEAD OF triggers -- Creates INSTEAD OF triggers for each view which has been -- created. create_INSTEAD_OF_triggers( i_OOT_IDs(loop_idx) ,io_OVW_IDs(loop_idx) ,io_OVW_name(loop_idx) ,base_tabs(loop_idx) ,trg_nested_tab_items); End If; End Loop; o(proc || ' Finished'); ck_util.register (utility_name,'Finish'); rmdbg.flush_trace; EXCEPTION -- Catch all exceptions When Others Then Raise; END create_OVW_4_OOT ; -- BEGIN Local Defined Procedure Bodies procedure o (text varchar2) is -- Output messages via DBMS_OUTPUT when DEBUGGing in SQLPLUS -- or trace file, if diagnostics enabled via client tool. begin If DEBUG Then dbms_output.enable(1000000); dbms_output.put_line(text); Else rmdbg.trace (text); End If; end; Procedure create_FROM_clause ( i_view_id In Number ,i_baserel_id In Number ) Is proc Varchar2(70) := 'CKOVWUTL.create_from_clause'; VWTAB ciorelation_selection.data; BEGIN o(proc ||' entry: View ID '||i_view_id|| ' Base Relation ID'||i_baserel_id); vwtab.v.SEQUENCE_NUMBER := 10; vwtab.i.SEQUENCE_NUMBER := true; vwtab.v.SUBQUERY_USAGE_FLAG := 'N'; vwtab.i.SUBQUERY_USAGE_FLAG := true; vwtab.v.TABLE_REFERENCE := i_baserel_id; if vwtab.v.TABLE_REFERENCE is not null then vwtab.i.TABLE_REFERENCE := true; end if; vwtab.v.VIEW_REFERENCE := i_view_id; if vwtab.v.VIEW_REFERENCE is not null then vwtab.i.VIEW_REFERENCE := true; end if; -- Perform the INSERT operation o('Inserting a new Relation Selection'); ciorelation_selection.ins(null,VWTAB); EXCEPTION -- Catch all exceptions When Others Then Raise; END create_FROM_clause; Procedure create_SELECT_list( i_view_id In Number ,i_oot_reference In Number ,i_basetab_id In Number ,i_alias In Varchar2 ,io_trg_nstd_itms In Out nested_tab_items_ttyp ,i_rcrsv_lvl In Number ,i_txtstrm In RM.stream ) Is proc Varchar2(70) := 'CKOVWUTL.create_select_list'; didx Integer := 1; bidx Integer := 1; nidx Integer := 1; oot_ref ci_oracle_collection_types.oracle_object_type_reference%Type; nstd_tab_id ci_relation_definitions.id%Type; nstd_tab_name ci_relation_definitions.name%Type; att_type_name ci_oracle_types.name%Type; ort_type ci_oracle_types.ort_type%Type; cllctn_type ci_oracle_collection_types.collection_type%Type; objref_flag ci_oracle_collection_types.object_ref_flag%Type; colname2 ci_columns.name%Type; table_id2 ci_relation_definitions.id%Type; oti2 ci_columns.object_type_inclusion%Type; seq_no ci_columns.sequence_number%Type; refd_objviewid ci_relation_definitions.id%Type; fkey_id ci_foreign_key_constraints.id%Type; select_list Varchar2(80) := NULL; from_clause Varchar2(80); txtlen Number := 0; txtwrt Number; seperator Varchar2(2); -- seperator for items in selectlist (NULL,',') seperator2 Varchar2(2); -- seperator for constructor items (NULL,',') Cursor make_basetab_from_clause Is SELECT 'FROM ' || t.name || ' ' || i_alias FROM ci_table_definitions t WHERE t.id = i_basetab_id UNION SELECT 'FROM ' || v.name || ' ' || i_alias FROM ci_view_definitions v WHERE v.id = i_basetab_id ; -- Main driving cursor for creating select list entries. -- Get the attribute details and corresponding columns -- or foreign key constraints Cursor get_type_att_col_maps Is SELECT a.name attname , a.id attid , a.oracle_type_reference otr , a.object_type_inclusion oti , decode(c.datatype , 'LONG', '''UNSUPPORTED MAPPING TO LONG DATATYPE''' ,decode(c.name, NULL, 'NULL', lower(decode(i_alias, NULL, NULL, i_alias ||'.') ||c.name ))) name1 , c.id colid , c.table_reference id1 , a.sequence_number sequence_number FROM ci_columns a , ci_mapping_elements m , ci_transformation_mapping_sets s , ci_columns c WHERE a.oracle_object_type_reference = i_oot_reference AND (a.object_type_inclusion = 'EMBED' -- Get embedded -- Attributes of a Type OR a.object_type_inclusion Is NULL) -- Get scalar attibutes AND m.target_cielement_reference = a.id AND m.source_cielement_reference = c.id AND m.transformation_set_reference = s.id AND s.transformation_type = 'REL2OREL' UNION -- return a 'NULL' value where the attribute is not mapped SELECT a.name attname , a.id attid , a.oracle_type_reference otr , a.object_type_inclusion oti , decode('X', 'Y', 'Z', 'NULL') name1 , decode(1,2,1,NULL) colid , decode(1,2,1,NULL) id1 , a.sequence_number sequence_number FROM ci_columns a WHERE a.oracle_object_type_reference = i_oot_reference AND (a.object_type_inclusion = 'EMBED' -- Get embedded -- Attributes of a Type OR a.object_type_inclusion Is NULL) -- Get scalar attibutes AND not exists ( SELECT NULL FROM ci_mapping_elements m , ci_transformation_mapping_sets s , ci_columns c WHERE m.target_cielement_reference = a.id AND m.source_cielement_reference = c.id AND m.transformation_set_reference = s.id AND s.transformation_type = 'REL2OREL' ) UNION -- also get REF attributes, retrieving the mapped -- FKey name and ID SELECT a.name attname , a.id attid , a.oracle_type_reference otr , a.object_type_inclusion oti , f.name name1 , decode(1,2,1,null) colid , f.id id1 -- fkey ID , a.sequence_number sequence_number FROM ci_columns a , ci_mapping_elements m , ci_transformation_mapping_sets s , ci_foreign_key_constraints f WHERE a.oracle_object_type_reference = i_oot_reference AND a.object_type_inclusion = 'REF' -- Get REF attributes AND m.target_cielement_reference = a.id AND m.source_cielement_reference = f.id AND m.transformation_set_reference = s.id AND s.transformation_type = 'REL2OREL' UNION -- get a 'NULL' for each REF attribute, -- which has no mapping SELECT a.name attname , a.id attid , a.oracle_type_reference otr , a.object_type_inclusion oti , decode('X','Y','Z',null) name1 , decode(1,2,1,null) colid , decode(1,2,1,null) id1 -- fkey ID , a.sequence_number sequence_number FROM ci_columns a WHERE a.oracle_object_type_reference = i_oot_reference AND a.object_type_inclusion = 'REF' -- Get REF attributes AND not exists ( SELECT NULL FROM ci_mapping_elements m , ci_transformation_mapping_sets s , ci_foreign_key_constraints f WHERE m.target_cielement_reference = a.id AND m.source_cielement_reference = f.id AND m.transformation_set_reference = s.id AND s.transformation_type = 'REL2OREL' ) ORDER BY 8 ; -- Get details for processing embedded attribute types, -- either Constructed or Collection. Cursor get_type_of_embedded_att(type_id Number) Is SELECT initcap(t.name) -- type constructor name , t.ort_type , decode('x', 'y', 'z', NULL) -- decodes required to set -- correct datatpe , decode('x', 'y', 'z', NULL) , decode(1, 2, 3, NULL) FROM ci_oracle_types t WHERE t.ort_type = 'OOT' AND t.id = type_id UNION SELECT initcap(t.name) -- attribute type of collections , t.ort_type , t.collection_type , t.object_ref_flag , t.oracle_object_type_reference FROM ci_oracle_collection_types t WHERE t.id = type_id ; -- Get the atribute and column details for constructed embedded -- attributes. The mapped columns must belong to the base table -- mapped to the Type of the Object View. Cursor get_embedded_att_col_maps(oot_reference Number) Is SELECT a.name attname , i_alias ||'.'||c.name colname2 , c.id colid , c.table_reference table_id2 , a.object_type_inclusion oti2 , a.sequence_number sequence_number FROM ci_columns a , ci_mapping_elements m , ci_transformation_mapping_sets s , ci_columns c WHERE a.oracle_object_type_reference = oot_reference AND c.table_reference = i_basetab_id AND m.target_cielement_reference = a.id AND m.source_cielement_reference = c.id AND m.transformation_set_reference = s.id AND s.transformation_type = 'REL2OREL' ORDER BY a.sequence_number ; BEGIN o(proc ||' entry: View ID '||i_view_id|| ' Base Relation ID'||i_basetab_id || ' Object Type ID' ||i_oot_reference); -- Construct a FROM clause to append to Free Format select list -- and get the Table/View alias qualifier for Columns at the same -- time. Open make_basetab_from_clause; Fetch make_basetab_from_clause Into from_clause; Close make_basetab_from_clause; -- construct and store 'Select List' seperator := ' '; For i In get_type_att_col_maps Loop If i.oti Is NULL Then -- attribute is a scalar attribute o('Processing scalar attribute: '|| i.name1); -- add the mapped column to the select list (example 6.1) add_to(select_list, seperator || i.name1, i_txtstrm, i_view_id); -- Else attribute is an object type ElsIf i.oti = EMBED Then -- attribute is embedded o('Processing EMBEDed attribute: '|| i.attname); Open get_type_of_embedded_att(i.otr); Fetch get_type_of_embedded_att Into att_type_name, ort_type, cllctn_type, objref_flag, oot_ref; Close get_type_of_embedded_att; o('att_type_name :'||att_type_name||' ort_type :'||ort_type|| ' cllctn_type :'||cllctn_type||' objref_flag :'||objref_flag|| ' oot_ref :'|| oot_ref); If ort_type = 'OOT' Then -- If attributes of the embedded type are scalar which map to -- columns in the same relational table add type constructor -- to select list (example 6.2) seperator2 := NULL; add_to(select_list, seperator||att_type_name ||'(', i_txtstrm ,i_view_id); For j in get_embedded_att_col_maps(i.otr) Loop If j.oti2 Is NOT NULL Then add_to(select_list, seperator2||j.colname2||'_' , i_txtstrm, i_view_id); Else -- Must be a SCALAR attribute/column add_to(select_list, seperator2||j.colname2, i_txtstrm, i_view_id); End If; seperator2 := ', '; End Loop; -- Note: If 'seperator2' is still NULL, then the attributes where not mapped If seperator2 Is NULL Then add_to(select_list, '', i_txtstrm, i_view_id); End If; -- complete the constructor add_to(select_list, ')', i_txtstrm, i_view_id); ElsIf cllctn_type In ('VARRAY', 'NESTED TABLE') Then o('Processing collection type :' || cllctn_type); If i.id1 = i_basetab_id Then -- attribute maps to column in -- same relational table -- add the column to the select list (example 6.3, 6.6) add_to(select_list, seperator || i.name1, i_txtstrm ,i_view_id); ElsIf fkey_id_between(oot_ref, i_basetab_id, fkey_id , nstd_tab_id, nstd_tab_name) Then o('Relational master-detail implementation, Att_Type_Name :' ||att_type_name); -- attribute maps to a column of a different relation and -- there is a foreign key between this and the context base -- table. -- (relational master - detail implementation) -- Add sub-query to select list (example 6.4, 6.7) o('Arg2 : '|| seperator || ' Arg 3: '|| fkey_id ||' Arg 4 :'||oot_ref || ' Arg 5 :'|| att_type_name || ' Arg 6 :'|| objref_flag); io_trg_nstd_itms(nidx).tab_id := nstd_tab_id; io_trg_nstd_itms(nidx).tab_name := nstd_tab_name; io_trg_nstd_itms(nidx).fkey_id := fkey_id; io_trg_nstd_itms(nidx).att_type := att_type_name; io_trg_nstd_itms(nidx).oot_ref := oot_ref; io_trg_nstd_itms(nidx).att_id := i.attid; io_trg_nstd_itms(nidx).att_name := i.attname; nidx := nidx + 1; If select_list Is NOT NULL Then txtlen := Length(select_list); rmotext.write_text(i_view_id, i_txtstrm, txtlen, select_list ,txtwrt); o('Flushed SELECT LIST: '||select_list); o(txtwrt ||' characters flushed'); rmotext.write_newline(i_view_id, i_txtstrm, txtwrt); select_list := NULL; End If; add_subquery_to(fkey_id , i.attname, att_type_name, oot_ref, objref_flag , io_trg_nstd_itms, i_rcrsv_lvl , seperator , i_txtstrm , i_view_id); Else add_to(select_list, seperator || i.name1 || '_' , i_txtstrm, i_view_id); End If; End If; ElsIf i.oti = REF Then -- attribute is a REF -- If the REF is not mapped, add a NULL entry to select list If i.id1 Is NULL Then add_to(select_list, seperator || 'NULL', i_txtstrm, i_view_id); Else -- add make_ref to select list (example 6.5) add_makeref_to(select_list, seperator, i.otr, i.id1, i_alias , i_txtstrm, i_view_id); End If; End If; seperator := ', '; -- all items after first are preceded by ',' End Loop; -- If select_list Is NOT NULL Then txtlen := Length(select_list); rmotext.write_text(i_view_id, i_txtstrm, txtlen, select_list ,txtwrt); o('Flushed SELECT LIST: '||select_list); o(txtwrt ||' characters flushed'); rmotext.write_newline(i_view_id, i_txtstrm, txtwrt); select_list := NULL; End If; -- add a FROM clause to the free format definition -- (assumes that DDL Server Generator doesn't use the declarative -- part of the View if free format text = 'Y') txtlen := Length(from_clause); rmotext.write_text(i_view_id, i_txtstrm, txtlen, from_clause ,txtwrt); EXCEPTION -- Catch all exceptions When Others Then Raise; END create_SELECT_list; Function fkey_id_between( i_nested_obj_id In Number ,i_base_tab_id In Number ,o_fkey_id Out Number ,o_nstd_tab_id Out Number ,o_nstd_tab_name Out Varchar2 ) Return Boolean Is proc Varchar2(70) := 'CKOVWUTL.fkey_id_between'; fkey_id Number; Cursor get_fkey_id Is SELECT f.id , f.table_reference , t.name FROM ci_foreign_key_constraints f , ci_mapping_elements m , ci_transformation_mapping_sets s , ci_relation_definitions t WHERE m.target_cielement_reference = i_nested_obj_id AND m.transformation_set_reference = s.id AND s.transformation_type = 'REL2OREL' AND f.table_reference = m.source_cielement_reference AND f.foreign_table_reference = i_base_tab_id AND f.primary_key_reference Is NOT NULL AND t.id = f.table_reference AND Not Exists ( -- we'll stop if there's ambiguity -- (>1 fkey 2 pkey) SELECT NULL FROM ci_foreign_key_constraints f2 WHERE f2.table_reference = f.table_reference AND f2.foreign_table_reference = i_base_tab_id AND f2.primary_key_reference Is NOT NULL AND f2.id <> f.id ) ; BEGIN o(proc ||' entry: '|| 'Object Type ID :'|| i_nested_obj_id|| 'TAB ID :'||i_base_tab_id); Open get_fkey_id; Fetch get_fkey_id Into o_fkey_id, o_nstd_tab_id, o_nstd_tab_name; If get_fkey_id%FOUND Then Close get_fkey_id; Return True; Else Close get_fkey_id; Return False; End If; EXCEPTION -- Catch all exceptions When Others Then Raise; END fkey_id_between; Procedure create_INSTEAD_OF_triggers ( i_oot_id In Number ,i_view_id In Number ,i_view_name In Varchar2 ,i_base_tab In base_tab_typ ,i_trg_nstd_itms In nested_tab_items_ttyp ) Is proc Varchar2(70) := 'CKOVWUTL.create_instead_of_triggers'; TRG ciodatabase_trigger.data; PLM cioplsql_module.data; Cursor does_trigger_exist Is SELECT t.ID , p.id FROM ci_database_triggers t , ci_plsql_modules p WHERE t.view_definition_reference = i_view_id AND p.id (+) = t.plsql_module_reference ; conflict_id Number; BEGIN o(proc ||' entry: '|| 'Object View ID :'|| i_view_id|| ' Object View Name :'||i_view_name); Open does_trigger_exist; Fetch does_trigger_exist Into trg.v.ID, plm.v.ID; Close does_trigger_exist; If plm.v.ID Is NULL Then plm.v.FREE_FORMAT_TEXT_FLAG := 'Y'; plm.i.FREE_FORMAT_TEXT_FLAG := true; -- Bug 1313999 - Changed following: substr -> substrb (nls support) (lmcardle. 27-Sep-2000) plm.v.NAME := substrb(i_view_name, 1, 26) || '_TRG'; plm.i.NAME := true; plm.v.PLSQL_MODULE_TYPE := 'TRG-LOGIC'; plm.i.PLSQL_MODULE_TYPE := true; plm.v.PURPOSE := 'PL/SQL for INSTEAD OF trigger created by the ' ||'Object View utility. '; plm.i.PURPOSE := true; -- Bug 1313999 - Changed following: substr -> substrb (nls support) (lmcardle. 27-Sep-2000) plm.v.SHORT_NAME := substrb(i_view_name, 1, 16) || '_TRG'; plm.i.SHORT_NAME := true; -- Perform the INSERT operation o('Inserting a new Plsql Module'); cioplsql_module.ins(null,PLM); -- TNS/9-JUN-99 : Fix bug 898608 If not cimappcon.is_pac_name_unique( cdapi.app_sys_ref, plm.v.ID, plm.v.ELEMENT_TYPE_NAME, conflict_ID) Then plm.v.NAME := substrb(i_view_name, 1, 26-lengthb(to_char(SYSDATE, 'DDMMYYYYHH24MISS'))) || to_char(SYSDATE, 'DDMMYYYYHH24MISS') || '_TRG'; plm.i.NAME := true; plm.v.SHORT_NAME := substrb(i_view_name, 1, 16-lengthb(to_char(SYSDATE, 'DDMMYYYYHH24MISS'))) || to_char(SYSDATE, 'DDMMYYYYHH24MISS') || '_TRG'; plm.i.SHORT_NAME := true; -- Perform the UPDATE operation o('Updating the Plsql Module for unique name'); cioplsql_module.upd(plm.v.ID,PLM); End If; End If; add_INSTEAD_OF_trigger_code (i_oot_id, plm.v.ID, i_base_tab, i_trg_nstd_itms); trg.v.EXECUTE_TIME := 'INSTEAD OF'; trg.i.EXECUTE_TIME := true; -- might as well always set PLSQL Module reference -- rather than testing to see if its just been created trg.v.PLSQL_MODULE_REFERENCE := PLM.v.id; trg.i.PLSQL_MODULE_REFERENCE := true; If trg.v.ID Is NULL Then trg.v.COMPLETE_FLAG := 'Y'; trg.i.COMPLETE_FLAG := true; trg.v.NAME := substrb(i_view_name, 1, 26) || '_TRG'; trg.i.NAME := true; trg.v.PURPOSE := 'INSTEAD OF trigger created by Object View utility.'; trg.i.PURPOSE := true; trg.v.VIEW_DEFINITION_REFERENCE := i_view_id; trg.i.VIEW_DEFINITION_REFERENCE := true; -- Perform the INSERT operation o('Inserting a new Database Trigger'); ciodatabase_trigger.ins(null,TRG); Else o('Updating Database Trigger with ID : '|| trg.v.ID); ciodatabase_trigger.upd(trg.v.ID,TRG); End If; EXCEPTION -- Catch all exceptions When Others Then Raise; END create_INSTEAD_OF_triggers; Procedure add_INSTEAD_OF_trigger_code ( i_oot_id In Number ,i_plsql_mod_id In Number ,i_base_tab In base_tab_typ ,i_trg_nstd_itms In nested_tab_items_ttyp ) Is proc Varchar2(70) := 'CKOVWUTL.add_instead_of_trigger_code'; dclr_sctn dclr_sctn_text_t; proc_body proc_body_text_t; ins_part1 proc_body_text_t; ins_part2 proc_body_text_t; x1 Number := 1; x2 Number := 1; sep Varchar2(3) := NULL; attvar Varchar2(30); txtstrm RM.stream; txtlen Integer; txtwrt Integer; Cursor get_att_cols(tab_id Number, context_objtyp_id Number) Is SELECT a.name attname , a.id attid , a.oracle_type_reference otr , a.object_type_inclusion oti , c.name colname , c.id colid , c.table_reference tabid , c.sequence_number sequence_number FROM ci_columns a , ci_mapping_elements m , ci_transformation_mapping_sets s , ci_columns c WHERE c.table_reference = tab_id -- get the scalar attributes AND a.object_type_inclusion Is NULL AND m.target_cielement_reference = a.id AND m.source_cielement_reference = c.id AND m.transformation_set_reference = s.id AND s.transformation_type = 'REL2OREL' AND a.oracle_object_type_reference = context_objtyp_id UNION SELECT k.name||'.'||a.name attname , a.id attid , a.oracle_type_reference otr , a.object_type_inclusion oti , c.name colname , c.id colid , c.table_reference tabid , c.sequence_number sequence_number FROM ci_columns a , ci_mapping_elements m , ci_transformation_mapping_sets s , ci_columns c , ci_oracle_object_types y , ci_columns k WHERE c.table_reference = tab_id -- get constructor qualified attributes AND m.source_cielement_reference = c.id AND m.target_cielement_reference = a.id AND a.oracle_object_type_reference = y.id AND k.oracle_type_reference = y.id AND k.oracle_object_type_reference = context_objtyp_id AND a.object_type_inclusion Is NULL AND k.object_type_inclusion = 'EMBED' AND m.transformation_set_reference = s.id AND s.transformation_type = 'REL2OREL' UNION -- also get REF attributes and retrieve the mapped -- FKey column components SELECT a.name attname , a.id attid , a.oracle_type_reference otr , a.object_type_inclusion oti , c.name colname , c.id colid , c.table_reference tabid , c.sequence_number sequence_number FROM ci_columns a , ci_mapping_elements m , ci_transformation_mapping_sets s , ci_foreign_key_constraints f , ci_key_components k , ci_columns c WHERE c.table_reference = tab_id AND k.column_reference = c.id and k.constraint_reference = f.id AND a.object_type_inclusion = 'REF' AND a.oracle_object_type_reference = context_objtyp_id AND m.target_cielement_reference = a.id AND m.source_cielement_reference = f.id AND m.transformation_set_reference = s.id AND s.transformation_type = 'REL2OREL' ORDER BY 8 ; -- CvE 19-Dec-2000, bug 1313999 -- I believe that use of substr and length here are OK, as 1 char is to be removed -- from the end of the name Cursor get_ref_type_name(tab_id Number) IS SELECT lower(rpad(substr(o.name, 1, length(o.name)-1)||'V', 32)) ||initcap(o.name)||';' dcl , lower(a.name) ||') INTO '|| lower(substr(o.name, 1, length(o.name)-1)||'V')||' FROM DUAL;' drf -- NOTE: 'SELECT DEREF...' will be prefixed to 'drf' in the inline code FROM ci_mapping_elements m , ci_transformation_mapping_sets s , ci_foreign_key_constraints f , ci_columns a , ci_oracle_object_types o WHERE f.table_reference = tab_id AND Exists ( SELECT NULL FROM ci_columns c , ci_key_components k WHERE c.table_reference = tab_id AND c.id = k.column_reference AND k.constraint_reference = f.id ) AND m.target_cielement_reference = a.id AND m.source_cielement_reference = f.id AND a.oracle_type_reference = o.id AND a.object_type_inclusion = 'REF' AND m.transformation_set_reference = s.id AND s.transformation_type = 'REL2OREL' ; -- CvE 19-Dec-2000, bug 1313999 -- I believe that use of substr and length here are OK, as 1 char is to be removed -- from the end of the name Cursor get_ref_type_att (type_id Number, source_colid Number, sep Varchar2) Is SELECT sep||lower(substr(o.name, 1, length(o.name)-1)||'V'||'.'|| a.name) FROM ci_oracle_object_types o , ci_columns a , ci_key_components k , ci_mapping_elements m , ci_transformation_mapping_sets s WHERE o.id= type_id AND a.oracle_object_type_reference = o.id AND k.column_reference = source_colid AND k.constraint_type = 'FOREIGN' AND k.foreign_column_reference = m.source_cielement_reference AND m.target_cielement_reference = a.id AND m.transformation_set_reference = s.id AND s.transformation_type = 'REL2OREL' UNION -- If the foreign column is a REF, there won't be a mapping. -- For now use the base column rather than the attribute name. -- If necessary, change this to a double or recursive deref. SELECT sep||lower(substr(o.name, 1, length(o.name)-1)||'V'||'.'|| c.name) FROM ci_oracle_object_types o , ci_columns c , ci_key_components k WHERE o.id= type_id AND k.column_reference = source_colid AND k.constraint_type = 'FOREIGN' AND k.foreign_column_reference = c.id AND Not Exists ( SELECT NULL FROM ci_mapping_elements m , ci_transformation_mapping_sets s WHERE k.foreign_column_reference = m.source_cielement_reference AND m.transformation_set_reference = s.id AND s.transformation_type = 'REL2OREL' ) ; BEGIN o(proc ||' entry: '|| 'PL/SQL Module ID :'|| i_plsql_mod_id); dclr_sctn(1) := 'DECLARE'; dclr_sctn(2) := ' i Number;'; proc_body(1) := 'BEGIN'; -- This loop creates the INSERT for the base table/view For i in get_ref_type_name(i_base_tab.id) Loop dclr_sctn(dclr_sctn.LAST+1) := i.dcl; ins_part1(x1) := 'SELECT Deref(:NEW.'||i.drf; x1 := x1 + 1; End Loop; ins_part2(x2) := 'INSERT INTO ' || lower(i_base_tab.name) || ' VALUES ('; x2 := x2 + 1; sep := ' '; For i In get_att_cols(i_base_tab.id, i_oot_id) Loop If i.oti = 'REF' Then o('Obj Type Ref Id: '||i.otr||' Column Id: '||i.colid); Open get_ref_type_att(i.otr, i.colid, sep); Fetch get_ref_type_att Into ins_part2(x2); Close get_ref_type_att; o('X2: '||x2||' ins_part2(x2): '||ins_part2(x2)); Else ins_part2(x2) := sep||':NEW.'||lower(i.attname); End If; x2 := x2 + 1; sep := ', '; End Loop; ins_part2(x2) := ');'; assemble_plsql(ins_part1, ins_part2, proc_body); -- This loop creates INSERT statements for all 'collection' -- type attributes (NESTED-TABLE, VARRAY) For i in 1 .. i_trg_nstd_itms.COUNT Loop x1 := 1; x2 := 1; -- Set variable name for Collection Type -- CvE 19-Dec-2000, bug 1313999 -- I believe that use of substr and length here are OK, as 1 char is to be removed -- from the end of the name attvar := lower(substr(i_trg_nstd_itms(i).att_type, 1, length(i_trg_nstd_itms(i).att_type)-1) ||'V'); o('X2: '||x2||' ATTVAR '||attvar); -- Declare a ref type and a deref for each reference -- attribute used in a collection type For k in get_ref_type_name(i_trg_nstd_itms(i).tab_id) Loop o('X2: '||x2||' DCL '||k.dcl); dclr_sctn(dclr_sctn.LAST+1) := k.dcl; ins_part2(x2) := 'SELECT Deref('||attvar||'(i).'|| k.drf; o('X2: '||x2||' ins_part2 '||ins_part2(x2)); x2 := x2 + 1; End Loop; o('X2 : '||x2); dclr_sctn(dclr_sctn.LAST + 1) := rpad(attvar, 32) || initcap(i_trg_nstd_itms(i).att_type)||';'; ins_part1(x1) := attvar || ' := :NEW.' || i_trg_nstd_itms(i).att_name||';'; x1 := x1 + 1; ins_part1(x1) := 'FOR i IN 1..'||attvar||'.COUNT LOOP'; x1 := x1 + 1; o('X2 : '||x2 || ' i_trg_nstd_itms(i).tab_name '||i_trg_nstd_itms(i).tab_name); ins_part2(x2) := 'INSERT INTO ' || lower(i_trg_nstd_itms(i).tab_name) || ' VALUES ('; x2 := x2 + 1; sep := ' '; For j in get_att_cols(i_trg_nstd_itms(i).tab_id, i_trg_nstd_itms(i).oot_ref) Loop If j.oti = 'REF' Then Open get_ref_type_att(j.otr, j.colid, sep); Fetch get_ref_type_att Into ins_part2(x2); Close get_ref_type_att; Else ins_part2(x2) := sep||attvar||'(i).'||lower(j.attname); End If; x2 := x2 + 1; sep := ', '; End Loop; ins_part2(x2) := ');'; x2 := x2 + 1; ins_part2(x2) := 'END LOOP;'; assemble_plsql(ins_part1, ins_part2, proc_body); End Loop; proc_body(proc_body.LAST + 1) := 'END;'; rmotext.truncall(i_plsql_mod_id, 'CDIPLS'); -- Open a text stream for view and text type, to write to rmotext.open(i_plsql_mod_id, 'CDIPLS', 'PLM', 'W', txtstrm); For i In 1..dclr_sctn.COUNT Loop o(dclr_sctn(i)); txtlen := length(dclr_sctn(i)); rmotext.write_text(i_plsql_mod_id,txtstrm,txtlen,dclr_sctn(i),txtwrt); rmotext.write_newline(i_plsql_mod_id, txtstrm, txtwrt); End Loop; o('Number of lines in proc_body :' || proc_body.COUNT); For i in 1..proc_body.COUNT Loop o('Line number :' ||i); o(proc_body(i)); txtlen := length(proc_body(i)); o('Text length :' ||txtlen); rmotext.write_text(i_plsql_mod_id,txtstrm,txtlen,proc_body(i),txtwrt); o('Text written :' ||txtwrt); rmotext.write_newline(i_plsql_mod_id, txtstrm, txtwrt); End Loop; rmotext.close(i_plsql_mod_id, txtstrm); EXCEPTION -- Catch all exceptions When Others Then Raise; END add_INSTEAD_OF_trigger_code; Procedure assemble_plsql( io_ins_part1 In Out proc_body_text_t ,io_ins_part2 In Out proc_body_text_t ,io_proc_body In Out proc_body_text_t ) Is bx Number := io_proc_body.LAST + 1; -- always >= 1 as first element is 'BEGIN' j Number; BEGIN -- Assemble/copy the text comprising the NESTED TABLE INSERT, -- into the procedure body table. For j in 1..io_ins_part1.COUNT Loop io_proc_body(bx) := io_ins_part1(j); bx := bx + 1; End Loop; For j in 1..io_ins_part2.COUNT Loop io_proc_body(bx) := io_ins_part2(j); bx := bx + 1; End Loop; io_ins_part1.DELETE; io_ins_part2.DELETE; EXCEPTION -- Catch all exceptions When Others Then Raise; END assemble_plsql; Procedure add_to( io_txtbuf In Out Varchar2 ,i_item In Varchar2 ,i_txtstream In RM.stream ,i_view_id In Number ) Is -- Adds the item to text buffer and flushes the buffer -- whenever the maximum length of a line is reached. proc Varchar2(70) := 'CKOVWUTL.add_to'; txtlen Number; txtwrt Number; BEGIN o(proc ||' entry: ' || 'Adding Item :'|| i_item); o('Text bufferText: '||io_txtbuf); o('Adding text item: '||i_item); txtlen := length(io_txtbuf); If lengthb(io_txtbuf) + lengthb(i_item) > MAXLEN Then rmotext.write_text(i_view_id, i_txtstream, txtlen, io_txtbuf ,txtwrt); o('Length of text written: ' ||txtwrt); rmotext.write_newline(i_view_id, i_txtstream, txtwrt); io_txtbuf := i_item; Else io_txtbuf := io_txtbuf || i_item; End If; EXCEPTION -- Catch all exceptions When Others Then Raise; END add_to; Procedure add_makeref_to( io_select_list In Out Varchar2 ,i_seperator In Varchar2 ,i_oot_ref In Number ,i_fkey_id In Number ,i_alias In Varchar2 ,i_txtstream In RM.stream ,i_view_id In Number) Is proc Varchar2(70) := 'CKOVWUTL.add_makeref_to'; refd_objviewname ci_relation_definitions.name%Type; refd_objbasetab ci_relation_definitions.id%Type; seperator Varchar2(2) := NULL; -- local seperator for make_ref arglist Cursor get_refd_objview(object_type_ID Number) Is SELECT v.name , t.id FROM ci_view_definitions v , ci_relation_definitions t , ci_mapping_elements m , ci_transformation_mapping_sets s WHERE v.oracle_object_type_reference = object_type_ID AND m.target_cielement_reference = object_type_ID AND m.source_cielement_reference = t.id AND m.transformation_set_reference = s.id AND s.transformation_type = 'REL2OREL' ; Cursor get_fkey_cols(foreign_key_ID Number) Is SELECT lower(decode(i_alias, NULL, NULL, i_alias ||'.') ||c.name ) key_component , k.sequence_number sequence_number FROM ci_columns c , ci_key_components k WHERE k.constraint_reference = foreign_key_ID AND c.id = k.column_reference ORDER BY k.sequence_number ; Cursor get_pkey_cols(tab_ID Number) Is SELECT lower(decode(i_alias, NULL, NULL, i_alias ||'.') ||c.name ) key_component , k.sequence_number sequence_number FROM ci_columns c , ci_key_components k WHERE c.table_reference = tab_ID AND c.id = k.column_reference AND k.constraint_type = 'PRIMARY' ORDER BY k.sequence_number ; BEGIN o(proc ||' entry: '|| 'Object Type ID :'||i_oot_ref ||' FKey ID : '||i_fkey_id); add_to(io_select_list, i_seperator || ' Make_Ref(', i_txtstream ,i_view_id); Open get_refd_objview(i_oot_ref); Fetch get_refd_objview Into refd_objviewname, refd_objbasetab; Close get_refd_objview ; If refd_objviewname Is NULL Then refd_objviewname := ''; End If; add_to(io_select_list, refd_objviewname ||', ', i_txtstream ,i_view_id); If i_fkey_id Is NULL Then -- Add each of primary key (OID) columns from the ref'd base table -- to the MAKE_REF function. This is the case of a collection -- (varray or nested table) of REF's o('Get views basetable columns for OID. Table ID: '||refd_objbasetab); For j in get_pkey_cols(refd_objbasetab) Loop add_to(io_select_list, seperator || j.key_component, i_txtstream ,i_view_id); seperator := ', '; End Loop; Else -- Add each of the foreign key columns from the Base table -- to the MAKE_REF For j in get_fkey_cols(i_fkey_id) Loop add_to(io_select_list, seperator || j.key_component, i_txtstream ,i_view_id); seperator := ', '; End Loop; End If; add_to(io_select_list, ')', i_txtstream, i_view_id); EXCEPTION -- Catch all exceptions When Others Then Raise; END add_makeref_to; Procedure add_subquery_to( i_fkey_id In Number ,i_att_name In Varchar2 ,i_oot_name In Varchar2 ,i_oot_ref In Number ,i_ref_flag In Varchar2 ,io_trg_nstd_itms In Out nested_tab_items_ttyp ,i_rcrsv_lvl In Number ,i_seperator In Varchar2 ,i_txtstream In RM.stream ,i_view_id In Number) Is proc Varchar2(70) := 'CKOVWUTL.add_subquery_to'; from_tab_id ci_table_definitions.id%Type; from_tab ci_table_definitions.name%Type; to_tab ci_table_definitions.name%Type; alias_from_tab ci_table_definitions.alias%Type; alias_to_tab ci_table_definitions.alias%Type; select_list Varchar2(80) := NULL; txtlen Integer; txtwrt Integer; subq_sep Varchar2(10); -- used to prepend ', ' or 'WHERE ' or -- 'AND ' etc. Cursor get_from_tab Is -- If ALIAS was a property of ci_relation_definition, -- the cursors to get TAB names and ALIAS's would be trivial. -- However, this rather clumsy way will do for now. SELECT t1.name , nvl(t1.alias, 'D'||i_rcrsv_lvl) , t1.id FROM ci_foreign_key_constraints f , ci_table_definitions t1 , ci_relation_definitions t2 WHERE f.id = i_fkey_id -- should be able to assume that -- the foreign key is AND -- for relational tables/views f.table_reference = t1.id AND f.foreign_table_reference = t2.id UNION SELECT t1.name , nvl(t1.alias, 'D'||i_rcrsv_lvl) , t1.id FROM ci_foreign_key_constraints f , ci_view_definitions t1 , ci_relation_definitions t2 WHERE f.id = i_fkey_id -- should be able to assume that -- the foreign key is AND -- for relational tables/views f.table_reference = t1.id AND f.foreign_table_reference = t2.id ; Cursor get_to_tab Is SELECT t2.name , nvl(t2.alias, 'M'||i_rcrsv_lvl) FROM ci_foreign_key_constraints f , ci_table_definitions t2 , ci_relation_definitions t1 WHERE f.id = i_fkey_id -- should be able to assume that -- the foreign key is AND -- for relational tables/views f.table_reference = t1.id AND f.foreign_table_reference = t2.id UNION SELECT t2.name , nvl(t2.alias, 'M'||i_rcrsv_lvl) FROM ci_foreign_key_constraints f , ci_view_definitions t2 , ci_relation_definitions t1 WHERE f.id = i_fkey_id -- should be able to assume that -- the foreign key is AND -- for relational tables/views f.table_reference = t1.id AND f.foreign_table_reference = t2.id ; Cursor get_subq_join_conds(alias_from_tab Varchar2, alias_to_tab Varchar2) Is SELECT alias_from_tab||'.'||lower(c1.name) || '=' || alias_to_tab ||'.'||lower(c2.name) join_cond FROM ci_key_components k , ci_columns c1 , ci_columns c2 WHERE k.constraint_reference = i_fkey_id AND k.column_reference = c1.id AND k.foreign_column_reference = c2.id ; BEGIN o(proc ||' entry: '|| ' Object Type ID: ' || i_oot_ref || ' Object View ID: ' || i_view_id); o('Adding MULTISET SELECT subquery'); -- start by getting the FROM clause, so that alias qualifiers -- for the tables can be retrieved at the same time Open get_from_tab; Fetch get_from_tab Into from_tab, alias_from_tab, from_tab_id; Close get_from_tab; Open get_to_tab; Fetch get_to_tab Into to_tab, alias_to_tab; Close get_to_tab; If alias_from_tab = alias_to_tab Then -- augment 'from' table alias if conflicts with to table alias alias_from_tab := alias_from_tab || 'Q'; End If; txtlen := length(i_seperator||'Cast( Multiset ( SELECT '); o('Writing Text : '||i_seperator||'Cast( Multiset ( SELECT '); rmotext.write_text(i_view_id, i_txtstream, txtlen, i_seperator||'Cast( Multiset ( SELECT ' ,txtwrt); rmotext.write_newline(i_view_id, i_txtstream, txtwrt); If i_ref_flag = 'Y' Then add_makeref_to(select_list, null, i_oot_ref, NULL,alias_from_tab , i_txtstream, i_view_id); -- add a FROM clause to the free format definition -- (assumes that DDL Server Generator doesn't use the declarative -- part of the View if free format text = 'Y') If select_list Is NOT NULL Then txtlen := Length(select_list); rmotext.write_text(i_view_id, i_txtstream, txtlen, select_list ,txtwrt); select_list := NULL; -- reset select_list buffer after flushing o('Flushed SELECT LIST: '||select_list); o(txtwrt ||' characters flushed'); End If; rmotext.write_newline(i_view_id, i_txtstream, txtwrt); txtlen := Length('FROM '||from_tab||' '||alias_from_tab); rmotext.write_text(i_view_id, i_txtstream, txtlen ,'FROM '||from_tab||' '||alias_from_tab ,txtwrt); Else subq_sep := NULL; create_SELECT_list( i_view_id ,i_oot_ref ,from_tab_id ,alias_from_tab ,io_trg_nstd_itms ,i_rcrsv_lvl + 1 ,i_txtstream ); -- Note: the FROM clause was added by 'create_SELECT_list' procedure End If; subq_sep := ' WHERE '; For i in get_subq_join_conds(alias_from_tab, alias_to_tab) Loop add_to(select_list, subq_sep || i.join_cond, i_txtstream ,i_view_id); subq_sep := ' AND '; End Loop; add_to(select_list,') As '||i_oot_name||')', i_txtstream ,i_view_id); If select_list Is NOT NULL Then txtlen := Length(select_list); rmotext.write_text(i_view_id, i_txtstream, txtlen, select_list ,txtwrt); o('Flushed SELECT LIST: '||select_list); o(txtwrt ||' characters flushed'); rmotext.write_newline(i_view_id, i_txtstream, txtwrt); select_list := NULL; End If; EXCEPTION -- Catch all exceptions When Others Then Raise; END add_subquery_to; -- END Local Defined Procedure Bodies END ckovwutl; /