plgdoir.sps

Oracle PL/SQL procedure generator (second generator type)

CREATE OR REPLACE
PACKAGE PLGdoir &authidopen AUTHID CURRENT_USER &authidclose
/* Interface to Driver-Based Object Information Repository */

/*----------------------------------------------------------------
||                  PL/Generator from Quest Software
||----------------------------------------------------------------
||    File: plgdoir.sps
||  Author: Steven Feuerstein
||
|| This is a part of the PL/Generator from Quest Software 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
|| -------- ------ -----------------------------------------------
|| 3/99     SEF    Add PLGalias to avoid naming conflicts.
|| 3/99     SEF    Fix del procedure to avoid deletes of wrong drivers.
|| 11/1/98  SEF    Add just_like_drv function and support for aliased
||                 drivers.
|| 10/98    SEF    Add support for header source.
|| 04/98    SEF    Add admin user capability.
|| 06/97    SEF    Created.
******************************************************************/

IS
   /* Tuning options */
   minalias BOOLEAN := TRUE;

   c_plg_alias_prefix CONSTANT CHAR(5) := 'PLG>>';

   /* Boolean logic through string values */
   c_true CONSTANT CHAR(1) := 'T';
   c_false CONSTANT CHAR(1) := 'F';
   c_truefalse CONSTANT CHAR(3) := 'T,F';
   c_global CONSTANT CHAR(4) := '*ALL';
   c_delim CONSTANT CHAR(1) := CHR (8);

   /* Info Types known to PLGgen */

   c_objname CONSTANT CHAR(7)   := 'OBJNAME';
   c_jobjname CONSTANT CHAR(8)  := 'JOBJNAME';
   c_dbobjname CONSTANT CHAR(9) := 'DBOBJNAME';
   c_target CONSTANT CHAR(6)    := 'TARGET';
   c_schema CONSTANT CHAR(6)    := 'SCHEMA';
   c_dbschema CONSTANT CHAR(8)  := 'DBSCHEMA';
   c_dbobjname CONSTANT CHAR(9) := 'DBOBJNAME';
   c_target_schema CONSTANT CHAR(13) := 'TARGET_SCHEMA';

   c_table CONSTANT CHAR(5) := 'TABLE';
   c_column CONSTANT CHAR(6) := 'COLUMN';
   c_pkydesc CONSTANT CHAR(7) := 'PKYDESC';
   c_fky CONSTANT CHAR(3) := 'FKY';
   c_pky CONSTANT CHAR(3) := 'PKY';
   c_pkycol CONSTANT CHAR(6) := 'PKYCOL';
   c_colfunc CONSTANT CHAR(7) := 'COLFUNC';
   c_company CONSTANT CHAR(7) := 'COMPANY';
   c_preload CONSTANT CHAR(7) := 'PRELOAD';
   c_loadnone CONSTANT CHAR(8) := 'LOADNONE';
   c_loadall CONSTANT CHAR(7) := 'LOADALL';
   c_loadincr CONSTANT CHAR(8) := 'LOADINCR';
   c_genpky CONSTANT CHAR(6) := 'GENPKY';
   c_genpkyonly CONSTANT CHAR(10) := 'GENPKYONLY';
   c_sequence   CONSTANT CHAR(7)  := 'SEQNAME';
   c_pkygenproc CONSTANT CHAR(10) := 'PKYGENPROC';
   c_pkygenfunc CONSTANT CHAR(10) := 'PKYGENFUNC';
   c_usingxmn CONSTANT CHAR(8) := 'USINGXMN';
   c_fromod2k CONSTANT CHAR(8) := 'FROMOD2K';
   c_hidden CONSTANT CHAR(6) := 'HIDDEN';
   c_updcol CONSTANT CHAR(6) := 'UPDCOL';
   c_lookup CONSTANT CHAR(6) := 'LOOKUP';
   c_appprefix CONSTANT CHAR(9) := 'APPPREFIX';
   c_tabforview CONSTANT CHAR(10) := 'TABFORVIEW';
   c_reggen CONSTANT CHAR(6) := 'REGGEN';
   c_alias CONSTANT CHAR(5) := 'ALIAS';
   c_plgadmin CONSTANT CHAR(8) := 'PLGADMIN';
   c_array CONSTANT CHAR(5) := 'ARRAY';
   c_arraytag CONSTANT CHAR(8) := 'ARRAYTAG';
   c_test_sequence CONSTANT CHAR(12) := 'TEST_SEQNAME';
   c_comment CONSTANT CHAR(12)        := 'COMMENTCHARS';

   /* Must keep in lower case; see set_array_oper */
   c_array_prefix CONSTANT CHAR(3) := 'ga#';
   c_array_prefix_len CONSTANT PLS_INTEGER := 3;
   c_array_package_prefix CONSTANT CHAR(3) := 'gp#';
   c_array_view_prefix CONSTANT CHAR(3) := 'gv#';

   /* Driver database sources */
   c_table_dbsrc CONSTANT CHAR(5) := 'TABLE';
   c_plsql_dbsrc CONSTANT CHAR(5) := 'PLSQL';
   c_java_dbsrc CONSTANT CHAR(4) := 'JAVA';

   /* Exception Handling record type and constants. */
   TYPE exc_rectype IS RECORD (
       exctype PLGadmin.identifier,
       raisemech PLGadmin.dbmaxvc2,
       handlemech PLGadmin.dbmaxvc2,
       setcontext BOOLEAN,
       successval PLGadmin.identifier,
       failureval PLGadmin.identifier,
       failuremsg PLGadmin.dbmaxvc2
      );

   c_exchdlg CONSTANT CHAR(7) := 'EXCHDLG';
   c_psexc CONSTANT CHAR(5) := 'PSEXC';
   c_plvexc CONSTANT CHAR(6) := 'PLVEXC';
   c_retcd CONSTANT CHAR(5) := 'RETCD';
   c_raise CONSTANT CHAR(5) := 'RAISE';
   c_handle CONSTANT CHAR(6) := 'HANDLE';
   c_plvraise CONSTANT CHAR(8) := 'PLVRAISE';
   c_plvhandle CONSTANT CHAR(9) := 'PLVHANDLE';
   c_psraise CONSTANT CHAR(7) := 'PSRAISE';
   c_pshandle CONSTANT CHAR(8) := 'PSHANDLE';
   c_rcraise CONSTANT CHAR(7) := 'RCRAISE';
   c_rchandle CONSTANT CHAR(8) := 'RCHANDLE';
   c_successval CONSTANT CHAR(10) := 'SUCCESSVAL';
   c_failureval CONSTANT CHAR(10) := 'FAILUREVAL';
   c_failuremsg CONSTANT CHAR(10) := 'FAILUREMSG';
   c_usecurvar CONSTANT CHAR(9) := 'USECURVAR';
   c_usesaprogs CONSTANT CHAR(10) := 'USESAPROGS';
   c_usecountrows CONSTANT CHAR(12) := 'USECOUNTROWS';
   c_restrictref CONSTANT CHAR(11)  := 'RESTRICTREF';
   c_objabbrev CONSTANT CHAR(9) := 'OBJABBREV';
   c_useobjabbrev CONSTANT CHAR(23) := 'USEOBJABBREV';
   c_curinfo CONSTANT CHAR(7) := 'CURINFO';
   c_sfu CONSTANT CHAR(3) := 'SFU';
   c_updpky CONSTANT CHAR(6) := 'UPDPKY';
	c_usestdhdr CONSTANT CHAR(9) := 'USESTDHDR';
	c_queryonly CONSTANT CHAR(9) := 'QUERYONLY';

   /* 2000.1.2 Ability to turn off gen of validation code */
	c_CHECKDI CONSTANT CHAR(7) := 'CHECKDI';

   /* 2000.2.5 Dynamic vs Static SQL */
   c_usestaticSQL CONSTANT CHAR(12) := 'USESTATICSQL';
   
   c_drvsrctype CONSTANT CHAR(10) := 'DRVSRCTYPE';
   c_drvtrgtype CONSTANT CHAR(10) := 'DRVTRGTYPE';
   c_file CONSTANT PLS_INTEGER := 1;
   c_dbtab CONSTANT PLS_INTEGER := 2;
   c_list CONSTANT PLS_INTEGER := 3;

   /* Driver Structure record type and constants. */
   TYPE src_rectype IS RECORD (
       driver PLG_doir.driver%TYPE,
       srcstring PLGadmin.identifier,
       srctype PLGadmin.identifier,
       plsql_command PLGadmin.dbmaxvc2,
       returntype PLGadmin.identifier,
       objid INTEGER,
       context PLGadmin.identifier  /* plsqltype */
      );

   c_driver_wide CONSTANT CHAR(6) := 'GENREC';
   c_column_specific CONSTANT CHAR(7) := 'CURRCOL';
   c_plsql_command CONSTANT CHAR(3) := 'OIR';
   c_iteration_specific CONSTANT CHAR(9) := 'ITERATION';

   c_driver CONSTANT CHAR(6)    := 'DRIVER';
   c_drvsrc CONSTANT CHAR(10)   := 'DRIVERFILE';
   c_drvdir CONSTANT CHAR(9)    := 'DRIVERDIR';
   c_drvdelim CONSTANT CHAR(11) := 'DRIVERDELIM';
   c_gendir CONSTANT CHAR(6)    := 'GENDIR';
   c_gendelim CONSTANT CHAR(8)  := 'GENDELIM';

   /* fixed driver source sequence numbers */
   c_prepsrc_seq CONSTANT PLS_INTEGER := -1;
   c_custsrc_seq CONSTANT PLS_INTEGER := -2;
   c_hdrsrc_seq CONSTANT PLS_INTEGER := -3;
   
   -- 2000.2 named constant for targets saved to DB
   c_dbtarg_seq CONSTANT PLS_INTEGER := -99;

   /* file types: when you add to this, update the srctype function and
      the name constants below. */
   c_ft_prep CONSTANT PLS_INTEGER := 0;
   c_ft_code CONSTANT PLS_INTEGER := 1;
   c_ft_test CONSTANT PLS_INTEGER := 2;
   c_ft_doc  CONSTANT PLS_INTEGER := 3;
   c_ft_html CONSTANT PLS_INTEGER := 4;
   c_ft_cust CONSTANT PLS_INTEGER := 5;
   c_ft_script CONSTANT PLS_INTEGER := 6;
   c_ft_hdr CONSTANT PLS_INTEGER := 7;
   c_ft_java CONSTANT PLS_INTEGER := 8;
   c_ft_c CONSTANT PLS_INTEGER := 9;
   c_ft_cobol CONSTANT PLS_INTEGER := 10;
   c_ft_fortran CONSTANT PLS_INTEGER := 11;
   c_ft_xml CONSTANT PLS_INTEGER := 12;
   c_ft_plsql CONSTANT PLS_INTEGER := 13;

   c_prep CONSTANT VARCHAR2(10) := 'PREP';
   c_code CONSTANT VARCHAR2(10) := 'CODE';
   c_test CONSTANT VARCHAR2(10) := 'TEST';
   c_doc  CONSTANT VARCHAR2(10) := 'DOC';
   c_html CONSTANT VARCHAR2(10) := 'HTML';
   c_cust CONSTANT VARCHAR2(10) := 'CUST';
   c_script CONSTANT VARCHAR2(10) := 'SCRIPT';
   c_hdr CONSTANT VARCHAR2(10) := 'HDR';
   c_java CONSTANT VARCHAR2(10) := 'JAVA';
   c_c CONSTANT VARCHAR2(10) := 'C';
   c_cobol CONSTANT VARCHAR2(10) := 'COBOL';
   c_fortran CONSTANT VARCHAR2(10) := 'FORTRAN';
   c_xml CONSTANT VARCHAR2(10) := 'XML';
   c_plsql CONSTANT VARCHAR2(10) := 'PLSQL';
   
   /* meta file types */
   c_mft_prep CONSTANT INTEGER := 100;
   c_mft_code CONSTANT INTEGER := 101;
   c_mft_test CONSTANT INTEGER := 102;
   c_mft_doc  CONSTANT INTEGER := 103;
   c_mft_html CONSTANT INTEGER := 104;
   c_mft_cust CONSTANT INTEGER := 105;
   c_mft_script CONSTANT INTEGER := 106;
   c_mft_hdr CONSTANT INTEGER := 107;

   TYPE drvrec_t IS RECORD (
       driver PLG_doir.driver%TYPE,
       fname VARCHAR2(200),
       descr PLGadmin.dbmaxvc2,
       seq PLS_INTEGER,
       ftype PLS_INTEGER,
       use_file BOOLEAN,
       objid PLS_INTEGER,
       first_row PLS_INTEGER,
       last_row PLS_INTEGER
      );

   TYPE driver_rectype IS RECORD (
       name PLGadmin.identifier,
       descr PLGadmin.dbmaxvc2,
       just_like PLGadmin.identifier,
       objid INTEGER
      );

   /* Aliasing constants and structures */
   c_misc_alias  CONSTANT CHAR(10) := 'MISC ALIAS';
   c_wrap_alias  CONSTANT CHAR(10) := 'WRAP ALIAS';
   c_ident_alias CONSTANT CHAR(11) := 'IDENT ALIAS';
   c_tag_alias   CONSTANT CHAR(9)  := 'TAG ALIAS';
   c_cust_alias  CONSTANT CHAR(10) := 'CUST ALIAS';
   c_pref_alias  CONSTANT CHAR(10) := 'PREF ALIAS';
   c_suff_alias  CONSTANT CHAR(10) := 'SUFF ALIAS';

   c_prefix CONSTANT CHAR(3) := 'pre';
   c_suffix CONSTANT CHAR(3) := 'suf';
   c_custpre CONSTANT CHAR(3) := 'pre';
   c_custpost CONSTANT CHAR(4) := 'post';

   /* Screen names for GUI interface */
   c_scr_perftrc    CONSTANT CHAR(11) := 'SCR_PERFTRC';
   c_scr_codstds    CONSTANT CHAR(11) := 'SCR_CODSTDS';
   c_scr_errhdlg    CONSTANT CHAR(11) := 'SCR_ERRHDLG';
   c_scr_properties CONSTANT CHAR(14) := 'SCR_PROPERTIES';
   c_scr_primkey    CONSTANT CHAR(11) := 'SCR_PRIMKEY';
   c_scr_columns    CONSTANT CHAR(11) := 'SCR_COLUMNS';
   c_scr_config     CONSTANT CHAR(10) := 'SCR_CONFIG';
   c_scr_prefs      CONSTANT CHAR(9)  := 'SCR_PREFS';

   /* Retrieve Information Values */
   FUNCTION global
      RETURN VARCHAR2;

   /*NOT USED
   FUNCTION info_val
      (drv IN VARCHAR2,
       objname_in IN VARCHAR2,
       objtype_in IN VARCHAR2,
       infotype_in IN VARCHAR2,
       seq_in INTEGER,
       sch IN VARCHAR2 := NULL)
      RETURN PLG_doir.info%TYPE;
   */

   FUNCTION colval
      (drv IN VARCHAR2,
       tab IN VARCHAR2,
       col IN VARCHAR2,
       infotype_in IN PLG_doir.infotype%TYPE,
       seq_in IN PLG_doir.infoseq%TYPE := 1,
       sch IN VARCHAR2 := NULL)
      RETURN PLG_doir.info%TYPE;

   /*NOT USED
   FUNCTION globalinfo
      (drv IN VARCHAR2,
       objtype_in IN VARCHAR2,
       infotype_in IN PLG_doir.infotype%TYPE,
       sch IN VARCHAR2 := NULL)
      RETURN PLG_doir.info%TYPE;
   */

   FUNCTION tabinfo
      (drv IN VARCHAR2,
       tab IN VARCHAR2,
       infotype_in IN PLG_doir.infotype%TYPE,
       seq_in IN PLG_doir.infoseq%TYPE := 1,
       sch IN VARCHAR2 := NULL,
       user_only IN BOOLEAN := FALSE)
      RETURN PLG_doir.info%TYPE;

   PROCEDURE gettabrec
      (drv IN VARCHAR2,
       tab IN VARCHAR2,
       infotype_in IN PLG_doir.infotype%TYPE,
       seq_in IN PLG_doir.infoseq%TYPE := 1,
       sch IN VARCHAR2 := NULL,
       user_only IN BOOLEAN := FALSE,
       tabinfo_out OUT PLG_doir%ROWTYPE,
       restrict_objid IN VARCHAR2 := NULL
       );

   PROCEDURE get_info_and_seq
      (drv IN VARCHAR2,
       objname_in IN VARCHAR2,
       objtype_in IN VARCHAR2,
       attrname_in IN VARCHAR2,
       attrtype_in IN VARCHAR2,
       infotype_in IN PLG_doir.infotype%TYPE,
       info_out OUT PLG_doir.info%TYPE,
       infoseq_out OUT PLG_doir.infoseq%TYPE,
       sch IN VARCHAR2 := NULL);

   PROCEDURE setlookup
      (drv IN VARCHAR2,
       tab IN VARCHAR2,
       lutype IN VARCHAR2,
       /* c_raise */
       lucontext IN VARCHAR2,
       /* progname - ins or c_global */
       luaction IN VARCHAR2,
       /* ISNULL */
       numval IN INTEGER,
       /* errnum */
       strval IN VARCHAR2,
       /* errmsg */
       sch IN VARCHAR2 := NULL);

   PROCEDURE getlookup
      (drv IN VARCHAR2,
       tab IN VARCHAR2,
       lutype IN VARCHAR2,
       /* c_raise */
       lucontext IN VARCHAR2,
       /* progname - ins or c_global */
       luaction IN VARCHAR2,
       /* ISNULL */
       numval IN OUT INTEGER,
       /* errnum */
       strval IN OUT VARCHAR2,
       /* errmsg */
       sch IN VARCHAR2 := NULL);

   FUNCTION descindex
      (drv IN VARCHAR2,
       tab IN VARCHAR2,
       sch IN VARCHAR2 := NULL)
      RETURN VARCHAR2;

   FUNCTION pkycol
      (drv IN VARCHAR2,
       tab IN VARCHAR2,
       pos IN INTEGER,
       sch IN VARCHAR2 := NULL)
      RETURN PLG_doir.info%TYPE;

   FUNCTION loadtype
      (drv IN VARCHAR2,
       tab IN VARCHAR2,
       sch IN VARCHAR2 := NULL)
      RETURN VARCHAR2;

   FUNCTION loadany
      (drv IN VARCHAR2,
       tab IN VARCHAR2,
       sch IN VARCHAR2 := NULL)
      RETURN BOOLEAN;

   FUNCTION loadall
      (drv IN VARCHAR2,
       tab IN VARCHAR2,
       sch IN VARCHAR2 := NULL)
      RETURN BOOLEAN;

   FUNCTION loadincr
      (drv IN VARCHAR2,
       tab IN VARCHAR2,
       sch IN VARCHAR2 := NULL)
      RETURN BOOLEAN;

