plggen.tst

Oracle PL/SQL procedure generator (second generator type)

SET DEFINE OFF
CREATE OR REPLACE PACKAGE BODY PLGgen
IS
/*----------------------------------------------------------------
||                  PL/Generator from RevealNet
||----------------------------------------------------------------
||    File: PLGgen.spb
||  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
|| -------- ------ -----------------------------------------------
|| 6/10/99  SEF    Veva's birthday! Add support for NEQ
|| 6/10/99  SEF    Veva's birthday! Fix to subst_string calls to
||                 pass on the context, usually curlooprow.
|| 5/30/99  SEF    LTRIM iscomment.
|| 4/99     SEF    99.2 Move to single pass architecture
|| 4/99     SEF    99.1 Revamp conditional logic
|| 3-4/99   SEF    MANY changes for cgml exposure, check for trial version.
|| 1/7/99   SEF    Disable auto compilation capability. There are
||                 numerous problems.
|| 1/4/99   SEF    Adjust column deletions
|| 11/25/98 SEF    Major tuning and revamping - use of drv_array.
|| 11/98    SEF    Improve DML value processing (setdmlval).
|| 11/98    SEF    Strip out before and after from program calls to tune.
|| 9/24/98  SEF    Make sure that all calls to PLGdoir.alias passes the schema.
|| 9/98     SEF    Betweentext: only one allowed active in N nested loops.
||                 In other words, if you specify a BETWEEN it replaces
||                 any of the others.
|| 9/98     SEF    Add [exec] operand to language.
|| 9/98     SEF    Add support for code analysis (analyze_plsql) AND
||                 then move it to PLGplsql package.
|| 8/98     SEF    Remove reliance on column hash.
|| 5/98     SEF    Add support for encryption
|| 4/98     SEF    - Add support for unique cons columns to avoid
||                 invalid parameter list.
||                 - Allow prep file to have INCLUDE statements
||                 that are ignored.
|| 3/98     SEF    Put specialized prep and cust file handling in here.
||                 They are no longer regular driver files.
|| 1/98     SEF    Tune processing
|| 11/97    SEF    driver files placed in OIR. Clean up interface.
|| 06/24/97 SEF    Enhance for foreign key name resolution.
|| 04/97    SEF    Created: En route to Amsterdam
******************************************************************/

