plggen.sps

Oracle PL/SQL procedure generator (second generator type)

CREATE OR REPLACE PACKAGE PLGgen &authidopen AUTHID CURRENT_USER &authidclose

/* Table Package Generator */

/*----------------------------------------------------------------
||                  PL/Generator from RevealNet
||----------------------------------------------------------------
||    File: PLGgen.sps
||  Author: Steven Feuerstein
||
|| This is a part of the PL/Generator Code library.
|| Copyright (C) 1998-1999 RevealNet, Inc.
|| All rights reserved.
||
|| For more information, call RevealNet at 1-800-REVEAL4
|| or check out our Web page: www.revealnet.com
||
------------------ Modification History --------------------------
|| Date     By     Description
||	-------- ------ -----------------------------------------------
|| 3/00     SEF    Add [set]comment capability
|| 4/99     SEF    99.1.1 Revamp conditional logic
|| 1/4/99   SEF    Remove use of colrecdmlval
|| 9/98     SEF    Add support for code analysis (analyze_plsql)
|| 06/24/97 SEF    Enhance for foreign key name resolution.
|| 04/97    SEF    Created
-----------------------------------------------------------------*/
IS
   c_asis CONSTANT CHAR(1) := '\';
   c_asis_wild CONSTANT CHAR(2) := '\%';
   c_asis_translate CONSTANT CHAR(2) := 'A\';
   c_translate CONSTANT CHAR(1) := 'A';

   g_timer PLS_INTEGER := 0;

