DROP PROCEDURE ADRREF.ADRREF_PROC_CACHE_OBJECTS;

CREATE OR REPLACE PROCEDURE ADRREF.ADRREF_PROC_CACHE_OBJECTS AS

  CURSOR adrref_tab  IS select A.TABLE_NAME, A.CONSTRAINT_NAME, B.COLUMN_NAME
                        from sys.user_constraints a, sys.user_cons_columns b
                        where a.constraint_name = B.CONSTRAINT_NAME
                        AND A.CONSTRAINT_TYPE = 'P'
                        ORDER BY TABLE_NAME ;

  CURSOR adrref_ndx  IS select A.TABLE_NAME, A.CONSTRAINT_NAME, B.COLUMN_NAME, C.INDEX_NAME
                               from sys.user_constraints a, sys.user_cons_columns b, sys.user_indexes c
                               where a.constraint_name = B.CONSTRAINT_NAME
                               AND a.table_name = c.table_name
                               AND A.CONSTRAINT_TYPE = 'P'
                               ORDER BY TABLE_NAME;

  sql_stmt        String(512);
  v_max_id        number;

  BEGIN
     FOR this_sql IN adrref_tab  LOOP
       sql_stmt :=  'SELECT /*+ FULL(' ||this_sql.table_name||' ) */ MAX('
                                       || this_sql.column_name
                                       || ') from '
                                       || this_sql.table_name;
       EXECUTE IMMEDIATE sql_stmt into v_max_id;
     END LOOP;

     FOR this_sql IN adrref_ndx  LOOP  -- Not even trying to run this yet. DBMS runs fine
       sql_stmt :=  'SELECT /*+ INDEX(' ||this_sql.table_name|| ' ' ||this_sql.index_name||' ) */ MAX('||this_sql.column_name ||')  from '||this_sql.table_name ;
--       DBMS_OUTPUT.PUT_LINE(sql_stmt);
       EXECUTE IMMEDIATE sql_stmt into v_max_id;
     END LOOP;

  END;
/
