plginds.sps

Oracle PL/SQL procedure generator (second generator type)

CREATE OR REPLACE PACKAGE PLGinds &authidopen AUTHID CURRENT_USER &authidclose

/* Retrieves indexes owned by specified table IN specified schema. */

/*----------------------------------------------------------------
||                  PL/Vision Professional 
||----------------------------------------------------------------
||    File: PLGinds.sps
||  Author: Steven Feuerstein
||
|| This is a part of the PL/Vision Professional Code library.
|| Copyright (C) 1996-99 Quest Software, Inc.
|| All rights reserved.
||
|| For more information, call Quest Software at 1-800-REVEAL4
|| or check out our Web page: www.Quest Software.com
||
-----------------------------------------------------------------*/
IS   
   c_max CONSTANT PLS_INTEGER := 255;

   c_unique CONSTANT CHAR(6) := 'UNIQUE';
   c_nonunique CONSTANT CHAR(9) := 'NONUNIQUE';

   c_all CONSTANT PLS_INTEGER := 0;
   c_unique_only CONSTANT PLS_INTEGER := 1;
   c_unique_nopky CONSTANT PLS_INTEGER := 2;
   
   TYPE ind_rectype IS RECORD
      (owner ALL_INDEXES.OWNER%TYPE, 
       table_name ALL_INDEXES.TABLE_NAME%TYPE,
       table_owner ALL_INDEXES.TABLE_OWNER%TYPE,
       index_name ALL_INDEXES.INDEX_NAME%TYPE,
       table_type ALL_INDEXES.TABLE_TYPE%TYPE,
       uniqueness ALL_INDEXES.UNIQUENESS%TYPE,
       column_count INTEGER,
       starting_row INTEGER,
       cumulative_column_count INTEGER);
   
   TYPE ind_tabtype IS TABLE OF ind_rectype 
      INDEX BY BINARY_INTEGER;

   TYPE indcol_rectype IS RECORD
      (column_name ALL_IND_COLUMNS.COLUMN_NAME%TYPE,
       column_position ALL_IND_COLUMNS.COLUMN_POSITION%TYPE,
       abs_position PLS_INTEGER,
       column_length ALL_IND_COLUMNS.COLUMN_LENGTH%TYPE);
   
   TYPE indcol_tabtype IS TABLE OF indcol_rectype 
      INDEX BY BINARY_INTEGER;
         
   PROCEDURE fortab (tab IN VARCHAR2,
      indtab IN OUT ind_tabtype, 
      coltab IN OUT indcol_tabtype,
      sch IN VARCHAR2 := NULL,
      indextype IN INTEGER := c_all,
      one_index IN VARCHAR2 := NULL);

   FUNCTION isunique (indtab IN ind_tabtype, indnum IN INTEGER) 
      RETURN BOOLEAN;

   FUNCTION nthind (indtab IN ind_tabtype, indname IN VARCHAR2) RETURN INTEGER;

   FUNCTION nthcol 
      (coltab IN indcol_tabtype, indnum IN INTEGER, colnum IN INTEGER)
      RETURN indcol_rectype;
   
END PLGinds;                      
/
rem show errors