/* Constants and Global data */

   g_is_express BOOLEAN := FALSE;

   g_display BOOLEAN := FALSE;
   g_timer_start PLS_INTEGER := 0;
   g_useOneOutList BOOLEAN := FALSE;
   g_load_datasource BOOLEAN := TRUE;
   g_cleanup PLGadmin.vc2000_tabtype;
   
   c_schema CONSTANT PLGadmin.identifier := USER;

   plsql_compile_error EXCEPTION;
      PRAGMA EXCEPTION_INIT (plsql_compile_error , -6550);

   stop_generation EXCEPTION;

   /* Used to detect infinite loops in the parsing engine. */
   g_maxiterations PLS_INTEGER := 10000;

   no_columns_found EXCEPTION;

   c_targetstate CONSTANT INTEGER := 0;
   c_loopstate CONSTANT INTEGER := 2;
   c_ifstate   CONSTANT INTEGER := 3;
   c_inclstate CONSTANT INTEGER := 4;

   /* Tagged elements for parsing */
   vt_isnull PLGadmin.identifier;
   vt_isnotnull PLGadmin.identifier;
   vt_append PLGadmin.identifier;
   vt_replace PLGadmin.identifier;
   vt_as PLGadmin.identifier;
   vt_between PLGadmin.identifier;
   vt_startrow PLGadmin.identifier;
   vt_endrow PLGadmin.identifier;
   vt_sqlcode PLGadmin.identifier;
   vt_sqlerrm PLGadmin.identifier;
   vt_eq PLGadmin.identifier;
   vt_eq_len PLS_INTEGER := 4;
   vt_neq PLGadmin.identifier;
   vt_neq_len PLS_INTEGER := 5;
   vt_like PLGadmin.identifier;
   vt_like_len PLS_INTEGER := 6;
   vt_in PLGadmin.identifier;
   vt_in_len PLS_INTEGER := 4;
   vt_notin PLGadmin.identifier;
   vt_notin_len PLS_INTEGER := 7;
   vt_or PLGadmin.identifier;
   vt_and PLGadmin.identifier;
   vt_or_len PLS_INTEGER := 4;
   vt_and_len PLS_INTEGER := 5;
   vt_colname PLGadmin.identifier;

   c_listOFlists CONSTANT CHAR(13) := 'PLG$tempareas';
   g_listOFlists PLS_INTEGER;

   /* Open/Close characters */
   g_objtagopen CHAR(1)  := c_open;
   g_objtagclose CHAR(1) := c_close;
   g_idtag CHAR(1)      := c_idtag;
   g_aliasopen CHAR(1)  := c_aliasopen;
   g_aliasclose CHAR(1) := c_aliasclose;

   g_dbg BOOLEAN := FALSE;
   g_dbgsrc INTEGER := NULL;
   g_dbgfile PLGadmin.dbmaxvc2 := NULL;

   g_autocommit BOOLEAN := FALSE;
   g_line# PLS_INTEGER; -- current line number
   g_check_idents BOOLEAN := TRUE;

   g_drvsrctype PLS_INTEGER := PLGdoir.c_dbtab;
   g_drvtrgtype PLS_INTEGER := PLGdoir.c_file;

   dyncur INTEGER;

   g_compile BOOLEAN := FALSE;
   g_program_string PLGadmin.maxvc2;

   /* Identifier sequence/uniqueness values. */
   g_ident_seq_len CONSTANT PLS_INTEGER := 3;
   g_ident_seq_max CONSTANT PLS_INTEGER := 999;
   g_ident_seq PLS_INTEGER;

   /* Locations of drivers and generated code. */
   g_drvdir PLGadmin.dbmaxvc2;
   g_drvdelim PLGadmin.dbmaxvc2;

   g_gendir PLGadmin.dbmaxvc2;
   g_gendelim PLGadmin.dbmaxvc2;

   /* Driver Array and Information */

   TYPE drv_array_rectype IS RECORD (
      original_line PLS_INTEGER,
      text PLGadmin.dbmaxvc2,
      ut_text PLGadmin.dbmaxvc2,
      firstTag PLGadmin.identifier,
      firstTagLoc PLS_INTEGER,
      firstNonSpace PLS_INTEGER,
      next_row PLS_INTEGER,
      prev_row PLS_INTEGER
      );

   TYPE drv_array_tabtype IS TABLE OF drv_array_rectype INDEX BY BINARY_INTEGER;
   drv_array drv_array_tabtype;

   v_drv_array_encrypted BOOLEAN;
   v_drv_max CONSTANT PLS_INTEGER := 100000;

   TYPE drv_info_t IS TABLE OF plgdoir.drvrec_t
      INDEX BY BINARY_INTEGER;
   drv_info drv_info_t;

   /* Data structures for driver source array. */
   c_global_cust_seq CONSTANT PLS_INTEGER := -10;
   c_schema_cust_seq CONSTANT PLS_INTEGER := -20;
   c_object_cust_seq CONSTANT PLS_INTEGER := -30;

   /* Offset sequence number for includes. */
   c_include_seq_start CONSTANT PLS_INTEGER := -31;
   g_include_seq PLS_INTEGER := c_include_seq_start;
   g_last_drv_array_row PLS_INTEGER;

   g_storing_text BOOLEAN := FALSE;
   g_writeto code_target_t;

   /* Object Cache */
   /* Disabled
   TYPE aliascache_rectype IS RECORD (
      inuse BOOLEAN := FALSE,
      currinuse BOOLEAN := FALSE,
      delim CHAR(1),
      list PLGadmin.maxvc2,
      nmtab PLGadmin.vc2000_tabtype,
      sctab PLGadmin.vc2000_tabtype
      );

   g_aliascache aliascache_rectype;
   */

   PROCEDURE tmr_start IS BEGIN g_timer_start := DBMS_UTILITY.GET_TIME; END;
   PROCEDURE tmr_end IS BEGIN
      g_timer := g_timer + DBMS_UTILITY.GET_TIME - g_timer_start; END;

   /* Local substitute. */
   PROCEDURE pl (str IN VARCHAR2)
   IS
   BEGIN
      IF LENGTH (str) > 80
      THEN
         DBMS_OUTPUT.PUT_LINE (substr (str, 1, 80));
         pl (substr (str, 81));
      ELSE
         DBMS_OUTPUT.PUT_LINE (str);
      END IF;
   END;

   FUNCTION escaping (str IN VARCHAR2) RETURN BOOlEAN
   IS BEGIN RETURN str LIKE '!%'; END;

   /* Scan gencol table for matching column. */
   FUNCTION gencol_row (col IN VARCHAR2) RETURN PLS_INTEGER
   IS
      retval PLS_INTEGER  := gencol.FIRST;
   BEGIN
      LOOP
         EXIT WHEN retval IS NULL OR col = gencol(retval).colname;
         retval := gencol.NEXT (retval);
      END LOOP;
      RETURN retval;
   END;

