plgcons.spb

Oracle PL/SQL procedure generator (second generator type)

CREATE OR REPLACE PACKAGE BODY PLGcons
IS

/*----------------------------------------------------------------
||                  PL/Vision Professional 
||----------------------------------------------------------------
||    File: PLGcons.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_max CONSTANT INTEGER := 1000;

   v_cols PLGadmin.dbmaxvc2 := NULL;

   CURSOR cons_cur (sch VARCHAR2, tab VARCHAR2, 
     stat VARCHAR2, typ VARCHAR2)
   IS
      SELECT constraint_name,
             constraint_type,
             table_name,
             search_condition,
             r_owner,
             r_constraint_name,
             delete_rule,
             status
        FROM /* dba_ */ ALL_CONSTRAINTS
       WHERE owner = sch
         AND table_name = tab
         AND (constraint_type = typ OR typ IS NULL)
         AND (status = stat OR stat IS NULL);

   PROCEDURE clrcol
   IS
   BEGIN
      v_cols := NULL;
   END;

   PROCEDURE addcol (col IN VARCHAR2)
   IS
   BEGIN
      v_cols := v_cols || '^' || col || '^';
   END;

   FUNCTION newcol (col IN VARCHAR2) RETURN BOOLEAN
   IS
   BEGIN
      RETURN (INSTR (v_cols, '^' || col || '^') = 0 OR v_cols IS NULL);
   END;

   FUNCTION rowintab (
      tab_in IN ident_tabtype,
      val_in IN VARCHAR2,
      ignorecase IN BOOLEAN := FALSE
      )
   RETURN PLS_INTEGER
   IS
      v_val PLGadmin.identifier := val_in;
      retval PLS_INTEGER := tab_in.FIRST;
   BEGIN
      IF ignorecase
      THEN
         v_val := UPPER (v_val);
      END IF;

      LOOP
         EXIT WHEN retval IS NULL;
         IF ignorecase
         THEN
            EXIT WHEN v_val = UPPER (tab_in(retval));
         ELSE
            EXIT WHEN v_val = tab_in(retval);
         END IF;
         retval := tab_in.NEXT (retval);
      END LOOP;

      RETURN retval;
   END;

   PROCEDURE fortab (tab IN VARCHAR2,
      constab IN OUT cons_tabtype, 
      coltab IN OUT conscol_tabtype,
      sch IN VARCHAR2 := NULL,
      consstatus IN VARCHAR2 := NULL,
      constype IN VARCHAR2 := NULL,
      colsuffix IN VARCHAR2 := NULL,
      colprefix IN VARCHAR2 := NULL,
      identtag IN VARCHAR2 := NULL
      )
   IS
      ucolstab ident_tabtype;
   BEGIN
      fortab (
         tab, constab, coltab, ucolstab,
         sch, consstatus, constype,
         colsuffix, colprefix, identtag
         );
   END;

   PROCEDURE fortab (
      tab IN VARCHAR2,
      constab IN OUT cons_tabtype, 
      coltab IN OUT conscol_tabtype,
      ucolstab IN OUT ident_tabtype,
      sch IN VARCHAR2 := NULL,
      consstatus IN VARCHAR2 := NULL,
      constype IN VARCHAR2 := NULL,
      colsuffix IN VARCHAR2 := NULL,
      colprefix IN VARCHAR2 := NULL,
      identtag IN VARCHAR2 := NULL
      )
   IS
      v_tab PLGadmin.identifier := tab;
      v_pky PLGadmin.identifier;
      spec PLGobj.spec_type;
      cons_rec cons_cur%ROWTYPE;
      conscol_rec conscol_rectype;
      v_row PLS_INTEGER := 0;
      v_colrow PLS_INTEGER := 0;
      v_cum PLS_INTEGER := 0;
      v_abs_pos PLS_INTEGER := 0;
      v_token PLGadmin.identifier;
      v_delims PLGadmin.dbmaxvc2 :=
         TRANSLATE (PLGprs.plsql_delimiters, 'A"', 'A');
      sc ALL_CONSTRAINTS.SEARCH_CONDITION%TYPE;
      sctab PLGadmin.vc2000_tabtype;

   BEGIN
      constab.DELETE;
      coltab.DELETE;

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

      OPEN cons_cur (spec.owner, spec.name, consstatus, constype);
            
      LOOP
         FETCH cons_cur INTO cons_rec;
         EXIT WHEN cons_cur%NOTFOUND;
         v_row := NVL (constab.LAST, 0)+1;
         constab(v_row).constraint_name := cons_rec.constraint_name;
         constab(v_row).converted_constraint_name := cons_rec.constraint_name;
         constab(v_row).constraint_type := cons_rec.constraint_type;
         constab(v_row).table_name := cons_rec.table_name;

         constab(v_row).r_owner := cons_rec.r_owner;
         constab(v_row).r_constraint_name := cons_rec.r_constraint_name;

         constab(v_row).search_condition := cons_rec.search_condition;
         constab(v_row).converted_search_condition := NULL;

         constab(v_row).delete_rule := cons_rec.delete_rule;
         constab(v_row).status := cons_rec.status;

         constab(v_row).column_count := 0;
         constab(v_row).cumulative_column_count := 0;
         constab(v_row).starting_row := NULL;
         constab(v_row).starting_row := NULL;

         /* Parse out the condition. */
         IF RTRIM (cons_rec.search_condition) IS NOT NULL
         THEN
            sctab.DELETE; /* 1/28/99 Have to clear each time. */

            /* 99.2 7/99 - might have "COLNAME" IS NOT NULL */
            PLGprs.string (cons_rec.search_condition, sctab,
               delimiters_in => v_delims);

            clrcol; /* 1/7/99 Clear it anew for each constraint. */

            FOR tokenind IN sctab.FIRST .. sctab.LAST
            LOOP
               /* Search against table with PLGcols.iscolumn */
                  
               v_token := sctab(tokenind);

               IF PLGcols.iscolumn (spec.name, v_token, spec.owner)
               THEN
                  /* 99.2 strip off " and UPPER case it. */
                  IF SUBSTR (v_token, 1, 1) = '"'
                  THEN
                     v_token := TRANSLATE (v_token, 'A"', 'A');
                  END IF;
                  v_token := UPPER (v_token);
                  
                  IF newcol (v_token)
                  THEN
                     addcol (v_token);
                      
                     constab (v_row).column_count := constab (v_row).column_count + 1;
                     v_abs_pos := v_abs_pos + 1;
                     
                     v_colrow := PLGofst.shifted (v_row, c_max, 
                        constab(v_row).column_count, 1);

                     coltab(v_colrow).column_name := v_token;

                     /* 4/98 Build unique cols table. */
                     IF rowintab (ucolstab, v_token) IS NULL
                     THEN
                        ucolstab (NVL (ucolstab.LAST, 0) + 1) := v_token;
                     END IF;

                     coltab(v_colrow).abs_position := v_abs_pos;
                     
                     coltab(v_colrow).converted_column_name :=
                        identtag || colprefix || v_token || colsuffix || identtag;

                     v_token := coltab(v_colrow).converted_column_name;    
                      
                     IF constab(v_row).starting_row IS NULL -- tokenind = sctab.FIRST
                     THEN
                        constab(v_row).starting_row := v_colrow;
                     END IF;
                  ELSE
                     v_token := 
                        identtag || colprefix || v_token || colsuffix || identtag;
                  END IF; 
               END IF;
               
               constab (v_row).converted_search_condition := 
                  constab (v_row).converted_search_condition || v_token;
                  
            END LOOP;
            
            v_cum := v_cum + constab (v_row).column_count;
            constab (v_row).cumulative_column_count := v_cum;
         END IF;

         IF constab(v_row).column_count = 1 AND
            UPPER (constab (v_row).search_condition) =
               coltab(v_colrow).column_name || ' IS NOT NULL' 
         THEN
            constab(v_row).not_null := TRUE;
            IF LENGTH (coltab(v_colrow).column_name) < c_max_converted_length - 8
            THEN
               constab(v_row).converted_constraint_name := 
                  'NOTNULL_' || coltab(v_colrow).column_name;
            END IF;
         END IF;
      END LOOP;
      CLOSE cons_cur;
   EXCEPTION
      WHEN OTHERS
      THEN
         IF cons_cur%ISOPEN THEN CLOSE cons_cur; END IF;
         RAISE;
   END;

   FUNCTION nthcol 
      (coltab IN conscol_tabtype, consnum IN INTEGER, colnum IN INTEGER)
      RETURN conscol_rectype
   IS
      retval conscol_rectype;
   BEGIN
      retval := coltab (PLGofst.shifted (consnum, c_max, colnum, 1));
      RETURN retval;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         DBMS_OUTPUT.PUT_LINE('PLGcons.nthcol failed to retrieve '||
            PLGofst.shifted (consnum, c_max, colnum, 1));
   END;

   FUNCTION isconscol 
      (coltab IN conscol_tabtype, colname IN VARCHAR2)
      RETURN BOOLEAN
   IS
      v_col PLGadmin.identifier := UPPER (colname);
      v_row PLS_INTEGER := coltab.FIRST;
   BEGIN
      LOOP
         EXIT WHEN v_row IS NULL OR v_col = coltab(v_row).column_name;
         v_row := coltab.NEXT (v_row);
      END LOOP;
      RETURN v_row IS NOT NULL;
   END;

END PLGcons;                      
/