/* Public global areas containing object information */

   /* One Out List */
   oneOutList_t PLGadmin.vc2000_tabtype;

   TYPE code_target_t IS RECORD
      (fileid UTL_FILE.FILE_TYPE,
       listid PLS_INTEGER,
       templistid PLS_INTEGER,
       inuse BOOLEAN,
       listseq PLS_INTEGER,
       listnum PLS_INTEGER := 0
       );

   TYPE obj_rectype IS RECORD (
      owner ALL_OBJECTS.OWNER%TYPE,
      name ALL_OBJECTS.OBJECT_NAME%TYPE,
      type ALL_OBJECTS.OBJECT_TYPE%TYPE);

   TYPE obj_tabtype IS TABLE OF obj_rectype INDEX BY BINARY_INTEGER;

   dbobject obj_tabtype;

   /* Invalid identifiers list */
   TYPE invalid_ident_rectype IS RECORD (
      driver PLGadmin.identifier,
      owner PLGadmin.identifier,
      name PLGadmin.identifier,
      type PLGadmin.identifier,
      alias PLG_doir.info%TYPE,
      alias_description PLG_doir.doc%TYPE,
      ident PLGadmin.dbmaxvc2,
      new_ident PLGadmin.identifier
      );

   TYPE invalid_ident_tabtype IS TABLE OF invalid_ident_rectype
      INDEX BY BINARY_INTEGER;

   invalid_idents invalid_ident_tabtype;

   TYPE gencoltab IS TABLE OF PLGgenv.colrectype INDEX BY BINARY_INTEGER;

   /* Contains all column-level information */
   gencol gencoltab;

   /* Basic column information */
   col PLGcols.col_tabtype;

   /* Consolidate information in own tables. Communicate with gencol
      via a hash table on column name. */

   ind   PLGinds.ind_tabtype;
   indcol PLGinds.indcol_tabtype;

   uind   PLGinds.ind_tabtype;
   uindcol PLGinds.indcol_tabtype;

   fky    PLGfkys.fky_tabtype;
   fkycol PLGfkys.fkycol_tabtype;

   fkydesc    PLGinds.ind_tabtype;
   fkydesccol PLGinds.indcol_tabtype;

   pkycol  PLGpky.pky_tabtype;
   desccol PLGinds.indcol_tabtype;

   cons    PLGcons.cons_tabtype;
   conscol PLGcons.conscol_tabtype;

   curs PLGdoir.curinfo_tabtype;

   TYPE integer_tabtype IS TABLE OF PLS_INTEGER
       INDEX BY BINARY_INTEGER;

   /* Values in these tables point to rows in gencol */
   nonpkycol  integer_tabtype;
   updcol     integer_tabtype;
   notnullcol integer_tabtype;
   uconscol   integer_tabtype;

   loop_hash integer_tabtype;
   col_hash integer_tabtype;

   /* Code analysis tables. */
   prog PLGdesc.prog_tt;
   -- Not in use progsep PLGdesc.progsep_tt;
   arg PLGdesc.arglist_tt;
   retarg PLGdesc.arglist_tt;

   /* Stack of states for looping. */
   TYPE state_tab IS TABLE OF PLGgenv.genrectype INDEX BY BINARY_INTEGER;
   genstate state_tab;

   /* State stack structure */

   TYPE state_stk_type IS RECORD (
      statetype PLS_INTEGER,
      currrow PLS_INTEGER,
      nthrow PLS_INTEGER,
      startrow PLS_INTEGER,
      endrow PLS_INTEGER,
      startend_override BOOLEAN,
      statecommand PLGadmin.identifier,
      statevar PLGadmin.identifier,
      statelabel PLGadmin.identifier,
      pretext PLGadmin.identifier, /* opposite of between; on all but first, in front. */
      at_first_line BOOLEAN, /* TRUE if putting first line in loop */
      betweentext VARCHAR2(100),
      /* 2000.2 */
      appendtext VARCHAR2(100), /* text to be appended to EVERY line in loop */
      astext VARCHAR2(100),
      istrue BOOLEAN,
      pkgname PLGadmin.identifier /* for loops, stored in PLGdoir */
      --aftertext VARCHAR2(100),
      --beforetext VARCHAR2(100),
      --lstriptext VARCHAR2(100),
      --rstriptext VARCHAR2(100),
      );

   TYPE state_stk_tabtype IS TABLE OF state_stk_type INDEX BY BINARY_INTEGER;
   loop_stack state_stk_tabtype;

   empty_staterec state_stk_type;
   targetstate state_stk_type;
   ifstate state_stk_type;
   loopstate state_stk_type;
   inclstate state_stk_type;

   /* Constants */

   g_ident_length INTEGER := 30;

   /* These constants should really be in plgte!
      I will place a copy there for use with the setting of arrays. */
   c_colname CONSTANT CHAR(7) := 'COLNAME';
   c_pkycol CONSTANT CHAR(6) := 'PKYCOL';
   c_desccol CONSTANT CHAR(7) := 'DESCCOL';
   c_sequence CONSTANT CHAR(8) := 'SEQUENCE';
   c_nonpkycol CONSTANT CHAR(9) := 'NONPKYCOL';
   c_fky CONSTANT CHAR(3) := 'FKY';
   c_fkycol CONSTANT CHAR(6) := 'FKYCOL';
   c_fkydesc CONSTANT CHAR(7) := 'FKYDESC';
   c_fkydesccol CONSTANT CHAR(10) := 'FKYDESCCOL';
   c_updcol CONSTANT CHAR(6) := 'UPDCOL';
   c_notnullcol CONSTANT CHAR(10) := 'NOTNULLCOL';
   c_col CONSTANT CHAR(3) := 'COL';
   c_uind CONSTANT CHAR(4) := 'UIND';
   c_uindcol CONSTANT CHAR(7) := 'UINDCOL';
   c_ind CONSTANT CHAR(3) := 'IND';
   c_indcol CONSTANT CHAR(6) := 'INDCOL';
   c_cons CONSTANT CHAR(4) := 'CONS';
   c_conscol CONSTANT CHAR(7) := 'CONSCOL';
   c_uconscol CONSTANT CHAR(8) := 'UCONSCOL';

   c_prog       CONSTANT CHAR(4) := 'PROG';
   c_progsep    CONSTANT CHAR(7) := 'PROGSEP';
   c_arg        CONSTANT CHAR(3) := 'ARG';
   c_retarg     CONSTANT CHAR(6) := 'RETARG';
   c_argsep     CONSTANT CHAR(6) := 'ARGSEP';
   c_argfromsep CONSTANT CHAR(10) := 'ARGFROMSEP';

   /* CGML syntax elements */
   c_open       CONSTANT CHAR(1) := '[';
   c_close      CONSTANT CHAR(1) := ']';
   c_aliasopen  CONSTANT CHAR(1) := '{';
   c_aliasclose CONSTANT CHAR(1) := '}';
   c_idtag     CONSTANT CHAR(1) := '^';
   c_substopen  CONSTANT CHAR(1) := CHR(8);
   c_substclose CONSTANT CHAR(1) := CHR(9);

   c_foreach CONSTANT CHAR(7) := 'FOREACH';
   c_endforeach CONSTANT CHAR(10) := 'ENDFOREACH';
   c_endforeach2 CONSTANT CHAR(8) := '\FOREACH';

   /* Special set options */
   c_object CONSTANT CHAR(6) := 'OBJECT';
   c_driver CONSTANT CHAR(6) := 'DRIVER';
   c_driver_type CONSTANT CHAR(10) := 'DRIVERTYPE';
   c_aliastag CONSTANT CHAR(8) := 'ALIASTAG';
   c_objecttag CONSTANT CHAR(9) := 'OBJECTTAG';

   c_start_cust_line CONSTANT VARCHAR2(50) := '/* PL/Generator CUSTOMIZE START */';
   c_end_cust_line CONSTANT VARCHAR2(50) := '/* PL/Generator CUSTOMIZE END */';

   /* Conditional Logic Constants */
   c_if CONSTANT CHAR(2) := 'IF';
   c_ifnot CONSTANT CHAR(5) := 'IFNOT';
   c_else CONSTANT CHAR(4) := 'ELSE';
   c_elsif CONSTANT CHAR(5) := 'ELSIF';
   c_elsifnot CONSTANT CHAR(8) := 'ELSIFNOT';
   c_elseif CONSTANT CHAR(6) := 'ELSEIF';
   c_elseifnot CONSTANT CHAR(9) := 'ELSEIFNOT';
   c_endif CONSTANT CHAR(5) := 'ENDIF';
   c_endif2 CONSTANT CHAR(3) := '\IF';
   c_end_if CONSTANT CHAR(6) := 'END IF';

   c_eq CONSTANT CHAR(2) := 'EQ';
   c_neq CONSTANT CHAR(3) := 'NEQ';
   c_and CONSTANT CHAR(3) := 'AND';
   c_or CONSTANT CHAR(2) := 'OR';
   c_isnull CONSTANT CHAR(6) := 'ISNULL';
   c_isnotnull CONSTANT CHAR(9) := 'ISNOTNULL';
   c_like CONSTANT CHAR(4) := 'LIKE';
   c_in CONSTANT CHAR(2) := 'IN';
   c_notin CONSTANT CHAR(5) := 'NOTIN';
   c_delim CONSTANT CHAR(1) := ',';

   c_set CONSTANT CHAR(3) := 'SET';
   c_as CONSTANT CHAR(2) := 'AS';
   c_save CONSTANT CHAR(4) := 'SAVE';
   c_exec CONSTANT CHAR(4) := 'EXEC';
   c_execrto CONSTANT CHAR(7) := 'EXECRTO';
   c_execfile CONSTANT CHAR(8) := 'EXECFILE';
   c_append CONSTANT CHAR(6) := 'APPEND';
   c_stop CONSTANT CHAR(4) := 'STOP';

   c_asis_tag CONSTANT CHAR(4) := 'ASIS';
   c_endasis  CONSTANT CHAR(7) := 'ENDASIS';
   c_endasis2 CONSTANT CHAR(7) := '\ASIS';
   
   c_header       CONSTANT CHAR(6) := 'HEADER';
   c_include      CONSTANT CHAR(7) := 'INCLUDE';
   c_defarray     CONSTANT CHAR(8) := 'DEFARRAY';
   c_enddefarray  CONSTANT CHAR(11) := 'ENDDEFARRAY';
   c_enddefarray2 CONSTANT CHAR(9) := '\DEFARRAY';
   c_deftabarray  CONSTANT CHAR(11) := 'DEFTABARRAY';
   c_defqryarray  CONSTANT CHAR(11) := 'DEFQRYARRAY';
   c_storein      CONSTANT CHAR(7) := 'STOREIN';
   c_close_cmd    CONSTANT CHAR(5) := 'CLOSE';
   c_special      CONSTANT CHAR(7) := 'SPECIAL';
   c_raise        CONSTANT CHAR(5) := PLGdoir.c_raise;
   c_handle       CONSTANT CHAR(6) := PLGdoir.c_handle;
   c_totemp       CONSTANT CHAR(6) := 'TOTEMP';
   c_tofile       CONSTANT CHAR(6) := 'TOFILE';
   c_totarget     CONSTANT CHAR(8) := 'TOTARGET';
   c_rto          CONSTANT CHAR(3) := 'RTO';
   c_endtotemp    CONSTANT CHAR(9) := 'ENDTOTEMP';
   c_endtofile    CONSTANT CHAR(9) := 'ENDTOFILE';
   c_endtotarget CONSTANT CHAR(11) := 'ENDTOTARGET';
   c_endtotemp2   CONSTANT CHAR(7) := '\TOTEMP';
   c_endtofile2   CONSTANT CHAR(7) := '\TOFILE';
   c_endtotarget2 CONSTANT CHAR(9) := '\TOTARGET';
   c_endrto       CONSTANT CHAR(6) := 'ENDRTO';
   c_endrto2      CONSTANT CHAR(4) := '\RTO';
   c_setalias     CONSTANT CHAR(8) := 'SETALIAS';
   c_cleanup      CONSTANT CHAR(7) := 'CLEANUP';
   c_endcleanup   CONSTANT CHAR(10) := 'ENDCLEANUP';
   c_endcleanup2  CONSTANT CHAR(8) := '\CLEANUP';
   c_docleanup    CONSTANT CHAR(9) := 'DOCLEANUP';
   c_clearcleanup CONSTANT CHAR(12) := 'CLEARCLEANUP';
   c_setcomment   CONSTANT CHAR(7) := 'COMMENT';

   /* Not a great way to do this, but will do for now. Make sure to
      terminate the strings with a , */

   c_cgml_start_commands VARCHAR2(2000) :=
      'FOREACH,ENDFOREACH,\FOREACH,OBJECT,DRIVER,IF,IFNOT,' ||
      'ELSE,ELSIF,ELSIFNOT,ELSEIF,ELSEIFNOT,ENDIF,\IF,END IF,' ||
      'SET,EXEC,EXECRTO,EXECFILE,STOP,HEADER,INCLUDE,STOREIN,' ||
      'CLOSE,SPECIAL,RAISE,HANDLE,TOTEMP,TOFILE,TOTARGET,RTO,' ||
      'ENDTOTEMP,ENDTOFILE,ENDTOTARGET,\TOTEMP,\TOFILE,\TOTARGET,ENDRTO,\RTO,' ||
      'SETALIAS,DEFTABARRAY,DEFARRAY,DEFQRYARRAY,' ||
      'CLEANUP,ENDCLEANUP,DOCLEANUP,CLEARCLEANUP,ASIS,ENDASIS,\ASIS';

   c_cgml_embedded_commands VARCHAR2(2000) :=
      'EQ,NEQ,LIKE,AND,OR,ISNULL,ISNOTNULL,AS,APPEND,REPLACE,BETWEEN,' ||
      'STARTROW,ENDROW,SQLCODE,SQLERRM,SAVE';