/* Exception handling information */
   FUNCTION psexc
      (drv IN VARCHAR2,
       tab IN VARCHAR2,
       sch IN VARCHAR2 := NULL)
      RETURN BOOLEAN;

   FUNCTION PLVexc
      (drv IN VARCHAR2,
       tab IN VARCHAR2,
       sch IN VARCHAR2 := NULL)
      RETURN BOOLEAN;

   FUNCTION retcd
      (drv IN VARCHAR2,
       tab IN VARCHAR2,
       sch IN VARCHAR2 := NULL)
      RETURN BOOLEAN;

   FUNCTION excinfo
      (drv IN VARCHAR2,
       tab IN VARCHAR2,
       sch IN VARCHAR2 := NULL)
      RETURN exc_rectype;

   FUNCTION raisemech
      (drv IN VARCHAR2,
       tab IN VARCHAR2,
       sch IN VARCHAR2 := NULL)
      RETURN VARCHAR2;

   FUNCTION handlemech
      (drv IN VARCHAR2,
       tab IN VARCHAR2,
       sch IN VARCHAR2 := NULL)
      RETURN VARCHAR2;

   FUNCTION failureval
      (drv IN VARCHAR2,
       tab IN VARCHAR2,
       sch IN VARCHAR2 := NULL)
      RETURN VARCHAR2;

   FUNCTION failuremsg
      (drv IN VARCHAR2,
       tab IN VARCHAR2,
       sch IN VARCHAR2 := NULL)
      RETURN VARCHAR2;

   FUNCTION successval
      (drv IN VARCHAR2,
       tab IN VARCHAR2,
       sch IN VARCHAR2 := NULL)
      RETURN VARCHAR2;