/* Local valbyname function - bypass PLGvar */
   FUNCTION valbyname (
      str IN VARCHAR2, cur IN INTEGER := NULL) RETURN VARCHAR2
   IS
      fdbk PLS_INTEGER;
      retval PLGadmin.dbmaxvc2;
   BEGIN
      DBMS_SQL.parse (dyncur, 'BEGIN :val := ' || str || '; END;',
         DBMS_SQL.native);
      DBMS_SQL.BIND_VARIABLE (dyncur, 'val', 'a', 2000);
      fdbk := DBMS_SQL.execute (dyncur);
      DBMS_SQL.VARIABLE_VALUE (dyncur, 'val', retval);
      RETURN retval;
   END;

   PROCEDURE dynproc (str IN VARCHAR2, showerr IN BOOLEAN := TRUE)
   IS
      fdbk PLS_INTEGER;
   BEGIN
      DBMS_SQL.parse (dyncur, 'BEGIN ' || str || '; END;',
         DBMS_SQL.native);
      fdbk := DBMS_SQL.execute (dyncur);
   EXCEPTION
      WHEN OTHERS
      THEN
         IF showerr THEN pl ('Dynproc failure: ' || SQLERRM); END IF;
   END;

/* Directory and delimiter information */

   PROCEDURE setgenenv (
      drv IN VARCHAR2,
      sch IN VARCHAR2,
      tab IN VARCHAR2
      )
   IS
   BEGIN
      g_gendir := PLGdoir.gendir (drv, tab, sch);

      g_gendelim := PLGdoir.gendelim (drv, tab, sch);

      PLGerr.assert (
         g_gendir IS NOT NULL,
         'You must specify a generation directory with PLGdoir.setgendir.'
         );
   END;

   PROCEDURE setdrvenv (
      drv IN VARCHAR2,
      sch IN VARCHAR2,
      tab IN VARCHAR2
      )
   IS
   BEGIN
      g_drvdir := PLGdoir.drvdir (drv, tab, sch);

      g_drvdelim := PLGdoir.drvdelim (drv, tab, sch);

      PLGerr.assert (
         g_drvdir IS NOT NULL,
         'You must specify a driver directory with a call to PLGdoir.setdrvdir.'
         );
   END;

