plgfkys.spb

Oracle PL/SQL procedure generator (second generator type)

CREATE OR REPLACE PACKAGE BODY PLGfkys
IS

/*----------------------------------------------------------------
||                  PL/Vision Professional 
||----------------------------------------------------------------
||    File: PLGfkys.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
-----------------------------------------------------------------*/

/***************** Modification History **************************
|| Date     By     Description
|| -------- ------ -----------------------------------------------
|| 3/15/98  SEF    Q298 collist function
|| 1/98     SEF    Q198 Add logic to ignore self ref fkys.
|| 11/97    SEF    Use resolve_synonym option for convspec.
|| 10/97    SEF    Break out cursor to get correct results.
|| 6/97     SEF    Created.
******************************************************************/
   c_pkg CONSTANT PLGadmin.identifier := 'PLGfkys';

   c_max CONSTANT PLS_INTEGER := 1000;

   CURSOR fky_cur (sch_in  VARCHAR2, tab_in VARCHAR2, cons_in VARCHAR2 := NULL)
   IS
      SELECT constraint_name,
             constraint_type,
             table_name,
             r_owner,
             r_constraint_name
        FROM /* dba_ */ ALL_CONSTRAINTS cons
       WHERE owner = sch_in
         AND table_name = tab_in
         AND constraint_type = 'R'
         AND (cons_in IS NULL OR constraint_name = UPPER (cons_in))
       ORDER BY constraint_name;

   CURSOR fkycol_cur (sch_in IN VARCHAR2, tab_in IN VARCHAR2,
      cons_in IN VARCHAR2)
   IS
      SELECT column_name,
             position
        FROM /* dba_ */ ALL_CONS_COLUMNS cons_cols
       WHERE cons_cols.owner = NVL (sch_in, USER)
         AND cons_cols.table_name = UPPER (tab_in)
         AND cons_cols.constraint_name = UPPER (cons_in)
       ORDER BY position;

   CURSOR pky_cur 
      (sch_in IN VARCHAR2, cons_in IN VARCHAR2, pos_in IN PLS_INTEGER)
   IS
      SELECT table_name, column_name
        FROM /* dba_ */ ALL_CONS_COLUMNS   
      WHERE owner = sch_in
        AND constraint_name = UPPER (cons_in)
        AND position = pos_in;

   PROCEDURE fortab (tab IN VARCHAR2, 
      fkys_out IN OUT fky_tabtype,
      fkycols_out IN OUT fkycol_tabtype,
      sch IN VARCHAR2 := NULL,
      one_constraint IN VARCHAR2 := NULL,
      incl_self_ref IN BOOLEAN := TRUE)
   IS
      v_tab PLGadmin.identifier := tab;
      v_fkyrow PLS_INTEGER;
      v_colrow PLS_INTEGER;
      v_cum PLS_INTEGER := 0;
      v_abs_pos PLS_INTEGER := 0;
	   fky_rec fky_cur%ROWTYPE;
      pky_rec pky_cur%ROWTYPE;
      spec PLGobj.spec_type;
   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 fky_cur (spec.owner, spec.name, one_constraint);
      FETCH fky_cur INTO fky_rec;
      IF fky_cur%NOTFOUND
      THEN
         CLOSE fky_cur;
         OPEN fky_cur (spec.owner, UPPER (spec.name), one_constraint);
         FETCH fky_cur INTO fky_rec;
      END IF;

      WHILE fky_cur%FOUND
      LOOP
         DECLARE  
            self_ref_fky EXCEPTION;
         BEGIN
            v_fkyrow := NVL (fkys_out.LAST, 0) + 1;

            /* For each foreign key... */
            fkys_out(v_fkyrow).constraint_name := fky_rec.constraint_name;
            fkys_out(v_fkyrow).constraint_type := fky_rec.constraint_type;
            fkys_out(v_fkyrow).table_name := fky_rec.table_name;
            fkys_out(v_fkyrow).pky_constraint_name := fky_rec.r_constraint_name;
            fkys_out(v_fkyrow).pky_owner := fky_rec.r_owner;

            FOR colrec IN fkycol_cur (spec.owner, spec.name, fky_rec.constraint_name)
            LOOP
               --  v_colrow := NVL (fkycols_out.LAST, 0) + 1;

               v_colrow := PLGofst.shifted (v_fkyrow, c_max, 
                  fkycol_cur%ROWCOUNT, 1);

               /* For each column in the FK... */
               fkycols_out(v_colrow).column_name := colrec.column_name;
               fkycols_out(v_colrow).position := colrec.position;
               v_abs_pos := v_abs_pos + 1;
               fkycols_out(v_colrow).abs_position := v_abs_pos;

               fkys_out(v_fkyrow).column_count := fkycol_cur%ROWCOUNT;

               /* Add the primary key information. */
               OPEN pky_cur (fky_rec.r_owner, fky_rec.r_constraint_name, colrec.position);
               FETCH pky_cur INTO pky_rec;
               CLOSE pky_cur;
               fkycols_out(v_colrow).pky_column_name := pky_rec.column_name;

               IF fkycol_cur%ROWCOUNT = 1
               THEN
                  /* If PKY table is same as FKY table, skip this FKY. */
                  IF NOT incl_self_ref AND pky_rec.table_name = UPPER (spec.name)
                  THEN
                     RAISE self_ref_fky;
                  END IF;

                  fkys_out(v_fkyrow).pky_table_name := pky_rec.table_name;
                  fkys_out(v_fkyrow).starting_row := v_colrow;
               END IF;
            END LOOP;
            v_cum := v_cum + fkys_out(fkys_out.LAST).column_count;
            fkys_out(fkys_out.LAST).cumulative_column_count := v_cum;
         EXCEPTION
            WHEN self_ref_fky
            THEN
               fkys_out.DELETE (fkys_out.LAST);
         END;
         FETCH fky_cur INTO fky_rec;
      END LOOP;
      CLOSE fky_cur;
   END;

   FUNCTION nthcol 
      (fkycoltab IN fkycol_tabtype, 
       fkynum IN INTEGER := 1, 
       colnum IN INTEGER := 1) 
      RETURN fkycol_rectype
   IS
      retval fkycol_rectype;
   BEGIN
      retval := fkycoltab (PLGofst.shifted (fkynum, c_max, colnum, 1));

      RETURN retval;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         DBMS_OUTPUT.PUT_LINE('PLGfkys.nthcol tried to get' ||
            PLGofst.shifted (fkynum, c_max, colnum, 1));
   END;

   FUNCTION isfky (
      tab IN VARCHAR2, 
      fky IN VARCHAR2, 
      sch IN VARCHAR2 := NULL)
   RETURN BOOLEAN
   IS
	   fky_rec fky_cur%ROWTYPE;
   BEGIN
      OPEN fky_cur (sch, tab, fky);
      FETCH fky_cur INTO fky_rec;
      IF fky_cur%NOTFOUND
      THEN
         CLOSE fky_cur;
         OPEN fky_cur (sch, UPPER (tab), fky);
         FETCH fky_cur INTO fky_rec;
      END IF;
      CLOSE fky_cur;
      RETURN fky_rec.constraint_name IS NOT NULL;
   EXCEPTION
	   WHEN OTHERS
		THEN
		   IF fky_cur%ISOPEN
			THEN
			   CLOSE fky_cur;
			END IF;
   END;

   FUNCTION collist (tab IN VARCHAR2, 
      fky IN VARCHAR2, 
      sch IN VARCHAR2 := NULL,
      delim IN VARCHAR2 := ',')
   RETURN VARCHAR2
   IS
	   fkycol_rec fkycol_cur%ROWTYPE;
      retval PLGadmin.dbmaxvc2;
   BEGIN
      OPEN fkycol_cur (NVL (sch, USER), tab, fky);
      FETCH fkycol_cur INTO fkycol_rec;
      IF fkycol_cur%NOTFOUND
      THEN
         CLOSE fkycol_cur;
         OPEN fkycol_cur (NVL (sch, USER), UPPER (tab), fky);
         FETCH fkycol_cur INTO fkycol_rec;
      END IF;
      
      WHILE fkycol_cur%FOUND
      LOOP
         retval := retval || delim || fkycol_rec.column_name;
         FETCH fkycol_cur INTO fkycol_rec;
      END LOOP;
      CLOSE fkycol_cur;
      RETURN LTRIM (retval, delim);
   END;

END PLGfkys;                      
/
rem show errors