rem ---------------------------------------------------------------------------- rem File: $RCSfile: jriscur.jps $ rem Author: $Author: cwilliam $ rem Date: $Date: 1998/12/14 15:33:19 $ rem Version: $Revision: 1.2 $ rem Status: $State: Exp $ rem Locked_by: $Locker: $ rem Description: rem Notes: rem RealAuth: rem IncepDate: rem Copyright: (c) Oracle Corporation 1998. All Rights Reserved. rem ---------------------------------------------------------------------------- rem Log of Changes from RCS rem ---------------------------------------------------------------------------- rem $Log: jriscur.jps $ rem Revision 1.2 1998/12/14 15:33:19 cwilliam rem modified table cursor to return tables not previously registered rem since there may be entries in rm_sql_tables for configured tables rem rem Revision 1.1 1998/09/09 15:56:28 cwilliam rem Initial revision rem rem ---------------------------------------------------------------------------- rem -- Package : jr_schema_cur -- A collection of cursor definitions for queries on the Oracle -- data dictionary tables prompt Package Header: jr_schema_cur create or replace package jr_schema_cur is ------------------------------------------------------------------------------- -- Cursor : tab_cur -- -- CW 10-Dec-98, return tables not previously registered, ie. where types = -1 -- CW 14-Apr-00, fix bug 1261236, remove outer join as query is slow -- Outer join not required since there is a row inserted into -- all_tab_comments for every table, regardless of whether there -- is a comment (comment is null if no comment exists) ------------------------------------------------------------------------------- cursor tab_cur (schema_name varchar2) is select t.table_name ,t.pct_free ,t.pct_used ,t.ini_trans ,t.max_trans ,t.initial_extent ,t.next_extent ,t.min_extents ,t.max_extents ,t.pct_increase ,c.comments from all_tables t, all_tab_comments c where t.owner=upper(tab_cur.schema_name) and t.owner=c.owner and t.table_name=c.table_name and not exists (select null from i$rm_sql_tables rt where rt.table_name = t.table_name and rt.product = 'JR' ); ------------------------------------------------------------------------------- -- Cursor : tab_cur_exists -- -- CvE 20-Apr-00, Created to fix bug 1226321 ------------------------------------------------------------------------------- cursor tab_cur_exists (schema_name varchar2) is select t.table_name from all_tables t where t.owner=upper(tab_cur_exists.schema_name) and exists (select null from i$rm_sql_tables rt where rt.table_name = t.table_name and rt.product = 'JR' ); -- Colin Williams 6th May 1998 -- Des2k model already has IRID, IVID columns defined -- Don't want register to try and add them again so modify cursor below -- to ignore them -- Generate only adds IRID, IVID columns if table has no 'owning' table, -- ie. it has no cascade foreign key to another table -- This means for a pac table we will not get 2 sets of IRID, IVID columns. -- However, this does mean that if there are IRID, IVID columns on a table -- which does have an 'owning' table, register will ignore the columns and -- generate will not add them ,this means we *lose* the columns, but I -- don't think this matters. -- 11th May 1998, as above except for PARENT_IRID, PARENT_IVID columns as well -- 14th May 1998, only ignore PARENT_IRID, PARENT_IVID columns if they belong to -- table SDD_SAC_ELEMENTS as this will have a foreign key defined -- so that registration generates these columns. -- However, there are other tables with PARENT_IRID, PARENT_IVID -- columns defined but no foreign key (eg. SDD_FOLDER_MEMBERS, -- so we are losing these columns and views based on these tables -- are failing to build. -- -- 4th September 1998 -- Query all columns that are in the user's table. The generate procedure will -- ignore any special columns -- --------------------------------------------- -- Cursor : col_cur --------------------------------------------- cursor col_cur (schema_name varchar2, table_name varchar2) is select col.table_name ,col.column_name ,col.data_type ,col.data_type || -- this turns the attributes into recreate text decode (col.data_type ,'VARCHAR2' , '(' || col.data_length || ')' ,'NVARCHAR2' , '(' || col.data_length || ')' ,'VARCHAR' , '(' || col.data_length || ')' ,'CHAR' , '(' || col.data_length || ')' ,'NCHAR' , '(' || col.data_length || ')' ,'RAW' , '(' || col.data_length || ')' ,'FLOAT' , decode(col.data_precision , null, '' , '(' || col.data_precision || ')' ) ,'NUMBER' , decode(col.data_precision , null, null , '(' || col.data_precision || decode(col.data_scale, 0,'',',' || col.data_scale) || ')' ) , null ) formatted_data_type ,decode(col.nullable, 'N', 'NOT NULL') formatted_nullable ,col.data_type_mod ,col.data_type_owner ,col.data_length ,col.data_precision ,col.data_scale ,col.nullable ,col.column_id ,col.default_length ,col.data_default ,col.num_distinct ,col.low_value ,col.high_value ,col.density ,col.num_nulls ,col.num_buckets ,col.last_analyzed ,col.sample_size ,col.character_set_name from all_tab_columns col where col.owner=upper(col_cur.schema_name) and col.table_name=col_cur.table_name order by col.column_id ; -- Note: These two are separate because the views are very slow as outer joins. --------------------------------------------- -- Cursor : col_com_cur --------------------------------------------- cursor col_com_cur (schema_name varchar2, table_name varchar2, column_name varchar2) is select com.comments from all_col_comments com where owner=upper(col_com_cur.schema_name) and table_name=col_com_cur.table_name and column_name=col_com_cur.column_name; --------------------------------------------- -- Cursor : col_lob_cur --------------------------------------------- cursor col_lob_cur (schema_name varchar2, table_name varchar2, column_name varchar2) is select decode(substr(index_name,1,3),'SYS', null, index_name) index_name ,chunk ,pctversion ,cache ,logging ,in_row from all_lobs where owner=upper(col_lob_cur.schema_name) and table_name=col_lob_cur.table_name and column_name=col_lob_cur.column_name; ---------------------------------------------------------------------- -- Cursor : con_cur -- -- 14-Apr-2000 CW Fix bug 948067 -- Constraints with Oracle generated names are ignored -- ie. generated = 'GENERATED NAME' -- constraint_name is like 'SYS%' (but not relying on this) ---------------------------------------------------------------------- cursor con_cur (schema_name varchar2, table_name varchar2) is select constraint_name -- decode(generated, 'USER NAME', constraint_name) constraint_name , constraint_type , search_condition , r_constraint_name , delete_rule , deferrable from all_constraints where owner = upper(con_cur.schema_name) and table_name = con_cur.table_name and status = 'ENABLED'; --------------------------------------------- -- Cursor : con_col_cur --------------------------------------------- cursor con_col_cur (schema_name varchar2, constraint_name varchar2) is select position ,column_name from all_cons_columns where owner=upper(con_col_cur.schema_name) and constraint_name=con_col_cur.constraint_name; --------------------------------------------- -- Cursor : trig_cur --------------------------------------------- cursor trig_cur (schema_name varchar2, table_name varchar2) is select trigger_name ,trigger_type ,triggering_event ,referencing_names ,when_clause ,description ,trigger_body from all_triggers where owner=upper(trig_cur.schema_name) and table_name=trig_cur.table_name and status='ENABLED'; --------------------------------------------- -- Cursor : ind_cur --------------------------------------------- cursor ind_cur (schema_name varchar2, table_name varchar2) is select index_name ,index_type ,uniqueness ,ini_trans ,max_trans ,initial_extent ,next_extent ,min_extents ,max_extents ,pct_increase ,pct_threshold ,include_column ,freelists ,freelist_groups ,pct_free from all_indexes where owner=upper(ind_cur.schema_name) and table_name=ind_cur.table_name and generated='N'; --------------------------------------------- -- Cursor : ind_col_cur --------------------------------------------- cursor ind_col_cur (schema_name varchar2, index_name varchar2) is select column_name ,column_position ,column_length from all_ind_columns where index_owner=upper(ind_col_cur.schema_name) and index_name=ind_col_cur.index_name; --------------------------------------------- -- Cursor : view_cur --------------------------------------------- cursor view_cur (schema_name varchar2) is select 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 all_views v where v.owner=upper(view_cur.schema_name) and not exists (select null from i$rm_sql_views rv where rv.view_name=v.view_name); --------------------------------------------- -- Cursor : seq_cur --------------------------------------------- cursor seq_cur (schema_name varchar2) is select s.sequence_name ,s.min_value ,s.max_value ,s.increment_by ,s.cycle_flag ,s.order_flag ,s.cache_size ,s.last_number from all_sequences s where sequence_owner=upper(seq_cur.schema_name) and not exists (select null from i$rm_sql_sequences rs where rs.sequence_name=s.sequence_name); --------------------------------------------- -- Cursor : stored_obj_cur --------------------------------------------- cursor stored_obj_cur (schema_name varchar2) is select o.object_name ,o.object_type from all_objects o where o.owner=upper(stored_obj_cur.schema_name) and o.object_type in ('PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY') and not exists (select null from i$rm_sql_stored_objects so where so.name=o.object_name) order by decode( o.object_type , 'PROCEDURE', 1 , 'FUNCTION', 2 , 'PACKAGE', 3 , 'PACKAGE BODY', 4 ) ,o.object_name; --------------------------------------------- -- Cursor : src_cur --------------------------------------------- cursor src_cur (schema_name varchar2, name varchar2, type varchar2) is select s.text ,s.line from all_source s where s.owner=upper(src_cur.schema_name) and s.name=src_cur.name and s.type=src_cur.type order by s.line; --------------------------------------------- -- Cursor : syn_cur --------------------------------------------- cursor syn_cur (schema_name varchar2) is select v.synonym_name ,v.table_name from all_synonyms v where v.owner=upper(syn_cur.schema_name) and v.table_owner=upper(syn_cur.schema_name) and v.db_link is null and not exists (select null from i$rm_sql_synonyms rv where rv.synonym_name=v.synonym_name); end jr_schema_cur; / rem rem ---------------------------------------------------------------------------- rem $$Header_is_done rem End of file $RCSfile: jriscur.jps $ rem ----------------------------------------------------------------------------