plgcons.sps

Oracle PL/SQL procedure generator (second generator type)

CREATE OR REPLACE PACKAGE PLGcons &authidopen AUTHID CURRENT_USER &authidclose

/*----------------------------------------------------------------
||                  PL/Vision Professional 
||----------------------------------------------------------------
||    File: PLGcons.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
||
|| 03/17/99 SEF Add identtag to fortab parm list so that the col names
||              can be validated.
-----------------------------------------------------------------*/
IS
   c_primary_key CONSTANT CHAR(1) := 'P';
   c_foreign_key CONSTANT CHAR(1) := 'R';
   c_check       CONSTANT CHAR(1) := 'C';

   c_enabled CONSTANT CHAR(7) := 'ENABLED';
   c_disabled CONSTANT CHAR(8) := 'DISABLED';

   c_max_converted_length CONSTANT INTEGER := 26;

   TYPE cons_rectype IS RECORD
      (constraint_name ALL_CONSTRAINTS.CONSTRAINT_NAME%TYPE,
       converted_constraint_name ALL_CONSTRAINTS.CONSTRAINT_NAME%TYPE,
       constraint_type ALL_CONSTRAINTS.CONSTRAINT_TYPE%TYPE,
       table_name ALL_CONSTRAINTS.TABLE_NAME%TYPE,
       search_condition ALL_CONSTRAINTS.SEARCH_CONDITION%TYPE,
       converted_search_condition ALL_CONSTRAINTS.SEARCH_CONDITION%TYPE,
       r_owner ALL_CONSTRAINTS.R_OWNER%TYPE,
       r_constraint_name ALL_CONSTRAINTS.R_CONSTRAINT_NAME%TYPE,
       delete_rule ALL_CONSTRAINTS.DELETE_RULE%TYPE,
       status ALL_CONSTRAINTS.STATUS%TYPE,
       not_null BOOLEAN,
       column_count INTEGER,
       cumulative_column_count INTEGER,
       starting_row INTEGER);

   TYPE cons_tabtype IS TABLE OF cons_rectype INDEX BY BINARY_INTEGER;

   TYPE conscol_rectype IS RECORD
      (column_name ALL_CONS_COLUMNS.COLUMN_NAME%TYPE,
       converted_column_name ALL_CONS_COLUMNS.COLUMN_NAME%TYPE,
       abs_position PLS_INTEGER);
   
   TYPE conscol_tabtype IS TABLE OF conscol_rectype 
      INDEX BY BINARY_INTEGER;

   TYPE ident_tabtype IS TABLE OF VARCHAR2(100) 
      INDEX BY BINARY_INTEGER;
         
   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
      );

   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
      );

   FUNCTION nthcol 
      (coltab IN conscol_tabtype, consnum IN INTEGER, colnum IN INTEGER)
      RETURN conscol_rectype;

   FUNCTION isconscol 
      (coltab IN conscol_tabtype, colname IN VARCHAR2)
      RETURN BOOLEAN;

END PLGcons;                      
/
rem show errors