plgcols.spb

Oracle PL/SQL procedure generator (second generator type)

CREATE OR REPLACE PACKAGE BODY PLGcols
IS

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

   g_dbg BOOLEAN := FALSE;
   
   CURSOR info_cur (sch_in IN VARCHAR2, tab_in IN VARCHAR2)
   IS
      SELECT t.column_id, 
             t.column_name, 
             data_type, 
             data_length, 
             data_precision,
             data_type || ' ' ||
             DECODE(data_type, 
                'NUMBER', DECODE(data_precision, NULL, NULL,
                    '('||DECODE(data_scale, 0, TO_CHAR(data_precision),
                       data_precision||','||data_scale) ||')'),
                'VARCHAR', '('||data_length||')',
                'VARCHAR2', '('||data_length||')',
                'CHAR', '('||data_length||')',
                'RAW', '('||data_length||')',
                NULL) data_declaration,
             nullable, 
             default_length, 
             data_default,
             comments,
             NULL, 
             NULL, 
             NULL, 
             NULL

        FROM /* dba_ */ ALL_TAB_COLUMNS T, /* dba_ */ ALL_COL_COMMENTS C

       WHERE T.OWNER = UPPER (sch_in) 
         AND T.TABLE_NAME = tab_in /* 99.2.8 don't change case UPPER (tab_in) */
         AND T.OWNER = C.OWNER
         AND T.TABLE_NAME = C.TABLE_NAME
         AND T.COLUMN_NAME = C.COLUMN_NAME

       ORDER BY COLUMN_ID;

   FUNCTION fortab (tab IN VARCHAR2, sch IN VARCHAR2 := NULL,
      getkeys IN BOOLEAN := FALSE)
      RETURN col_tabtype
   IS
      v_tab PLGadmin.identifier := tab;
      spec PLGobj.spec_type;
      info_rec col_rectype;
      retval col_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;
      
      IF getkeys AND retval.COUNT > 0
      THEN
         DECLARE
            pkys PLGpky.pky_tabtype;
            cr PLS_INTEGER;
         BEGIN
            /* Get PK information */
            pkys := PLGpky.fortab (tab, sch);
            IF pkys.COUNT > 0
            THEN
               FOR rowind IN pkys.FIRST .. pkys.LAST
               LOOP     
                  cr := retval.FIRST;
                  LOOP
                     IF retval(cr).column_name = pkys(rowind).column_name
                     THEN
                        retval(cr).constraint_name := pkys(rowind).constraint_name;
                        retval(cr).constraint_type := pkys(rowind).constraint_type;
                        retval(cr).pky_position := pkys(rowind).position;
                     END IF;
                     cr := cr + 1;
                     EXIT WHEN cr > retval.LAST;
                  END LOOP;
               END LOOP;
            END IF;
         END;
      END IF;
      
      RETURN retval; 
   END;

   PROCEDURE setcomment (colrec_inout IN OUT col_rectype, 
      comment_in IN ALL_COL_COMMENTS.COMMENTS%TYPE)
   IS
   BEGIN
      colrec_inout.comments := comment_in;
   END;

   FUNCTION ispky (coltab IN col_tabtype, colrow IN PLS_INTEGER) RETURN BOOLEAN
   IS
   BEGIN
      RETURN NVL (coltab(colrow).constraint_type = 'P', FALSE);
   END;

   FUNCTION iscolumn (tab IN VARCHAR2, col IN VARCHAR2, sch IN VARCHAR2 := NULL)
      RETURN BOOLEAN
   IS
      /* 99.2 "col" handling */
      v_col /* dba_ */ ALL_TAB_COLUMNS.COLUMN_NAME%TYPE;
      
      CURSOR info_cur (col VARCHAR2)
      IS
         SELECT 'x'
           FROM /* dba_ */ ALL_TAB_COLUMNS
          WHERE OWNER = UPPER (NVL (sch, USER)) 
            AND TABLE_NAME = UPPER (tab)
            AND COLUMN_NAME = col;
      info_rec info_cur%ROWTYPE;
      retval BOOLEAN;
   BEGIN
      IF SUBSTR (col, 1, 1) = '"'
      THEN
         v_col := TRANSLATE (col, 'A"', 'A');
      ELSE
         v_col := UPPER (col);
      END IF;
      
      OPEN info_cur (v_col);
      
      FETCH info_cur INTO info_rec;
      retval := info_cur%FOUND;
      CLOSE info_cur;
      RETURN retval;
   END;

END PLGcols;                      
/
rem show errors