plgfkys.sps

Oracle PL/SQL procedure generator (second generator type)

CREATE OR REPLACE PACKAGE PLGfkys &authidopen AUTHID CURRENT_USER &authidclose

/*----------------------------------------------------------------
||                  PL/Vision Professional 
||----------------------------------------------------------------
||    File: PLGfkys.sps
||  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 ro ignore self ref fkys.
|| 10/97    SEF    Break out cursor to get correct results.
|| 6/97     SEF    Created.
******************************************************************/
IS
   TYPE fky_rectype IS RECORD
      (constraint_name ALL_CONSTRAINTS.CONSTRAINT_NAME%TYPE,
       constraint_type ALL_CONSTRAINTS.CONSTRAINT_TYPE%TYPE,
       table_name ALL_CONSTRAINTS.TABLE_NAME%TYPE,
       pky_owner ALL_CONSTRAINTS.OWNER%TYPE,
       pky_table_name ALL_TAB_COLUMNS.TABLE_NAME%TYPE,
       pky_constraint_name ALL_CONSTRAINTS.CONSTRAINT_NAME%TYPE,
       column_count INTEGER,
       starting_row INTEGER,
       cumulative_column_count INTEGER,
       comment PLGadmin.dbmaxvc2);

   TYPE fkycol_rectype IS RECORD
      (column_name ALL_TAB_COLUMNS.COLUMN_NAME%TYPE,
       position ALL_CONS_COLUMNS.POSITION%TYPE,
       abs_position PLS_INTEGER,
       pky_column_name ALL_TAB_COLUMNS.COLUMN_NAME%TYPE);
   
   TYPE fky_tabtype IS TABLE OF fky_rectype 
      INDEX BY BINARY_INTEGER;
         
   TYPE fkycol_tabtype IS TABLE OF fkycol_rectype 
      INDEX BY BINARY_INTEGER;
         
   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);

   FUNCTION nthcol 
      (fkycoltab IN fkycol_tabtype, 
       fkynum IN INTEGER := 1, 
       colnum IN INTEGER := 1) 
      RETURN fkycol_rectype;

   FUNCTION isfky (tab IN VARCHAR2, 
      fky IN VARCHAR2, 
      sch IN VARCHAR2 := NULL)
   RETURN BOOLEAN;

   FUNCTION collist (tab IN VARCHAR2, 
      fky IN VARCHAR2, 
      sch IN VARCHAR2 := NULL,
      delim IN VARCHAR2 := ',')
   RETURN VARCHAR2;

END PLGfkys;                      
/
rem show errors