plgplsql.spb

Oracle PL/SQL procedure generator (second generator type)

CREATE OR REPLACE PACKAGE BODY PLGplsql
IS

/* PL/SQL Code Driver Foundation */

/*----------------------------------------------------------------
||                  PL/Generator from Quest Software
||----------------------------------------------------------------
||    File: PLGplsql.spb
||  Author: Steven Feuerstein
||
|| This is a part of the PL/Generator Code library.
|| Copyright (C) 1998-1999 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
||	-------- ------ -----------------------------------------------
|| 9/98     SEF    Created from plgte.spb
-----------------------------------------------------------------*/
   c_pkg CONSTANT CHAR(8) := 'PLGplsql';
   c_delim CONSTANT CHAR(1) := CHR(8);

   PROCEDURE setfiles
   IS
   BEGIN
--------------------START OF PRODUCTION driver SET-------------------------

      PLGdoir.defprepsrc (
         driver,
         'psprep.gdr',
         tab => PLGdoir.c_global,
         sch => PLGdoir.c_global);

      PLGdoir.defdrvsrc (driver, 1, 'pssa.gdr',
         'Generates stand-alone programs around packaged programs',
         ftype => PLGdoir.c_ft_code,
         use_file => TRUE,
         tab => PLGdoir.c_global,
         sch => PLGdoir.c_global);
   END;

   PROCEDURE setidentifiers
   IS
   BEGIN
   /* Define the identifiers for this driver. The identifiers define the list
      of possible names on which you can set aliases. */

      /* Identifiers */

      --PLGdoir.set_progident (driver, 'NAME', 'CONSTRUCTION', 'DESCRIPTION');
      --PLGdoir.set_argident (driver, 'NAME', 'CONSTRUCTION', 'DESCRIPTION');

      /* Atomic naming units, such as "upd" */

      PLGdoir.set_ident (driver, 'sa_prefix', NULL,
         'Prefix placed in front of stand-alone programs');

      /* Define the tagged elements in the cgml for the driver */
      PLGdoir.set_tag (driver, 'objname', 'MYOBJ', 'Name of object');
   END;

   PROCEDURE setsrc
   IS
   BEGIN
      PLGdoir.setsrc (driver, 'ERRNUM', PLGdoir.c_driver_wide);
      PLGdoir.setsrc (driver, 'ERRMSG', PLGdoir.c_driver_wide);

      /* Define elements for the arrays */

      PLGdoir.set_array (driver, 'PROG', 
         doc=>'Array of programs defined in package or stand alone program');
      PLGdoir.set_array_tag (driver, 'PROG', 'pkgname', doc=>'The name of the package that contains this program, if applicable');
      PLGdoir.set_array_tag (driver, 'PROG', 'progname', doc=>'The name of the program');
      PLGdoir.set_array_tag (driver, 'PROG', 'progtype', doc=>'The type of program, PROCEDURE or FUNCTION');
      PLGdoir.set_array_tag (driver, 'PROG', 'isfunction', doc=>'True if program type is FUNCTION');
      PLGdoir.set_array_tag (driver, 'PROG', 'isprocedure', doc=>'True if program type is PROCEDURE');      
      PLGdoir.set_array_tag (driver, 'PROG', 'overload', doc=>'ber of the overloaded program (NULL if not overloaded)');
      PLGdoir.set_array_tag (driver, 'PROG', 'isoverloaded', doc=>'A Boolean field indicating whether or not this program is overloaded');
      PLGdoir.set_array_tag (driver, 'PROG', 'argcount', doc=>'The number of arguments in the program''s parameter list');
      PLGdoir.set_array_tag (driver, 'PROG', 'has_composite_arg', 
         doc=>'TRUE if at least one argument is a composite');
      PLGdoir.set_array_tag (driver, 'PROG', 'ret_data_type', 
         doc=>'Datatype of RETURN clause, if a function');
      PLGdoir.set_array_tag (driver, 'PROG', 'ret_has_composite', 
         doc=>'TRUE if RETURN clause has a composite, if a function');
      PLGdoir.set_array_tag (driver, 'PROG', 'ret_argcount', 
         doc=>'Number of arguments in the RETURN arguments array');

      PLGdoir.set_array (driver, 'ARG',
         doc=>'Array of arguments for programs - correlate with prog array');
      -- Any changes here, copy to RETARG...
      plgdoir.set_array_tag (driver, 'ARG', 'owner',doc=>'The schema that owns the program in which this argument appears');
      plgdoir.set_array_tag (driver, 'ARG', 'object_name',doc=>'The name of the program in which this argument appears');
      plgdoir.set_array_tag (driver, 'ARG', 'package_name',doc=>'The package that contains the program in which this argument appears');
      plgdoir.set_array_tag (driver, 'ARG', 'object_id',doc=>'The ID or identifier for the object');
      plgdoir.set_array_tag (driver, 'ARG', 'overload',doc=>'The Nth overloaded program in which this argument appears, 0 if no overloading');
      plgdoir.set_array_tag (driver, 'ARG', 'position',doc=>'The position of the argument in the parameter list');
      plgdoir.set_array_tag (driver, 'ARG', 'data_level',doc=>'The level of the parameter; can be greater than one if part of a composite argument');
      plgdoir.set_array_tag (driver, 'ARG', 'argument_name',doc=>'The name of the argument');
      plgdoir.set_array_tag (driver, 'ARG', 'data_type',doc=>'The name of the datatype');
      plgdoir.set_array_tag (driver, 'ARG', 'default_value',doc=>'The default value for the argument, if any');
      plgdoir.set_array_tag (driver, 'ARG', 'default_length',doc=>'The default length');
      plgdoir.set_array_tag (driver, 'ARG', 'in_out',doc=>'The name of the argument mode (IN, OUT or IN OUT)');
      plgdoir.set_array_tag (driver, 'ARG', 'is_in',doc=>'True if argument mode is IN');
      plgdoir.set_array_tag (driver, 'ARG', 'is_in_out',doc=>'True if argument mode is IN OUT');
      plgdoir.set_array_tag (driver, 'ARG', 'is_out',doc=>'True if argument mode is OUT');
      plgdoir.set_array_tag (driver, 'ARG', 'data_length',doc=>'Length of the argument datatype');
      plgdoir.set_array_tag (driver, 'ARG', 'data_precision',doc=>'Precision of the argument datatype');
      plgdoir.set_array_tag (driver, 'ARG', 'data_scale',doc=>'Scale of the argument datatype');
      plgdoir.set_array_tag (driver, 'ARG', 'radix',doc=>'Radix of the argument datatype');
      plgdoir.set_array_tag (driver, 'ARG', 'is_composite',doc=>'TRUE if the argument is a composite type (record, collection, object, etc.)');
      plgdoir.set_array_tag (driver, 'ARG', 'composite_name',doc=>'Fully qualified name of argument (a.b.c.d)');
      plgdoir.set_array_tag (driver, 'ARG', 'composite_type',doc=>'Type of composite argument');
      plgdoir.set_array_tag (driver, 'ARG', 'is_record',doc=>'TRUE if argument is a record');
      plgdoir.set_array_tag (driver, 'ARG', 'is_indexby_table',doc=>'TRUE if argument is an index by table');
      plgdoir.set_array_tag (driver, 'ARG', 'is_ref_cursor',doc=>'TRUE if argument is a a REF CURSOR/cursor variable');
      plgdoir.set_array_tag (driver, 'ARG', 'is_object',doc=>'TRUE if argument is an object');
      plgdoir.set_array_tag (driver, 'ARG', 'is_nested_table',doc=>'TRUE if argument is a nested table');
      plgdoir.set_array_tag (driver, 'ARG', 'is_varray',doc=>'TRUE if argument is a VARRAY');
         
      PLGdoir.set_array (driver, 'RETARG',
         doc=>'Array of arguments for RETURN clause of function');
      plgdoir.set_array_tag (driver, 'RETARG', 'owner',doc=>'The schema that owns the program in which this argument appears');
      plgdoir.set_array_tag (driver, 'RETARG', 'object_name',doc=>'The name of the program in which this argument appears');
      plgdoir.set_array_tag (driver, 'RETARG', 'package_name',doc=>'The package that contains the program in which this argument appears');
      plgdoir.set_array_tag (driver, 'RETARG', 'object_id',doc=>'The ID or identifier for the object');
      plgdoir.set_array_tag (driver, 'RETARG', 'overload',doc=>'The Nth overloaded program in which this argument appears, 0 if no overloading');
      plgdoir.set_array_tag (driver, 'RETARG', 'position',doc=>'The position of the argument in the parameter list');
      plgdoir.set_array_tag (driver, 'RETARG', 'data_level',doc=>'The level of the parameter; can be greater than one if part of a composite argument');
      plgdoir.set_array_tag (driver, 'RETARG', 'argument_name',doc=>'The name of the argument');
      plgdoir.set_array_tag (driver, 'RETARG', 'data_type',doc=>'The name of the datatype');
      plgdoir.set_array_tag (driver, 'RETARG', 'default_value',doc=>'The default value for the argument, if any');
      plgdoir.set_array_tag (driver, 'RETARG', 'default_length',doc=>'The default length');
      plgdoir.set_array_tag (driver, 'RETARG', 'in_out',doc=>'The name of the argument mode (IN, OUT or IN OUT)');
      plgdoir.set_array_tag (driver, 'RETARG', 'is_in',doc=>'True if argument mode is IN');
      plgdoir.set_array_tag (driver, 'RETARG', 'is_in_out',doc=>'True if argument mode is IN OUT');
      plgdoir.set_array_tag (driver, 'RETARG', 'is_out',doc=>'True if argument mode is OUT');
      plgdoir.set_array_tag (driver, 'RETARG', 'data_length',doc=>'Length of the argument datatype');
      plgdoir.set_array_tag (driver, 'RETARG', 'data_precision',doc=>'Precision of the argument datatype');
      plgdoir.set_array_tag (driver, 'RETARG', 'data_scale',doc=>'Scale of the argument datatype');
      plgdoir.set_array_tag (driver, 'RETARG', 'radix',doc=>'Radix of the argument datatype');
      plgdoir.set_array_tag (driver, 'RETARG', 'is_composite',doc=>'TRUE if the argument is a composite type (record, collection, object, etc.)');
      plgdoir.set_array_tag (driver, 'RETARG', 'composite_name',doc=>'Fully qualified name of argument (a.b.c.d)');
      plgdoir.set_array_tag (driver, 'RETARG', 'composite_type',doc=>'Type of composite argument');
      plgdoir.set_array_tag (driver, 'RETARG', 'is_record',doc=>'TRUE if argument is a record');
      plgdoir.set_array_tag (driver, 'RETARG', 'is_indexby_table',doc=>'TRUE if argument is an index by table');
      plgdoir.set_array_tag (driver, 'RETARG', 'is_ref_cursor',doc=>'TRUE if argument is a a REF CURSOR/cursor variable');
      plgdoir.set_array_tag (driver, 'RETARG', 'is_object',doc=>'TRUE if argument is an object');
      plgdoir.set_array_tag (driver, 'RETARG', 'is_nested_table',doc=>'TRUE if argument is a nested table');
      plgdoir.set_array_tag (driver, 'RETARG', 'is_varray',doc=>'TRUE if argument is a VARRAY');
         
      /* Not currently in use...
      PLGdoir.set_array (driver, 'PROGSEP',
         doc=>'Array of separated programs (distinct overloadings) defined in package');
      PLGdoir.set_array_tag (driver, 'PROGSEP', 'pkgname', doc=>'The name of the package that contains this program, if applicable');
      PLGdoir.set_array_tag (driver, 'PROGSEP', 'progname', doc=>'The name of the program');
      PLGdoir.set_array_tag (driver, 'PROGSEP', 'progtype', doc=>'The type of program, PROCEDURE or FUNCTION');
      PLGdoir.set_array_tag (driver, 'PROGSEP', 'overload', doc=>'ber of the overloaded program (NULL if not overloaded)');
      PLGdoir.set_array_tag (driver, 'PROGSEP', 'isfunction', doc=>'TRUE if the program type is FUNCTION');
      PLGdoir.set_array_tag (driver, 'PROGSEP', 'argcount', doc=>'The number of arguments in the program''s parameter list');
      PLGdoir.set_array_tag (driver, 'PROGSEP', 'haveargs', doc=>'TRUE if there is at least one argument for the program');
      PLGdoir.set_array_tag (driver, 'PROGSEP', 'using_composites', doc=>'TRUE if the program uses a composite data type for an argument or return datatype');

      PLGdoir.set_array (driver, 'ARGSEP',
         doc=>'Array of arguments for programs - correlate with progsep array');
      plgdoir.set_array_tag (driver, 'ARGSEP', 'overload',doc=>'The Nth overloaded program in which this argument appears, 0 if no overloading');
      plgdoir.set_array_tag (driver, 'ARGSEP', 'position',doc=>'The position of the argument in the parameter list');
      plgdoir.set_array_tag (driver, 'ARGSEP', 'level',doc=>'The level of the parameter; can be greater than one if part of a composite argument');
      plgdoir.set_array_tag (driver, 'ARGSEP', 'argument_name',doc=>'The name of the argument');
      plgdoir.set_array_tag (driver, 'ARGSEP', 'datatype',doc=>'The numeric code of the datatype of the argument');
      plgdoir.set_array_tag (driver, 'ARGSEP', 'datatype_name',doc=>'The name of the datatype');
      plgdoir.set_array_tag (driver, 'ARGSEP', 'default_value',doc=>'The default value for the argument, if any');
      plgdoir.set_array_tag (driver, 'ARGSEP', 'in_out',doc=>'The argument mode (numeric value)');
      plgdoir.set_array_tag (driver, 'ARGSEP', 'in_out_name',doc=>'The name of the argument mode (IN, OUT or IN OUT)');
      plgdoir.set_array_tag (driver, 'ARGSEP', 'is_in',doc=>'True if argument mode is IN');
      plgdoir.set_array_tag (driver, 'ARGSEP', 'is_in_out',doc=>'True if argument mode is IN OUT');
      plgdoir.set_array_tag (driver, 'ARGSEP', 'is_out',doc=>'True if argument mode is OUT');
      plgdoir.set_array_tag (driver, 'ARGSEP', 'length',doc=>'Length of the argument datatype');
      plgdoir.set_array_tag (driver, 'ARGSEP', 'precision',doc=>'Precision of the argument datatype');
      plgdoir.set_array_tag (driver, 'ARGSEP', 'scale',doc=>'Scale of the argument datatype');
      plgdoir.set_array_tag (driver, 'ARGSEP', 'radix',doc=>'Radix of the argument datatype');
      plgdoir.set_array_tag (driver, 'ARGSEP', 'is_composite',doc=>'TRUE if the argument is a composite type (record, collection, object, etc.)');
      plgdoir.set_array_tag (driver, 'ARGSEP', 'composite_name',doc=>'Fully qualified name of argument (a.b.c.d)');
      plgdoir.set_array_tag (driver, 'ARGSEP', 'composite_type',doc=>'Type of composite argument');
      plgdoir.set_array_tag (driver, 'ARGSEP', 'is_record',doc=>'TRUE if argument is a record');
      plgdoir.set_array_tag (driver, 'ARGSEP', 'is_indexby_table',doc=>'TRUE if argument is an index by table');
      plgdoir.set_array_tag (driver, 'ARGSEP', 'is_ref_cursor',doc=>'TRUE if argument is a a REF CURSOR/cursor variable');
      plgdoir.set_array_tag (driver, 'ARGSEP', 'is_object',doc=>'TRUE if argument is an object');
      plgdoir.set_array_tag (driver, 'ARGSEP', 'is_nested_table',doc=>'TRUE if argument is a nested table');
      plgdoir.set_array_tag (driver, 'ARGSEP', 'is_varray',doc=>'TRUE if argument is a VARRAY');
         
      PLGdoir.set_array (driver, 'ARGFROMSEP');
      */
      
      /* 
         Loop processing information: for offset loop, need a _START
         and _END value. This should be reworked!
      */

      PLGdoir.setdynvarchar2 (driver, 'ARG_START',
         'PLGgen.prog([\prog.currrow]).startrow');

      PLGdoir.setdynvarchar2 (driver, 'ARG_END',
         'PLGgen.prog([\prog.currrow]).endrow');

      PLGdoir.setdynvarchar2 (driver, 'ARGSEP_START',
         'PLGgen.progsep([\progsep.currrow]).startrow');

      PLGdoir.setdynvarchar2 (driver, 'ARGSEP_END',
         'PLGgen.progsep([\progsep.currrow]).endrow');

      PLGdoir.setdynvarchar2 (driver, 'RETARG_START',
         'PLGgen.prog([\prog.currrow]).ret_startrow');

      PLGdoir.setdynvarchar2 (driver, 'RETARG_END',
         'PLGgen.prog([\prog.currrow]).ret_endrow');

      /* Automatic with array...
      PLGdoir.setdynboolean (driver, 'HAVEARGS',
         'PLGgen.[\arrayname]([\currrow]).argcount > 0');
      */
      
      /* Program level information */

      /* NO LONGER IN USE - INFORMATION IN ARRAYS
      
      PLGdoir.setdynvarchar2 (driver, 'PROG_DATATYPE_NAME',
         'PLGplsql.convtype([objname],' ||
         'PLGdesc.g_datatype_names(PLGgen.progsep([\currrow]).datatype)');

      PLGdoir.setdynvarchar2 (driver, 'ORIG_PROG_DATATYPE_NAME',
         'PLGdesc.g_datatype_names(PLGgen.progsep([\currrow]).datatype)');

      PLGdoir.setdynvarchar2 (driver, 'PROG_DATATYPE_COMPOSITE_TYPE',
         'PLGplsql.composite_return (' ||
         '   [objname],' ||
         '   [progname],' ||
         '   PLGgen.progsep([\currrow]).overload,' ||
         '   [schema])');

      PLGdoir.setdynvarchar2 (driver, 'overload',
         'PLGadmin.ifelse(PLGgen.progsep([\progsep.currrow]).overload = 0, '''', ' ||
         'TO_CHAR(PLGgen.progsep([\progsep.currrow]).overload))');

      PLGdoir.setdynvarchar2 (driver, 'overload_NOT_NULL',
         'PLGgen.progsep([\progsep.currrow]).overload');

      PLGdoir.setdynboolean (driver, 'ISFUNCTION',
         'PLGgen.progsep([\progsep.currrow]).progtype = ''FUNCTION''');
      */
      
      PLGdoir.setdynboolean (driver, 'USING_COMPOSITES',
         'PLGplsql.using_composites([objname],[schema])');

      /* Argument-specific values. */

      PLGdoir.setdynvarchar2 (driver, 'TOPLSQL_VAL',
         'PLGplsql.toplsql_val(' ||
             '[schema],' ||
             '[objname],' ||
             'PLGgen.[\arrayname]([\currrow]).argument_name,' ||
             'PLGgen.[\arrayname]([\currrow]).datatype)');

      PLGdoir.setdynvarchar2 (driver, 'FROMPLSQL_VAL',
         'PLGplsql.fromplsql_val(' ||
             '[schema],' ||
             '[objname],' ||
             'PLGgen.[\arrayname]([\currrow]).argument_name,' ||
             'PLGgen.[\arrayname]([\currrow]).datatype)');

      PLGdoir.setdynvarchar2 (driver, 'IN_OUT_NAME',
         'PLGdesc.g_mode_names(PLGgen.argsep([\currrow]).in_out)');

      PLGdoir.setdynboolean (driver, 'IS_IN',
         'PLGgen.[\arrayname]([\currrow]).in_out = PLGdesc.c_in');

      PLGdoir.setdynboolean (driver, 'IS_IN_OUT',
         'PLGgen.[\arrayname]([\currrow]).in_out = PLGdesc.c_in_out');

      PLGdoir.setdynboolean (driver, 'IS_OUT',
         'PLGgen.[\arrayname]([\currrow]).in_out = PLGdesc.c_out');

      PLGdoir.setdynvarchar2 (driver, 'DATATYPE_NAME',