plgadmin.spb

Oracle PL/SQL procedure generator (second generator type)

CREATE OR REPLACE PACKAGE BODY PLGadmin
IS
/*----------------------------------------------------------------
||                  PL/Generator from RevealNet 
||----------------------------------------------------------------
||    File: plgdoir.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
|| -------- ------ -----------------------------------------------
|| 06/99    SEF    Update version # to 99.1.4
|| 04/99    SEF    Add trial version logic
|| 03/99    SEF    Update version # for bug fixes: 1.1.4
|| 03/99    SEF    Update version # for bug fixes: 1.1.3
|| 05/98    SEF    Created.
******************************************************************/

   c_pkg CONSTANT identifier := 'plgadmin';

   c_delim CONSTANT CHAR(1) := CHR(5);

   c_trial CONSTANT CHAR(3) := 'TRL';
   c_professional CONSTANT CHAR(3) := 'PRO';
   c_personal CONSTANT CHAR(3) := 'PER';
   
   g_version VARCHAR2(100) := 'PRO-2000.2.8';

   c_encryp_size CONSTANT PLS_INTEGER := 2*2*2*2*2*2*2*2;
   g_do_encryption BOOLEAN := TRUE;
   
/* In-line conditional logic emulating DECODE */

   FUNCTION ifelse
      (bool_in IN BOOLEAN, tval_in IN BOOLEAN, fval_in IN BOOLEAN)
       RETURN BOOLEAN
   IS
   BEGIN
      IF bool_in
      THEN
         RETURN tval_in;
      ELSE
         RETURN fval_in;
      END IF;
   END;

   FUNCTION ifelse
       (bool_in IN BOOLEAN, tval_in IN DATE, fval_in IN DATE)
       RETURN DATE
   IS
   BEGIN
      IF bool_in
      THEN
         RETURN tval_in;
      ELSE
         RETURN fval_in;
      END IF;
   END;

   FUNCTION ifelse
       (bool_in IN BOOLEAN, tval_in IN NUMBER,  fval_in IN NUMBER)
       RETURN NUMBER
   IS
   BEGIN
      IF bool_in
      THEN
         RETURN tval_in;
      ELSE
         RETURN fval_in;
      END IF;
   END;

   FUNCTION ifelse
      (bool_in IN BOOLEAN, tval_in IN VARCHAR2, fval_in IN VARCHAR2)
       RETURN VARCHAR2
   IS
   BEGIN
      IF bool_in
      THEN
         RETURN tval_in;
      ELSE
         RETURN fval_in;
      END IF;
   END;

   PROCEDURE import (
      file IN VARCHAR2,      
      drv IN VARCHAR2,
      sch IN VARCHAR2 := NULL
      )
   IS
      c_tab identifier := 'plg_doir';
      tid INTEGER;
      fid INTEGER;
   BEGIN
      DBMS_OUTPUT.PUT_LINE (
         'PL/Generator Import is temporarily disabled.');
      /*
      tid := PLVrep.dbtabid (c_tab, '=' || c_tab);
      fid := PLVrep.fileid (file, '=' || c_tab);
      setgenenv (drv, NVL (sch, USER), PLGdoir.c_global);

      PLGdoir.del (drv, sch);
       
      PLVrep.copy (fid, tid, segdelim => c_delim);

      PLVrep.destroy (fid);
      PLVrep.destroy (tid);
   EXCEPTION
      WHEN OTHERS 
      THEN
         PLVrep.destroy (fid);
         PLVrep.destroy (tid);
         DBMS_OUTPUT.PUT_LINE ('PL/Generator import error ' || SQLCODE);
      */
   END;

   PROCEDURE export (
      file IN VARCHAR2,      
      drv IN VARCHAR2,
      sch IN VARCHAR2 := NULL
      )
   IS
      c_tab identifier := 'plg_doir';

      v_query dbmaxvc2 := 
         'SELECT * FROM plg_doir WHERE driver = UPPER (''' || drv || ''')';

      tid INTEGER;
      fid INTEGER;
   BEGIN
      DBMS_OUTPUT.PUT_LINE (
         'PL/Generator Import is temporarily disabled.');
      /*
      setgenenv (drv, NVL (sch, USER), PLGdoir.c_global);

      IF sch IS NOT NULL
      THEN
         v_query := v_query || 'AND owner = UPPER (''' || sch || ''')';
      END IF;

      tid := PLVrep.dbtabid (v_query);
      PLVrep.tabsegs (tid, c_tab);
      
      fid := PLVrep.fileid (file);
      PLVrep.tabsegs (fid, c_tab);
         
      PLVrep.copy (tid, fid, segdelim => c_delim);

      PLVrep.destroy (fid);
      PLVrep.destroy (tid);
      
   EXCEPTION
      WHEN OTHERS 
      THEN
         PLVrep.destroy (fid);
         PLVrep.destroy (tid);
         DBMS_OUTPUT.PUT_LINE ('PL/Generator export error ' || SQLCODE);
      */
   END;

   PROCEDURE test_fileIO (
      loc IN VARCHAR2,
      nm IN VARCHAR2 := 'plgen.tst')
   IS
      fid UTL_FILE.FILE_TYPE;

      PROCEDURE disperr (
         str IN VARCHAR2 := NULL)
      IS
      BEGIN
         IF str IS NULL
         THEN
            DBMS_OUTPUT.PUT_LINE (
               'Error ' || SQLERRM || 
               ' writing to location ' || loc || 
               ' and filename ' || nm
               );
         ELSE
            DBMS_OUTPUT.PUT_LINE (
               str || ' with location ' || loc || 
                      ' and filename ' || nm
               );
         END IF;
      END;

   BEGIN
      DBMS_OUTPUT.ENABLE (1000000);

      fid := UTL_FILE.FOPEN (loc, nm, 'W');
      UTL_FILE.PUT_LINE (fid, 'hello world');
      UTL_FILE.FCLOSE (fid);

   EXCEPTION
      WHEN UTL_FILE.INVALID_PATH
      THEN   
          disperr ('UTL_FILE Invalid path');
      
      WHEN UTL_FILE.INVALID_MODE
      THEN
          disperr ('UTL_FILE Invalid mode');

      WHEN UTL_FILE.INVALID_FILEHANDLE
      THEN
          disperr ('UTL_FILE Invalid filehandle');

      WHEN UTL_FILE.INVALID_OPERATION
      THEN
          disperr ('UTL_FILE Invalid operation');

      WHEN UTL_FILE.READ_ERROR
      THEN
          disperr ('UTL_FILE Read error');

      WHEN UTL_FILE.WRITE_ERROR
      THEN
          disperr ('UTL_FILE Write error');

      WHEN UTL_FILE.INTERNAL_ERROR
      THEN
          disperr ('UTL_FILE Internal error');

      WHEN OTHERS
      THEN
          disperr;
   END;

   /* Dynamic SQL requirements - cut off dependency on PLVdyn. */
   PROCEDURE compile 
      (stg_in IN VARCHAR2, 
       show_err_in IN BOOLEAN := FALSE,
       add_cor IN BOOLEAN := TRUE,
       schema_in IN VARCHAR2 := NULL)
   IS
      v_program maxvc2;
      v_line dbmaxvc2;
      v_loc INTEGER;
      v_bool BOOLEAN;
      c_cor CONSTANT CHAR(18) := 'CREATE OR REPLACE ';
      cur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR;
   BEGIN
      IF add_cor
      THEN
         v_program := c_cor || stg_in;
      ELSE
         v_program := stg_in;
      END IF;
      
      DBMS_SQL.PARSE (cur, v_program, DBMS_SQL.NATIVE);
      v_loc := DBMS_SQL.EXECUTE (cur);
      DBMS_SQL.CLOSE_CURSOR (cur);

      IF show_err_in 
      THEN
	      --LVvu.err;
         NULL;
      END IF;
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_SQL.CLOSE_CURSOR (cur);
         DBMS_OUTPUT.PUT_LINE ('Error compiling ' || v_program || ': ' || SQLERRM);
   END;

   PROCEDURE compile_from_file 
      (file_in IN VARCHAR2, 
       show_err_in IN BOOLEAN := FALSE,
       add_cor IN BOOLEAN := FALSE,
       schema_in IN VARCHAR2 := NULL)
   IS
      TYPE vc2000_tabtype IS TABLE OF VARCHAR2(2000)
          INDEX BY BINARY_INTEGER;
      ftab vc2000_tabtype;
      v_program maxvc2 := NULL;
      v_rows PLS_INTEGER;
   BEGIN
      /* Move contents of file to index table. */
      /*
      PLVfile.file2pstab 
         (file_in,
          ftab,
          v_rows);
      */
      FOR row_ind IN ftab.FIRST .. ftab.LAST
      LOOP
         IF v_program IS NULL
         THEN
            v_program := ftab (row_ind);
         ELSE
            v_program := v_program || CHR(10) || ftab (row_ind);
         END IF;
      END LOOP;