plginds.spb

Oracle PL/SQL procedure generator (second generator type)

CREATE OR REPLACE PACKAGE BODY PLGinds
IS

/*----------------------------------------------------------------
||                  PL/Vision Professional 
||----------------------------------------------------------------
||    File: plginds.spb
||  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
||
-----------------------------------------------------------------*/
   c_pkg CONSTANT PLGadmin.identifier := 'plginds';
   
   CURSOR ind_cur (sch_in IN VARCHAR2, tab_in IN VARCHAR2, ind IN VARCHAR2)
   IS
      SELECT owner, table_name, table_owner, 
             index_name, table_type, uniqueness,
             0 column_count,
             0 starting_row,
             0 cumulative_column_count
        FROM ALL_INDEXES
       WHERE table_name = tab_in
         AND table_owner = sch_in
         AND (ind IS NULL or index_name = UPPER (ind))
       ORDER BY uniqueness;

   CURSOR indcol_cur 
     (indsch_in IN VARCHAR,
      sch_in IN VARCHAR2, 
      tab_in IN VARCHAR2,
      ind_in IN VARCHAR2)
   IS
      SELECT column_name, column_position, column_length, 
             0 abs_position
        FROM ALL_IND_COLUMNS
       WHERE index_owner = indsch_in
         AND table_name = tab_in
         AND table_owner = sch_in
         AND index_name = ind_in
      ORDER BY column_position;

   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)
   IS
      v_tab PLGadmin.identifier := tab;
      v_pky PLGadmin.identifier;
      spec PLGobj.spec_type;
      ind_rec ind_rectype;
      indcol_rec indcol_rectype;
      v_colrow PLS_INTEGER := 0;
      v_cum PLS_INTEGER := 0;
      v_abs_pos PLS_INTEGER := 0;
   BEGIN
      indtab.DELETE;
      coltab.DELETE;

      IF indextype = c_unique_nopky
      THEN
         v_pky := PLGpky.name (tab, sch);
      END IF;

      IF INSTR (v_tab, '.') = 0 AND sch IS NOT NULL
      THEN
         v_tab := sch || '.' || v_tab;
      END IF; 
      spec := PLGobj.convspec (v_tab, resolve_synonym => TRUE);

      OPEN ind_cur (spec.owner, spec.name, one_index);
      FETCH ind_cur INTO ind_rec;
      IF ind_cur%NOTFOUND
      THEN
         CLOSE ind_cur;
         OPEN ind_cur (spec.owner, UPPER (spec.name), one_index);
         FETCH ind_cur INTO ind_rec;
      END IF;
      
      WHILE ind_cur%FOUND         
      LOOP
         IF (indextype = c_all) OR
            (indextype = c_unique_only AND ind_rec.uniqueness = c_unique) OR
            (indextype = c_unique_nopky AND ind_rec.uniqueness = c_unique AND ind_rec.index_name != v_pky) 
         THEN
            indtab (NVL (indtab.LAST, 0)+1) := ind_rec;
            indtab (indtab.LAST).column_count := 0;
            OPEN indcol_cur (ind_rec.owner, ind_rec.table_owner,
               ind_rec.table_name, ind_rec.index_name);
            LOOP
               FETCH indcol_cur INTO indcol_rec;
               v_abs_pos := v_abs_pos + 1;
               indcol_rec.abs_position := v_abs_pos;

               EXIT WHEN indcol_cur%NOTFOUND;
               
               v_colrow := PLGofst.shifted (indtab.COUNT, c_max, 
                  indcol_cur%ROWCOUNT, 1);

               coltab (v_colrow) := indcol_rec;
               
               indtab(indtab.LAST).column_count := indcol_cur%ROWCOUNT;
               IF indcol_cur%ROWCOUNT = 1
               THEN
                  indtab(indtab.LAST).starting_row := v_colrow;
               END IF;
            END LOOP;
            v_cum := v_cum + indtab (indtab.LAST).column_count;
            indtab (indtab.LAST).cumulative_column_count := v_cum;
            CLOSE indcol_cur;
         END IF;
         FETCH ind_cur INTO ind_rec;
      END LOOP;
      CLOSE ind_cur;
   END;

   FUNCTION isunique (indtab IN ind_tabtype, indnum IN INTEGER) 
      RETURN BOOLEAN
   IS
      retval BOOLEAN := indtab.EXISTS (indnum);
   BEGIN
      IF retval
      THEN
         retval := indtab(indnum).uniqueness = c_unique;
      END IF;
      RETURN retval;
   END;

   FUNCTION nthcol 
      (coltab IN indcol_tabtype, indnum IN INTEGER, colnum IN INTEGER)
      RETURN indcol_rectype
   IS
      retval indcol_rectype;
   BEGIN
      retval := coltab (PLGofst.shifted (indnum, c_max, colnum, 1));
      RETURN retval;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         DBMS_OUTPUT.PUT_LINE ('PLGinds.nthcol tried to get ' || 
            PLGofst.shifted (indnum, c_max, colnum, 1));
   END;
   
   FUNCTION nthind (indtab IN ind_tabtype, indname IN VARCHAR2) RETURN INTEGER
   IS
      v_indname PLGadmin.identifier := UPPER (indname);
      v_row PLS_INTEGER;
      retval PLS_INTEGER;
   BEGIN
      IF indname IS NOT NULL
      THEN
         v_row := indtab.FIRST;
         LOOP
            EXIT WHEN v_row IS NULL;
            IF v_indname = indtab(v_row).index_name
            THEN
               retval := v_row;
               EXIT;
            ELSE
               v_row := indtab.NEXT (v_row);
            END IF;
         END LOOP;
      END IF;
      RETURN retval;
   END;

END PLGinds;                      
/
rem show errors