-- Package : jr_rm_cur -- A collection of cursor definitions for queries on the -- repository meta model tables prompt Package Header: jr_rm_cur create or replace package jr_rm_cur is -------------------------------------------------------------------------- -- Cursor : tab_cur -- -- CW 25-Feb-99, only retrieve registered table rows, ie. not meta data -- rows created by configuration, as these are missing things like default -- values and there is a column with meta data which causes the error : -- ORA-02425: create table failed -- ORA-00910: specified length too long for its datatype -------------------------------------------------------------------------- cursor tab_cur (schema_irid number, schema_ivid number, regen varchar2 default 'N') is select t.irid ,t.ivid ,t.table_name ,t.jr_type ,t.initial_extent ,t.next_extent ,t.min_extents ,t.max_extents ,t.pct_increase ,t.comments ,t.name_column ,t.logical_type_column ,t.date_created_column ,t.date_changed_column ,t.created_by_column ,t.changed_by_column ,t.notm_column ,t.extension ,t.nls ,t.gen_api ,t.gen_api_callout ,t.gen_api_callout_stub ,t.owned_by ,t.maintain_dependent_objects from i$rm_sql_tables t, i$rm_sql_schema_members sm where t.irid = sm.object_irid and t.ivid = sm.object_ivid and sm.schema_irid = tab_cur.schema_irid and sm.schema_ivid = tab_cur.schema_ivid and t.is_system is null and (regen = 'Y' or ( not exists (select null from user_tables ut where ut.table_name = 'I$'||t.table_name or ut.table_name = 'L$'||t.table_name and ut.table_name <> t.table_name ) and not exists (select null from user_tables ut where ut.table_name = t.table_name ) ) ) order by t.table_name ; -------------------------------------------------------------------------- -- Cursor : tab_cur_exists -- -- -- CvE 20-Apr-00, Created to fix bug 1226321 -------------------------------------------------------------------------- cursor tab_cur_exists (schema_irid number, schema_ivid number) is select t.table_name from i$rm_sql_tables t, i$rm_sql_schema_members sm where t.irid = sm.object_irid and t.ivid = sm.object_ivid and sm.schema_irid = tab_cur_exists.schema_irid and sm.schema_ivid = tab_cur_exists.schema_ivid and t.is_system is null and ( exists (select null from user_tables ut where ut.table_name = 'I$'||t.table_name or ut.table_name = 'L$'||t.table_name and ut.table_name <> t.table_name ) or exists (select null from user_tables ut where ut.table_name = t.table_name ) ) order by t.table_name ; --------------------------------------------------------------------------------- -- Cursor : tab_not_in_logical_model_cur -- -- CW 10-Dec-98, also populate logical model for 'system' objects -- Need to do this because sdd_vvcs has a foreign key constraint to sdd_vvc_types, which is a -- 'system' object. Thus sdd_vvc_types had no entry in rm_element_types and creation of the -- link property from sdd_vvcs failed. -- Removed 'and t.is_system is null' from query below -- -- There is some odd meta data produced by configuration, thus : -- -- Table SRT Element Type -- RM$TEXT_USAGES -1 --< no row -- RM$TEXT_USAGES 9 --< cfg row --< no row !!! -- -- The following cursor used to populate the logical model if there was no -- *element type*, so we got 'duplicate' rows in rm_sql_row_types, thus : -- -- Table SRT Element Type -- RM$TEXT_USAGES -1 --< new row --< new row -- RM$TEXT_USAGES 9 --< cfg row --< no row ! -- -- Modify cursor check to see if there is a row in rm_sql_row_types referring -- to this table (or a table of the same name) so we won't populate the logical -- model for a table like RM$TEXT_USAGES -- -- CW 23-Mar-99 -- Fix bug 855799, register types as subtype of PAC or SAC -- Also query owned_by -- -- CW 13-Apr-99 -- Fix bug 840715, set descriptors for name column --------------------------------------------------------------------------------- cursor tab_not_in_logical_model_cur (schema_irid number, schema_ivid number) is select t.irid ,t.ivid ,t.table_name ,t.owned_by ,t.name_column from i$rm_sql_tables t, i$rm_sql_schema_members sm where t.irid = sm.object_irid and t.ivid = sm.object_ivid and sm.schema_irid = tab_not_in_logical_model_cur.schema_irid and sm.schema_ivid = tab_not_in_logical_model_cur.schema_ivid and t.populate_logical_model = 'Y' and not exists (select null from i$rm_sql_tables tab , rm_sql_row_types srt where tab.table_name = t.table_name and srt.table_mapped = tab.irid ) ; -------------------------------------------------------------------------- -- Cursor : tab_api_cur -- -- Return objects which require a plsql api to be generated -------------------------------------------------------------------------- cursor tab_api_cur ( schema_irid number , schema_ivid number , regenerate varchar2 default 'N' , include_system_objs varchar2 default 'N' ) is select t.irid ,t.ivid ,t.table_name ,t.gen_api_callout ,t.gen_api_callout_stub from i$rm_sql_tables t, i$rm_sql_schema_members sm where t.irid = sm.object_irid and t.ivid = sm.object_ivid and sm.schema_irid = tab_api_cur.schema_irid and sm.schema_ivid = tab_api_cur.schema_ivid and t.gen_api = 'Y' and ((tab_api_cur.include_system_objs = 'N' and t.is_system is null) or (tab_api_cur.include_system_objs = 'Y' and (t.is_system = 'Y' or t.is_system is null)) ) and ((tab_api_cur.regenerate = 'N' and not exists (select null from user_objects uo where uo.object_name = 'JRO' || t.table_name and uo.object_type = 'PACKAGE' ) ) or (tab_api_cur.regenerate = 'Y') ) ; --------------------------------------------- -- Cursor : col_cur --------------------------------------------- -- CW 15-Jul-98 -- Fix bug 700253, add irid, ivid columns to all objects -- Do not select them here as code adds these columns regardless of whether -- or not they exist in the table cursor col_cur (table_irid number, table_ivid number, include_repos_cols varchar2 default 'N') is select tc.column_name ,tc.data_type ,tc.data_length ,tc.data_precision ,tc.data_scale ,tc.nullable ,tc.column_id ,tc.id ,tc.default_length ,tc.data_default ,tc.num_distinct ,tc.low_value ,tc.high_value ,tc.data_type || -- this turns the attributes into recreate text decode (tc.data_type ,'VARCHAR2' , '(' || tc.data_length || ')' ,'NVARCHAR2' , '(' || tc.data_length || ')' ,'VARCHAR' , '(' || tc.data_length || ')' ,'CHAR' , '(' || tc.data_length || ')' ,'NCHAR' , '(' || tc.data_length || ')' ,'RAW' , '(' || tc.data_length || ')' ,'FLOAT' , decode(tc.data_precision , null, '' , '(' || tc.data_precision || ')' ) ,'NUMBER' , decode(tc.data_precision , null, null , '(' || tc.data_precision || decode(tc.data_scale, 0,'',',' || tc.data_scale) || ')' ) , null ) formatted_data_type ,decode(tc.nullable, 'N', 'NOT NULL') formatted_nullable ,tc.comments ,tc.lob_index_name ,tc.lob_chunk ,tc.lob_pctversion ,tc.lob_cache ,tc.lob_logging ,tc.lob_in_row ,tc.nls from i$rm_sql_columns tc where table_irid=col_cur.table_irid and table_ivid=col_cur.table_ivid and tc.column_name != decode(col_cur.include_repos_cols, 'N', 'IRID', 'XXXIRIDXXX') and tc.column_name != decode(col_cur.include_repos_cols, 'N', 'IVID', 'XXXIVIDXXX') order by column_id; --------------------------------------------- -- Cursor : ind_cur --------------------------------------------- cursor ind_cur (table_irid number, table_ivid number) is select i.irid ,i.ivid ,i.table_irid ,i.table_ivid ,i.index_name ,i.index_type ,i.uniqueness ,i.ini_trans ,i.max_trans ,i.initial_extent ,i.next_extent ,i.min_extents ,i.max_extents ,i.pct_increase ,i.pct_threshold ,i.include_column ,i.freelists ,i.freelist_groups ,i.pct_free from i$rm_sql_indexes i where i.table_irid = ind_cur.table_irid and i.table_ivid = ind_cur.table_ivid and i.index_type IN ('NORMAL', 'BITMAP') and i.uniqueness = 'NONUNIQUE' and not exists (select null from i$rm_sql_constraints con where con.table_irid = ind_cur.table_irid and con.table_ivid = ind_cur.table_ivid and con.constraint_name = i.index_name); --------------------------------------------- -- Cursor : ind_col_cur --------------------------------------------- cursor ind_col_cur (index_irid number, index_ivid number) is select ic.index_irid ,ic.index_ivid ,ic.column_position ,ic.column_name ,ic.column_length from i$rm_sql_ind_columns ic where ic.index_irid=ind_col_cur.index_irid and ic.index_ivid=ind_col_cur.index_ivid order by ic.column_position; --------------------------------------------- -- Cursor : trig_cur --------------------------------------------- cursor trig_cur (table_irid number, table_ivid number) is select t.trigger_name ,t.trigger_type ,t.triggering_event ,t.referencing_names ,t.when_clause ,t.description ,t.trigger_body from i$rm_sql_triggers t where t.table_irid = trig_cur.table_irid and t.table_ivid = trig_cur.table_ivid order by trigger_name; --------------------------------------------- -- Cursor : con_cur --------------------------------------------- -- For referential constraints this query used to return all non-cascade delete constraints since -- they would have been secondary objects and are processed separately -- However, now need to return all referential constraints which are not 'owning' constraints -- -- CW 22-Mar-99 -- Add an optional parameter to also return 'owning' foreign keys. These are needed as objects which -- are secondary to objects which can be both primary and secondary have no real foreign keys. -- cursor con_cur ( table_irid number , table_ivid number , constraint_type varchar2 , get_owners varchar2 default 'N' ) is select c.irid ,c.ivid ,c.table_irid ,c.table_ivid ,c.constraint_name ,c.constraint_type ,c.search_condition ,c.r_constraint_name ,c.delete_rule ,c.deferrable ,c.is_owning_fk ,c.updateable key_updateable from i$rm_sql_constraints c where c.table_irid = con_cur.table_irid and c.table_ivid = con_cur.table_ivid and ((con_cur.constraint_type='C' and c.constraint_type='C') or (con_cur.constraint_type='P' and c.constraint_type='P') or (con_cur.constraint_type='U' and c.constraint_type in ('P', 'U')) or (con_cur.constraint_type='R' and c.constraint_type='R' and c.is_owning_fk='N' and con_cur.get_owners = 'N') or (con_cur.constraint_type='R' and c.constraint_type='R' and con_cur.get_owners = 'Y') ) -- and c.delete_rule!='CASCADE')) and c.constraint_name is not null order by c.constraint_type, c.constraint_name; --------------------------------------------- -- Cursor : con_col_cur --------------------------------------------- cursor con_col_cur (constraint_irid number, constraint_ivid number) is select position ,column_name from i$rm_sql_cons_columns cc where cc.constraint_irid = con_col_cur.constraint_irid and cc.constraint_ivid = con_col_cur.constraint_ivid order by cc.position; --------------------------------------------- -- Cursor : ofk_cur -- Fix bug 774361, if owning foreign key columns are also in unique key constraint of a -- table, then generate a modified unique key constraint rather than a plsql function to -- check uniqueness --------------------------------------------- cursor ofk_cur (table_irid number, table_ivid number) is select c.irid ,c.ivid from i$rm_sql_constraints c where c.table_irid = ofk_cur.table_irid and c.table_ivid = ofk_cur.table_ivid and c.constraint_type = 'R' and c.is_owning_fk != 'N'; --------------------------------------------- -- Cursor : ofk_col_cur --------------------------------------------- cursor ofk_col_cur (constraint_irid number, constraint_ivid number) is select position ,column_name from i$rm_sql_cons_columns cc where cc.constraint_irid = ofk_col_cur.constraint_irid and cc.constraint_ivid = ofk_col_cur.constraint_ivid order by cc.position; ----------------------------------------------------------------------------- -- Cursor : nullify_con_cur -- Cursor to get all 'nullify' FK references for a table. -- That is, optional foreign key references that should be nullified when -- an object is deleted from this table. ----------------------------------------------------------------------------- --Required info is not in RS as yet...so cursor always returns no rows.. cursor nullify_con_cur (table_irid number, table_ivid number) is select fk.irid, fk.ivid, fk.constraint_name, fktab.table_name fk_table_name, fktab.owned_by from i$rm_sql_constraints pk, i$rm_sql_constraints fk, i$rm_sql_tables fktab where pk.table_irid=nullify_con_cur.table_irid and pk.table_ivid = nullify_con_cur.table_ivid and pk.constraint_type in ('P', 'U') and fk.r_constraint_name = pk.constraint_name and fk.constraint_type = 'R' and fk.delete_rule = 'NULLIFY' and fk.is_owning_fk = 'N' and fktab.irid = fk.table_irid and fktab.ivid = fk.table_ivid; -- Cursor to query all non-owning cascade delete foreign keys referencing this table -- Now that cascade delete foreign keys can exist (which are not owning foreign keys) need to generate -- code to implement the cascade delete in the versioned world cursor cascade_con_cur (table_irid number, table_ivid number) is select fk.irid, fk.ivid, fk.constraint_name, fktab.table_name fk_table_name, fktab.owned_by from i$rm_sql_constraints pk, i$rm_sql_constraints fk, i$rm_sql_tables fktab where pk.table_irid = cascade_con_cur.table_irid and pk.table_ivid = cascade_con_cur.table_ivid and pk.constraint_type in ('P', 'U') and fk.r_constraint_name = pk.constraint_name and fk.constraint_type = 'R' and fk.delete_rule = 'CASCADE' and fk.is_owning_fk = 'N' and fktab.irid = fk.table_irid and fktab.ivid = fk.table_ivid ; -- Cursor to query all owning cascade delete foreign keys referencing this table -- Need to generate code in triggers to cascade delete any children of secondary -- objects... cursor owning_con_cur (table_irid number, table_ivid number) is select fk.irid, fk.ivid, fk.constraint_name, fktab.table_name fk_table_name from i$rm_sql_constraints pk, i$rm_sql_constraints fk, i$rm_sql_tables fktab where pk.table_irid = owning_con_cur.table_irid and pk.table_ivid = owning_con_cur.table_ivid and pk.constraint_type in ('P', 'U') and fk.r_constraint_name = pk.constraint_name and fk.constraint_type = 'R' and fk.delete_rule = 'CASCADE' and fk.is_owning_fk in ('Y','O') and fktab.irid = fk.table_irid and fktab.ivid = fk.table_ivid ; --BUG 908240 WS 11-JUN-1999: Convert 'force' queries to check mandatory-ness --of reference properties in logical model rather than relying on --column 'NULLABLE' property -------------------------------------------- -- Cursor : force_null_con_cur -------------------------------------------- -- Cursor to get all nullable foreign keys that would normally -- block i.e. - get foreign keys to be nulled by force delete cursor force_null_con_cur (table_irid number, table_ivid number) is select fk.irid, fk.ivid, fk.constraint_name, fktab.table_name fk_table_name, fktab.owned_by from i$rm_sql_constraints fk, i$rm_sql_constraints pk, i$rm_sql_tables fktab where pk.table_irid = force_null_con_cur.table_irid and pk.table_ivid = force_null_con_cur.table_ivid and fk.r_constraint_name = pk.constraint_name and fk.constraint_type = 'R' and fk.delete_rule = 'NO ACTION' and fk.table_irid = fktab.irid and fk.table_ivid = fktab.ivid and not exists (select null from i$rm_sql_tables tab ,i$rm_sql_columns fkcol ,i$rm_sql_cons_columns fkccol ,i$rm_property_maps map ,i$rm_properties prop where tab.table_name = fktab.table_name and fkccol.constraint_irid=fk.irid and fkccol.constraint_ivid=fk.ivid and fkcol.table_irid = tab.irid and fkcol.table_ivid = tab.ivid and fkcol.column_name=fkccol.column_name and map.in_column = fkcol.id and prop.id=map.property and prop.mandatory = 'Y' ); -------------------------------------------- -- Cursor : force_casc_con_cur -------------------------------------------- -- Cursor to get all non-nullable foreign keys that would normally -- block i.e. - get foreign keys to be cascade deleted by force delete cursor force_casc_con_cur (table_irid number, table_ivid number) is select fk.irid, fk.ivid, fk.constraint_name, fktab.table_name fk_table_name from i$rm_sql_constraints fk, i$rm_sql_constraints pk, i$rm_sql_tables fktab where pk.table_irid = force_casc_con_cur.table_irid and pk.table_ivid = force_casc_con_cur.table_ivid and fk.r_constraint_name = pk.constraint_name and fk.constraint_type = 'R' and fk.delete_rule = 'NO ACTION' and fk.table_irid = fktab.irid and fk.table_ivid = fktab.ivid and exists (select null from i$rm_sql_tables tab ,i$rm_sql_columns fkcol ,i$rm_sql_cons_columns fkccol ,i$rm_property_maps map ,i$rm_properties prop where tab.table_name = fktab.table_name and fkccol.constraint_irid=fk.irid and fkccol.constraint_ivid=fk.ivid and fkcol.table_irid = tab.irid and fkcol.table_ivid = tab.ivid and fkcol.column_name=fkccol.column_name and map.in_column = fkcol.id and prop.id=map.property and prop.mandatory = 'Y' ); ----------------------------------------------------------------------------- -- Cursor : foreign_key_cur -- Fix bug 808307, need to query *all* foreign keys, with owning information ----------------------------------------------------------------------------- cursor foreign_key_cur (table_irid number, table_ivid number) is select c.irid ,c.ivid ,c.constraint_name ,c.r_constraint_name ,c.is_owning_fk from i$rm_sql_constraints c where c.table_irid = foreign_key_cur.table_irid and c.table_ivid = foreign_key_cur.table_ivid and c.constraint_type = 'R' ; --------------------------------------------- -- Cursor : view_cur --------------------------------------------- cursor view_cur (schema_irid number, schema_ivid number) is select v.irid ,v.ivid ,v.view_name ,v.text_length ,v.text ,v.type_text_length ,v.type_text ,v.oid_text_length ,v.oid_text ,v.view_type_owner ,v.view_type from i$rm_sql_views v, i$rm_sql_schema_members sm where v.irid = sm.object_irid and v.ivid = sm.object_ivid and sm.schema_irid = view_cur.schema_irid and sm.schema_ivid = view_cur.schema_ivid and not exists (select null from user_views uv where uv.view_name = v.view_name); --------------------------------------------- -- Cursor : seq_cur --------------------------------------------- cursor seq_cur (schema_irid number, schema_ivid number) is select seq.irid ,seq.ivid ,seq.sequence_name ,seq.min_value ,seq.max_value ,seq.increment_by ,seq.cycle_flag ,seq.order_flag ,seq.cache_size ,seq.start_with from i$rm_sql_sequences seq, i$rm_sql_schema_members sm where seq.irid = sm.object_irid and seq.ivid = sm.object_ivid and sm.schema_irid = seq_cur.schema_irid and sm.schema_ivid = seq_cur.schema_ivid and not exists (select null from user_sequences us where us.sequence_name = seq.sequence_name); --------------------------------------------- -- Cursor : stored_obj_cur --------------------------------------------- cursor stored_obj_cur (schema_irid number, schema_ivid number) is select so.irid ,so.ivid ,so.name ,so.type from i$rm_sql_stored_objects so, i$rm_sql_schema_members sm where so.irid = sm.object_irid and so.ivid = sm.object_ivid and sm.schema_irid = stored_obj_cur.schema_irid and sm.schema_ivid = stored_obj_cur.schema_ivid and not exists (select null from user_objects uo where uo.object_name = so.name); --------------------------------------------- -- Cursor : src_cur --------------------------------------------- cursor src_cur (stored_object_irid number, stored_object_ivid number) is select s.line , s.text from i$rm_sql_source s where s.stored_object_irid = src_cur.stored_object_irid and s.stored_object_ivid = src_cur.stored_object_ivid order by s.line; --------------------------------------------- -- Cursor : syn_cur --------------------------------------------- cursor syn_cur (schema_irid number, schema_ivid number) is select s.irid ,s.ivid ,s.synonym_name ,s.object_name from i$rm_sql_synonyms s, i$rm_sql_schema_members sm where s.irid = sm.object_irid and s.ivid = sm.object_ivid and sm.schema_irid = syn_cur.schema_irid and sm.schema_ivid = syn_cur.schema_ivid and not exists (select null from user_synonyms us where us.synonym_name = s.synonym_name); --------------------------------------------- -- Cursor : fk_col_cur --------------------------------------------- cursor fk_col_cur (fk_constraint_irid number, fk_constraint_ivid number) is select fkc.column_name fk_column_name ,pkc.column_name pk_column_name ,fkc.position from i$rm_sql_cons_columns fkc , i$rm_sql_constraints fk , i$rm_sql_constraints pk , i$rm_sql_cons_columns pkc where fk.irid=fk_col_cur.fk_constraint_irid and fk.ivid=fk_col_cur.fk_constraint_ivid and fkc.constraint_irid=fk.irid and fkc.constraint_ivid=fk.ivid and fkc.position=pkc.position and pkc.constraint_irid=pk.irid and pkc.constraint_ivid=pk.ivid and fk.r_constraint_name=pk.constraint_name order by fkc.position; --------------------------------------------- -- Cursor : child_tab_cur --------------------------------------------- -- This cursor returns all children of a table, ie, all tables connected by a normal (non-owning) foreign key. -- -- This is used to generate code (functions) to check blocking foreign keys and is called from triggers on -- configuration members when objects are inserted, updated or deleted -- -- CW 4-May-1999 -- Fix bug 875958, child_tab_cur should return all fks for validating when the referenced pk is updated -- Query all non-owning fks (real fks will check when pk is updated) cursor child_tab_cur (table_irid number, table_ivid number) is select t.table_name , t.owned_by , t.jr_type , t.logical_type_column , fk.irid , fk.ivid , fk.constraint_name , fk.deferrable , fk.delete_rule , pk.updateable pk_updateable from i$rm_sql_constraints fk, i$rm_sql_constraints pk, i$rm_sql_tables t where pk.table_irid = child_tab_cur.table_irid and pk.table_ivid = child_tab_cur.table_ivid and fk.r_constraint_name = pk.constraint_name and fk.constraint_type = 'R' and fk.is_owning_fk = 'N' and fk.table_irid = t.irid and fk.table_ivid = t.ivid; --------------------------------------------------- -- Cursor: nls_name_cur -- -- This cursor gets the NLS name for the logical -- element type that is stored in a table. -- As a table can store more than one logical type, -- cursor returns the most generic type... --------------------------------------------------- cursor nls_name_cur (table_name in varchar2) is select nls.nls_name name from rm_element_type_extensions nls ,rm_element_types et ,rm_element_types st ,rm_sql_row_types rt ,rm_sql_tables tab where nls.for_type=et.id and st.id=et.supertypes and et.primary_row_type=rt.id and rt.table_mapped=tab.id and tab.name=nls_name_cur.table_name and st.short_name in ('SHR','NSHR','SAC','MM'); ------------------------------------------------------------------ -- Cursor: type_id_cur -- -- This cursor gets the logical type id for an element type that -- is stored in a table. -- As a table can store more than one logical type, cursor -- returns the most generic type... ------------------------------------------------------------------ cursor type_id_cur (table_name in varchar2) is select et.id from rm_element_types et ,rm_element_types st ,rm_sql_row_types rt ,rm_sql_tables tab where tab.name = type_id_cur.table_name and rt.table_mapped = tab.id and et.primary_row_type = rt.id and st.id = et.supertypes and st.short_name in ('SHR','NSHR','SAC','MM', 'FEL') ; ------------------------------------------------------------------ -- Cursor : owned_tables_cur -- -- Query all tables owned by the specified table ------------------------------------------------------------------ cursor owned_tables_cur (table_irid number, table_ivid number) is select t.irid , t.ivid , t.table_name , decode(t.nls, 'Y', 'l$', 'i$') table_prefix , fk.constraint_name , fk.irid fk_irid , fk.ivid fk_ivid from i$rm_sql_constraints fk , i$rm_sql_constraints pk , i$rm_sql_tables t where pk.table_irid = owned_tables_cur.table_irid and pk.table_ivid = owned_tables_cur.table_ivid and fk.r_constraint_name = pk.constraint_name and fk.is_owning_fk in ('Y', 'O') and fk.table_irid = t.irid and fk.table_ivid = t.ivid; ------------------------------------------------------------------ -- Cursor : schema_members_cur -- -- Query all members in a schema ------------------------------------------------------------------ cursor schema_members_cur (schema_name varchar2) is select sm.object_irid , sm.object_ivid , sm.object_type from i$rm_sql_schema_versions s , i$rm_sql_schema_members sm where s.name = upper(schema_members_cur.schema_name) and sm.schema_irid = s.irid and sm.schema_ivid = s.ivid ; ------------------------------------------------------------------ -- Cursor : schema_tabs_with_fk_cur -- -- Query all foreign keys in a schema ------------------------------------------------------------------ cursor schema_tabs_with_fk_cur (schema_name varchar2) is select tab.irid , tab.ivid , tab.repos_table_name from i$rm_sql_schema_versions s , i$rm_sql_schema_members sm , rm_sql_tables tab where s.name = upper(schema_tabs_with_fk_cur.schema_name) and sm.schema_irid = s.irid and sm.schema_ivid = s.ivid and sm.object_type = 'TABLE' and tab.irid = sm.object_irid and tab.ivid = sm.object_ivid and exists (select null from rm_sql_constraints fk where fk.table_irid = tab.irid and fk.table_ivid = tab.ivid and fk.constraint_type = 'R' ) ; end jr_rm_cur; /