plgdesc.sps

Oracle PL/SQL procedure generator (second generator type)

CREATE OR REPLACE PACKAGE PLGdesc &authidopen AUTHID CURRENT_USER &authidclose
/*----------------------------------------------------------------
||                  PL/Vision Professional
||----------------------------------------------------------------
||    File: PLGdesc.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
||	-------- ------ -----------------------------------------------
|| 10/99    SEF    Re-architect to work directly from ALL_ARGUMENTS
||                 and not DBMS_DESCRIBE.
||
|| 7/98     SEF    Created for use in PL/Generator.
******************************************************************/
IS
   c_prog_max CONSTANT PLS_INTEGER := 2000;

   /* Core PL/SQL and SQL datatypes */
   c_varchar2       PLS_INTEGER := 1;
   c_nvarchar2      PLS_INTEGER := 1;
   c_number         PLS_INTEGER := 2;
   c_integer        PLS_INTEGER := 3;
   c_long           PLS_INTEGER := 8;
   c_varchar        PLS_INTEGER := 9;
   c_rowid          PLS_INTEGER := 11;
   c_date           PLS_INTEGER := 12;
   c_raw            PLS_INTEGER := 23;
   c_longraw        PLS_INTEGER := 24;
   c_binary_integer PLS_INTEGER := 29;
   c_pls_integer    PLS_INTEGER := 29;
   c_crowid         PLS_INTEGER := 69;
   c_char           PLS_INTEGER := 96;
   c_nchar          PLS_INTEGER := 96;
   c_ref_cursor     PLS_INTEGER := 102;
   c_mlslabel       PLS_INTEGER := 106;
   c_ref            PLS_INTEGER := 110;
   c_clob           PLS_INTEGER := 112;
   c_nclob          PLS_INTEGER := 112;
   c_blob           PLS_INTEGER := 113;
   c_bfile          PLS_INTEGER := 114;
   c_cfile          PLS_INTEGER := 115;
   c_object_type    PLS_INTEGER := 121;
   c_nested_table   PLS_INTEGER := 122;
   c_varray         PLS_INTEGER := 123;
   c_record         PLS_INTEGER := 250;
   c_indexby_table  PLS_INTEGER := 251;
   c_boolean        PLS_INTEGER := 252;

   /* Parameter modes */
   c_in CONSTANT PLS_INTEGER := 0;
   c_out CONSTANT PLS_INTEGER := 1;
   c_inout CONSTANT PLS_INTEGER := 2;

   /* Holds the descriptions of the datatypes */
   g_datatype_names DBMS_DESCRIBE.VARCHAR2_TABLE;

   /* Holds the descriptions of the parameter modes. */
   g_mode_names DBMS_DESCRIBE.VARCHAR2_TABLE;

   /* A single record holding all the information for one argument. 
      Most of these columns come from ALL_ARGUMENTS. */
   TYPE arglist_rt IS RECORD (
     owner PLGadmin.IDENTIFIER
    ,object_name PLGadmin.identifier
    ,package_name PLGadmin.identifier
    ,object_id ALL_ARGUMENTS.object_id%TYPE
    ,overload ALL_ARGUMENTS.overload%TYPE
    ,argument_name PLGadmin.identifier
    ,position ALL_ARGUMENTS.position%TYPE
    ,sequence ALL_ARGUMENTS.sequence%TYPE
    ,data_level ALL_ARGUMENTS.data_level%TYPE
    ,data_type ALL_ARGUMENTS.data_type%TYPE
    ,default_value PLGadmin.maxvc2
    ,default_length ALL_ARGUMENTS.default_length%TYPE
    ,in_out ALL_ARGUMENTS.in_out%TYPE
    ,data_length ALL_ARGUMENTS.data_length%TYPE
    ,data_precision ALL_ARGUMENTS.data_precision%TYPE
    ,data_scale ALL_ARGUMENTS.data_scale%TYPE
    ,radix ALL_ARGUMENTS.radix%TYPE
    ,CHARACTER_SET_NAME   VARCHAR2(44)         /* Oracle8 only */
    ,TYPE_OWNER           PLGadmin.identifier  /* Oracle8 only */
    ,TYPE_NAME            PLGadmin.identifier  /* Oracle8 only */        
    ,TYPE_SUBNAME         PLGadmin.identifier  /* Oracle8 only */     
    ,TYPE_LINK            VARCHAR2(128)        /* Oracle8 only */   
    ,startseprow PLS_INTEGER /* first row in separated list for this argument */
    ,endseprow PLS_INTEGER   /* last row in separated list for this argument */
    ,composite_name VARCHAR2(2000) /* a.b.c.d */
    ,is_composite BOOLEAN
    -- Avoid duplication ,level NUMBER
    -- Avoid duplication ,datatype NUMBER
    -- Avoid duplication ,length NUMBER
    -- Avoid duplication ,precision NUMBER
    -- Avoid duplication ,scale NUMBER
    );

   TYPE arglist_tt IS TABLE OF arglist_rt INDEX BY BINARY_INTEGER;

   /* Core program information based on DBMS_DESCRIBE. */
   TYPE prog_rt IS RECORD (
      pkgname PLGadmin.identifier,
      progname PLGadmin.identifier,
      progtype PLGadmin.identifier,
      isfunction BOOLEAN,
      isprocedure BOOLEAN,
      overload NUMBER,            /* nth overloading of program */
      isoverloaded BOOLEAN,      /* TRUE if this program is overloaded */
      startrow PLS_INTEGER,       /* Row of first argument */
      endrow PLS_INTEGER,         /* Row of last argument */
      argcount PLS_INTEGER,
      has_composite_arg BOOLEAN,
      /* Function related fields */
      ret_data_type ALL_ARGUMENTS.data_type%TYPE,
      ret_has_composite BOOLEAN,
      ret_startrow PLS_INTEGER,
      ret_endrow PLS_INTEGER,
      ret_argcount PLS_INTEGER,
      startseprow PLS_INTEGER,    /* Row of first separated program */
      endseprow PLS_INTEGER       /* Row of last separated program */
      );

   /* Fully-expanded program information. */
   TYPE progsep_rt IS RECORD (
      pkgname PLGadmin.identifier,
      progname PLGadmin.identifier,
      progtype PLGadmin.identifier,
      nonseprow PLS_INTEGER, /* pointer back to prog_rt list */
      overload NUMBER,  /* nth overloading of program */
      startrow PLS_INTEGER,  /* first separated argument row */
      endrow PLS_INTEGER,    /* last separated argument row */
      argcount PLS_INTEGER,   /* number of arguments */
      nonsepstartrow PLS_INTEGER,
      nonsepnonretstartrow PLS_INTEGER, /* First argument not part of RETURN */
      nonsependrow PLS_INTEGER,
      nonsepargcount PLS_INTEGER,
      has_composite_arg BOOLEAN,
      /* Function related fields */
      data_type NUMBER,
      ret_has_composite BOOLEAN,
      ret_startrow PLS_INTEGER,
      ret_endrow PLS_INTEGER,
      ret_argcount PLS_INTEGER
      );

   TYPE prog_tt IS TABLE OF prog_rt INDEX BY BINARY_INTEGER;
   TYPE progsep_tt IS TABLE OF progsep_rt INDEX BY BINARY_INTEGER;

   -- No longer using this... PROCEDURE args (obj IN VARCHAR2);

   PROCEDURE get_prog_info (
      sch IN VARCHAR2,
      pkg IN VARCHAR2,
      obj IN VARCHAR2,
      proglist IN OUT prog_tt,
      arglist IN OUT arglist_tt,
      retarglist IN OUT arglist_tt,
      progstart IN PLS_INTEGER := NULL,
      argstart IN PLS_INTEGER := NULL
      );
      
   /* Passes back argument information into specified list. */
   PROCEDURE args (
      sch IN VARCHAR2,
      pkg IN VARCHAR2,
      obj IN VARCHAR2,
      arglist_out IN OUT arglist_tt,
      startrow IN PLS_INTEGER := NULL
      );

   /* Displays all the argument information using DBMS_OUTPUT.PUT_LINE. */
   PROCEDURE showargs (obj IN VARCHAR2 := NULL);

   PROCEDURE obtain_args (
      sch IN VARCHAR2,
      pkg IN VARCHAR2,
      obj IN VARCHAR2,
      prog_out IN OUT prog_tt,
      arglist_out IN OUT arglist_tt
      );

   PROCEDURE filter_arguments (
      prog_out IN OUT prog_tt,
      arglist_out IN OUT arglist_tt,
      progsep_out IN OUT progsep_tt,
      argseplist_out IN OUT arglist_tt
      );

   PROCEDURE separate_progs_and_args (
      prog_out IN OUT prog_tt,
      arglist_out IN OUT arglist_tt,
      progsep_out IN OUT progsep_tt,
      argseplist_out IN OUT arglist_tt
      );

   /* Returns list of distinct program units in specified object. */

   FUNCTION progs (
      sch IN VARCHAR2,
      pkg IN VARCHAR2,
      obj IN VARCHAR2 ) RETURN prog_tt;

   PROCEDURE progs (
      sch IN VARCHAR2,
      pkg IN VARCHAR2,
      obj IN VARCHAR2,
      progtab IN OUT prog_tt,
      startrow IN PLS_INTEGER := NULL
      );

   PROCEDURE showprogs (
      prog_in IN prog_tt
      );
      
   PROCEDURE showprogs (
      prog_in IN prog_tt,
      args_in IN arglist_tt,
      progsep_in IN progsep_tt,
      argsep_in IN arglist_tt,
      progstart IN PLS_INTEGER := NULL,
      progend IN PLS_INTEGER := NULL,
      argstart IN PLS_INTEGER := NULL,
      argend IN PLS_INTEGER := NULL
      );

   /* Returns true if the datatype is a composite based on a TYPE
      statement (index-by table, record, ref cursor, etc.). */
   FUNCTION based_on_type (dtype IN INTEGER) RETURN BOOLEAN;
   FUNCTION based_on_type (dtype IN VARCHAR2) RETURN BOOLEAN;

END PLGdesc;
/