plgpky.spb

Oracle PL/SQL procedure generator (second generator type)

CREATE OR REPLACE PACKAGE BODY PLGpky
IS

/*----------------------------------------------------------------
||                  PL/Vision Professional 
||----------------------------------------------------------------
||    File: PLGpky.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 := 'PLGpky';

   CURSOR info_cur (sch_in IN VARCHAR2, tab_in IN VARCHAR2)
   IS
      SELECT          
         cons.constraint_name,
         cons.constraint_type,
         cols.column_id,
         cons_cols.position,
         cols.column_name
      FROM 
         /* dba_ */ ALL_CONS_COLUMNS cons_cols, 
         /* dba_ */ ALL_CONSTRAINTS cons,
         /* dba_ */ ALL_TAB_COLUMNS cols
      WHERE
         cons.owner = sch_in
         AND cons.table_name = tab_in
         AND cons.constraint_type = 'P'
         AND cons_cols.owner = cons.owner
         AND cons_cols.table_name = cons.table_name
         AND cons_cols.constraint_name = cons.constraint_name
         AND cols.owner = sch_in
         AND cols.table_name = tab_in
         AND cols.column_name = cons_cols.column_name
      ORDER BY cons.constraint_name,cons_cols.position;

   CURSOR pkyname_cur (sch_in IN VARCHAR2, tab_in IN VARCHAR2)
   IS
      SELECT cons.constraint_name
        FROM /* dba_ */ ALL_CONSTRAINTS cons
       WHERE cons.owner = sch_in
         AND cons.table_name = tab_in
         AND cons.constraint_type = 'P';

   FUNCTION name (tab IN VARCHAR2, sch IN VARCHAR2 := NULL)
      RETURN VARCHAR2
   IS
      v_tab PLGadmin.identifier := tab;
      spec PLGobj.spec_type;
      pkyname_rec pkyname_cur%ROWTYPE;
   BEGIN
      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 pkyname_cur (spec.owner, spec.name);
      FETCH pkyname_cur INTO pkyname_rec;
      IF pkyname_cur%NOTFOUND
      THEN
         CLOSE pkyname_cur;
         OPEN pkyname_cur (spec.owner, UPPER (spec.name));
         FETCH pkyname_cur INTO pkyname_rec;
      END IF;
      CLOSE pkyname_cur;
      RETURN pkyname_rec.constraint_name;
   END;
   
   FUNCTION fortab (tab IN VARCHAR2, sch IN VARCHAR2 := NULL)
      RETURN pky_tabtype
   IS
      v_tab PLGadmin.identifier := tab;
      spec PLGobj.spec_type;
      info_rec pky_rectype;
      retval pky_tabtype;

      PROCEDURE load_info IS
      BEGIN
         /* Try both regular case and upper case */
         OPEN info_cur (spec.owner, spec.name);      
         FETCH info_cur INTO info_rec;
         IF info_cur%NOTFOUND
         THEN
            CLOSE info_cur;
            OPEN info_cur (spec.owner, UPPER (spec.name));
            FETCH info_cur INTO info_rec;
         END IF;
         
         WHILE info_cur%FOUND
         LOOP
            retval (NVL (retval.LAST, 0) + 1) := info_rec;         
            FETCH info_cur INTO info_rec;
         END LOOP;
         CLOSE info_cur;
      END;
   BEGIN
      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);

      load_info;
       
      RETURN retval; 
   END;

   FUNCTION ispkycol (pkytab IN pky_tabtype, col IN VARCHAR2) RETURN BOOLEAN
   IS
      v_col PLGadmin.identifier := UPPER (col);
      v_row PLS_INTEGER := pkytab.FIRST;
      retval BOOLEAN := FALSE;
   BEGIN
      LOOP
         EXIT WHEN v_row IS NULL OR retval;
         retval := pkytab(v_row).column_name = v_col;
         v_row := pkytab.NEXT (v_row);
      END LOOP;
      RETURN retval;
   END;

   FUNCTION numcols (tab IN VARCHAR2, sch IN VARCHAR2 := NULL)
      RETURN INTEGER
   IS
      CURSOR pky_cur
      IS
         SELECT COUNT(*)         
         FROM 
            /* dba_ */ ALL_CONS_COLUMNS cons_cols, 
            /* dba_ */ ALL_CONSTRAINTS cons
         WHERE
            cons.owner = NVL (UPPER (sch), USER)
            AND cons.table_name = UPPER (tab)
            AND cons.constraint_type = 'P'
            AND cons_cols.owner = cons.owner
            AND cons_cols.table_name = cons.table_name
            AND cons_cols.constraint_name = cons.constraint_name;
      retval PLS_INTEGER := 0;
   BEGIN
      OPEN pky_cur;
      FETCH pky_cur INTO retval;
      CLOSE pky_cur;
      RETURN retval;
   END;

END PLGpky;                      
/
rem show errors