/* Toggles */

   PROCEDURE autocommit IS BEGIN g_autocommit := TRUE; END;
   PROCEDURE noautocommit IS BEGIN g_autocommit := FALSE; END;
   FUNCTION autocommitting RETURN BOOLEAN IS BEGIN RETURN g_autocommit; END;

   /* Debug mechanism */

   PROCEDURE dbg (
      srcnum IN INTEGER := 0,
      file IN VARCHAR2 := NULL)
   IS
   BEGIN
      g_dbg := TRUE;
      g_dbgsrc := srcnum;
      g_dbgfile := NULL;
   END;

   PROCEDURE setdbg (srcnum IN INTEGER := 0)
   IS
   BEGIN
      g_dbg := NVL (g_dbgsrc, -9999) = srcnum;
   END;

   PROCEDURE nodbg IS
   BEGIN
      g_dbg := FALSE;
      g_dbgsrc := NULL;
      g_dbgfile := NULL;
   END;

   FUNCTION debugging (srcnum IN INTEGER := 0)
      RETURN BOOLEAN
   IS
   BEGIN
      RETURN g_dbg OR (g_dbgsrc = srcnum);
   END;

   PROCEDURE set_maxiterations (num IN INTEGER)
   IS BEGIN g_maxiterations := NVL (num, 100000); END;
   FUNCTION maxiterations RETURN INTEGER IS BEGIN RETURN g_maxiterations; END;

/* Private Programs */

   FUNCTION writing_dbtab RETURN BOOLEAN
   IS
   BEGIN
      RETURN NOT OneOutList AND
             (is_express_version OR g_drvtrgtype = PLGdoir.c_dbtab);
   END;

   FUNCTION writing_file RETURN BOOLEAN
   IS
   BEGIN
      RETURN NOT OneOutList AND
             (NOT is_express_version AND g_drvtrgtype = PLGdoir.c_file);
   END;

   FUNCTION writing_list RETURN BOOLEAN
   IS
   BEGIN
      RETURN OneOutList OR
             (NOT is_express_version AND g_drvtrgtype = PLGdoir.c_list);
   END;

   FUNCTION reading_dbtab RETURN BOOLEAN
   IS
   BEGIN
      RETURN NOT OneOutList AND
             (is_express_version OR g_drvsrctype = PLGdoir.c_dbtab);
   END;

   FUNCTION reading_file RETURN BOOLEAN
   IS
   BEGIN
      RETURN NOT OneOutList AND
             (NOT is_express_version AND g_drvsrctype = PLGdoir.c_file);
   END;

   FUNCTION reading_list RETURN BOOLEAN
   IS
   BEGIN
      RETURN OneOutList OR
             (NOT is_express_version AND g_drvsrctype = PLGdoir.c_list);
   END;

   FUNCTION curlooprow (
      loop_in IN VARCHAR2,
      row_in IN PLS_INTEGER,
      pkg_in IN VARCHAR2 := 'PLGgen') RETURN VARCHAR2
   IS
   BEGIN
      RETURN pkg_in || '.' || loop_in || '(' || row_in ||').';
   END;

   FUNCTION curlooprow RETURN VARCHAR2
   IS
   BEGIN
      RETURN curlooprow (
         loopstate.statevar, loopstate.currrow, loopstate.pkgname);
   END;

   PROCEDURE init_src_trg_info (
      drv IN VARCHAR2,
      tab IN VARCHAR2,
      sch IN VARCHAR2 := NULL)
   IS
   BEGIN
      /* Set the source for the driver and target for the generated code. */

      g_drvsrctype :=
         NVL (
            PLGdoir.drvsrctype (drv, tab, sch), g_drvsrctype
            );

      g_drvtrgtype :=
         NVL (
            PLGdoir.drvtrgtype (drv, tab, sch), g_drvtrgtype
            );

      /* CANCELED: 99.2 Always do this -- customization files
         have to read from file.
      */
      IF g_drvsrctype = PLGdoir.c_file
      THEN
         setdrvenv (drv, sch, tab);
      END IF;

      IF g_drvtrgtype = PLGdoir.c_file
      THEN
         setgenenv (drv, sch, tab);
      END IF;

   END;