plgdrv.spb

Oracle PL/SQL procedure generator (second generator type)

CREATE OR REPLACE PACKAGE BODY plgdrv
IS
/*----------------------------------------------------------------
||                  PL/Generator from Quest Software
||----------------------------------------------------------------
||    File: plgdrv.spb
||  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
|| -------- ------ -----------------------------------------------
|| 10/98    SEF    Created.
******************************************************************/

   PROCEDURE dynproc (str IN VARCHAR2)
   IS
      dyncur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR;
      fdbk PLS_INTEGER;
   BEGIN
      DBMS_SQL.parse (dyncur, 'BEGIN ' || str || '; END;',
         DBMS_SQL.native);
      fdbk := DBMS_SQL.execute (dyncur);
      DBMS_SQL.CLOSE_CURSOR (dyncur);
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_SQL.CLOSE_CURSOR (dyncur);
         DBMS_OUTPUT.PUT_LINE ('Dynproc failure: ' || SQLERRM);
   END;

   PROCEDURE loaddriver (
      drv IN VARCHAR2, 
      loc IN VARCHAR2, 
      fname IN VARCHAR2,
      seq_in IN VARCHAR2,
      fname2 IN VARCHAR2 := NULL)
   IS
       fid utl_file.file_type;
       buffer PLGadmin.maxvc2;
       lt_buffer PLGadmin.maxvc2;
       v_name plg_source.name%TYPE := UPPER (fname);
       v_line PLS_INTEGER := 1;

       PROCEDURE recNgo (str IN VARCHAR2)
       IS
       BEGIN
          UTL_FILE.FCLOSE (fid);
          DBMS_OUTPUT.PUT_LINE (str || '-' || SQLERRM);
       END;
    BEGIN
       IF fname2 IS NOT NULL 
       THEN
          v_name := UPPER (fname2);
       END IF;
       
       BEGIN
          fid := UTL_FILE.FOPEN (loc, fname, 'r');
       EXCEPTION
          WHEN OTHERS 
          THEN
             IF fname2 IS NOT NULL
             THEN
                fid := UTL_FILE.FOPEN (loc, fname2, 'r');
             ELSE
                RAISE;
             END IF;
       END;
       
       LOOP
          UTL_FILE.GET_LINE (fid, buffer);

          lt_buffer := LTRIM (buffer);
          IF lt_buffer IS NULL OR
             (lt_buffer NOT LIKE '#%' AND	lt_buffer NOT LIKE '<P>#%')
          THEN
             /* Use negative line numbers to indicate temporary source */
             INSERT INTO plg_driver_source (line, driver, name, seq, text)
               VALUES (-1 * v_line, UPPER (drv), v_name, seq_in, buffer);

             v_line := v_line + 1;
          END IF;
       END LOOP;
       UTL_FILE.fclose (fid);
       COMMIT;
    EXCEPTION
       WHEN NO_DATA_FOUND
       THEN
          UTL_FILE.FCLOSE (fid);
       WHEN UTL_FILE.INVALID_PATH
        THEN recNgo ('invalid_path');
       WHEN UTL_FILE.INVALID_MODE
        THEN recNgo ('invalid_mode');
       WHEN UTL_FILE.INVALID_FILEHANDLE
        THEN recNgo ('invalid_filehandle');
       WHEN UTL_FILE.INVALID_OPERATION
        THEN recNgo ('invalid_operation');
       WHEN UTL_FILE.READ_ERROR
        THEN recNgo ('read_error');
       WHEN UTL_FILE.WRITE_ERROR
        THEN recNgo ('write_error');
       WHEN UTL_FILE.INTERNAL_ERROR
        THEN recNgo ('internal_error');
       WHEN OTHERS
        THEN recNgo (SQLCODE);
    END;

   PROCEDURE convert_to_inserts (
      drv IN VARCHAR2,
      loc IN VARCHAR2,
      for_express IN BOOLEAN := FALSE,
      extra_ext IN VARCHAR2 := NULL
      )
   IS
   /* Move drivers from file to database table */
      v_drv PLG_doir.driver%TYPE := upper (drv);
      drvrec PLGdoir.drvrec_t;
      v_filenum PLS_INTEGER := 1;
      fid utl_file.file_type;

       PROCEDURE recNgo (str IN VARCHAR2)
       IS
       BEGIN
          UTL_FILE.FCLOSE (fid);
          DBMS_OUTPUT.PUT_LINE (str || '-' || SQLERRM);
       END;

   BEGIN
      DELETE FROM plg_driver_source where driver = v_drv;

      drvrec := PLGdoir.prepsrc (
         v_drv,
         PLGdoir.c_global,
         PLGdoir.c_global);

      loaddriver (v_drv, loc, drvrec.fname, PLGdoir.c_prepsrc_seq);

      drvrec := PLGdoir.hdrsrc (
         v_drv,
         PLGdoir.c_global,
         PLGdoir.c_global);

      loaddriver (v_drv, loc, drvrec.fname, PLGdoir.c_hdrsrc_seq);

      LOOP
         drvrec := PLGdoir.drvsrc (
            v_drv,
            v_filenum,
            PLGdoir.c_global,
            PLGdoir.c_global);

         EXIT WHEN drvrec.fname IS NULL;

         IF extra_ext IS NOT NULL
         THEN
            loaddriver (v_drv, loc, 
               drvrec.fname || '.' || extra_ext, drvrec.seq, drvrec.fname);
         ELSE
            loaddriver (v_drv, loc, drvrec.fname, drvrec.seq);
         END IF;

         v_filenum := v_filenum + 1;
      END LOOP;

      /* Write out long series of INSERT statements for distribution. */

      /* Load up the alias cache for this driver. */
      --dynproc ('PLG' || v_drv || '.session_init');
      
       fid := UTL_FILE.FOPEN (loc, v_drv || '.ins', 'w');
       UTL_FILE.PUT_LINE (fid, 'SET FEEDBACK OFF');
       UTL_FILE.PUT_LINE (fid, 'SET DEFINE OFF');
       FOR rec IN (SELECT * FROM plg_driver_source WHERE driver = v_drv)
       LOOP
          /* Now line numbers are positive for the insert statements. */
          UTL_FILE.PUT_LINE (fid,
             'INSERT INTO plg_driver_source (line, driver, name, seq, text) VALUES (' ||
             -1 * rec.line || ', ''' ||
             rec.driver || ''', ''' ||
             rec.name || ''', ''' ||
             rec.seq || ''', ''' ||
             REPLACE (rec.text, '''', '''''') || ''');'
             );
       END LOOP;
       UTL_FILE.PUT_LINE (fid, 'SET FEEDBACK ON');
       UTL_FILE.PUT_LINE (fid, 'SET DEFINE ON');
       UTL_FILE.FCLOSE (fid);

      /* I have created the insert file, so clear the driver source. */
      DELETE FROM plg_driver_source
       WHERE driver = v_drv
         AND line < 0;

      COMMIT;

    EXCEPTION
       WHEN NO_DATA_FOUND
       THEN
          /* Finished reading file. */
          UTL_FILE.FCLOSE (fid);
       WHEN UTL_FILE.INVALID_PATH
        THEN recNgo ('invalid_path');
       WHEN UTL_FILE.INVALID_MODE
        THEN recNgo ('invalid_mode');
       WHEN UTL_FILE.INVALID_FILEHANDLE
        THEN recNgo ('invalid_filehandle');
       WHEN UTL_FILE.INVALID_OPERATION
        THEN recNgo ('invalid_operation');
       WHEN UTL_FILE.READ_ERROR
        THEN recNgo ('read_error');
       WHEN UTL_FILE.WRITE_ERROR
        THEN recNgo ('write_error');
       WHEN UTL_FILE.INTERNAL_ERROR
        THEN recNgo ('internal_error');
       WHEN OTHERS
        THEN recNgo (SQLCODE);
   END;

	PROCEDURE rid_wrap_aliases (drv IN VARCHAR2)
   IS
	   pre                           PLGadmin.dbmaxvc2;
	   suf                           PLGadmin.dbmaxvc2;
	   descrip                       PLGadmin.dbmaxvc2;

	   CURSOR wrap_cur
	   IS
	      SELECT DISTINCT SUBSTR (w.attrname, 1, 254) orig,
	                      SUBSTR (doc, 1, 254) orig_descrip
	        FROM PLG_doir w
	       WHERE w.owner = PLGdoir.c_global
	         AND w.objname = PLGdoir.c_global
	         AND w.objtype = 'ALIAS'
	         AND w.attrtype = 'WRAP ALIAS'
	         AND w.infotype = PLGdoir.c_global
	         AND w.infoseq = 1
	       ORDER BY orig;

	   PROCEDURE rid_alias (al IN VARCHAR2, pre IN VARCHAR2, suf IN VARCHAR2)
	   IS
	      v_pre                         PLGadmin.identifier
	         := LOWER (PLGgen.aliasopen ||
	                   PLGdoir.alias_prefix (
	                      drv,
	                      PLGdoir.c_global,
	                      al,
	                      sch => PLGdoir.c_global
	                   ) ||
	                   PLGgen.aliasclose);
	      v_suf                         PLGadmin.identifier
	         := LOWER (PLGgen.aliasopen ||
	                   PLGdoir.alias_suffix (
	                      drv,
	                      PLGdoir.c_global,
	                      al,
	                      sch => PLGdoir.c_global
	                   ) ||
	                   PLGgen.aliasclose);
	   BEGIN
	      UPDATE PLG_doir
	         SET info = REPLACE (info, v_pre, pre)
	       WHERE INSTR (info, v_pre) > 0;
	      DBMS_OUTPUT.PUT_LINE ('Got rid of ' || v_pre || ' in ' || SQL%ROWCOUNT || ' rows.');
	      UPDATE PLG_doir
	         SET info = REPLACE (info, v_suf, suf)
	       WHERE INSTR (info, v_suf) > 0;
	      DBMS_OUTPUT.PUT_LINE ('Got rid of ' || v_suf || ' in ' || SQL%ROWCOUNT || ' rows.');
	   END;
	BEGIN
	   FOR rec IN wrap_cur
	   LOOP
	      PLGdoir.get_wrap_alias (
	         'TE',
	         '*ALL',
	         rec.orig,
	         pre,
	         suf,
	         descrip,
	         '*ALL'
	      );
	      rid_alias (rec.orig, pre, suf);
	   END LOOP;
	END;

	PROCEDURE prep_for_lite (drv IN VARCHAR2)
   IS
	BEGIN
      rid_wrap_aliases (drv);
	END;

END plgdrv;
/