plgcnfrm.sql

Oracle PL/SQL procedure generator (second generator type)

CREATE OR REPLACE PROCEDURE plgconfirm_eval (fullstr IN OUT VARCHAR2, abbrevstr IN OUT VARCHAR2)
/* 
     PL/Generator Installation Confirmation

     Run this script IN THE SCHEMA THAT OWNS PL/Generator CODE
     to verify that all package specifications and bodies are present
     and none have compile errors.

     Copyright 1998 Quest Software
     Author: Steven Feuerstein
     Date: 6/30/98
*/
IS
   v_num INTEGER;
   first_problem BOOLEAN := TRUE;

   TYPE obj_tabtype IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
   objtab obj_tabtype;

   FUNCTION isplgobj (nm IN VARCHAR2) RETURN BOOLEAN
   IS
      v_nm VARCHAR2(100) := UPPER (nm);
      v_row PLS_INTEGER := objtab.FIRST;
      retval BOOLEAN := FALSE;
   BEGIN
      LOOP
         EXIT WHEN v_row IS NULL;
         retval := UPPER (objtab(v_row)) = v_nm;
         EXIT WHEN retval;
         v_row := objtab.NEXT (v_row);
      END LOOP;
      RETURN retval;
   END;
   
   PROCEDURE addtext (str IN VARCHAR2) IS
   BEGIN
      IF fullstr IS NULL
      THEN
         fullstr := str;
      ELSE
         fullstr := fullstr || CHR(10) || str;
      END IF;
   END;
   
   FUNCTION objfound (nm IN VARCHAR2, tp IN VARCHAR2) RETURN BOOLEAN
   IS
      CURSOR objcur IS
      SELECT 1
        FROM ALL_OBJECTS
       WHERE object_type = tp
         AND object_name = UPPER (nm);      
      objrec objcur%ROWTYPE;
   BEGIN
      OPEN objcur; FETCH objcur INTO objrec;
      RETURN objcur%FOUND;
   END;

   PROCEDURE show_problem (str IN VARCHAR2)
   IS
   BEGIN
      IF first_problem
      THEN
         addtext ('************************************************************');
         addtext ('* PL/Generator Installation Failure in Schema ' || USER);
         addtext ('************************************************************');
         first_problem := FALSE;
      END IF;
      addtext (str);
      abbrevstr := 'FAILURE';      
   END;

   PROCEDURE check_status
   IS
   BEGIN
      FOR rec IN (
        SELECT OBJECT_NAME, 
               DECODE (OBJECT_TYPE, 'PACKAGE', 'SPECIFICATION', OBJECT_TYPE) object_type
          FROM USER_OBJECTS 
         WHERE STATUS = 'INVALID' AND OBJECT_NAME LIKE 'PLG%'
        )
      LOOP
         IF isplgobj (rec.object_name)
         THEN
            show_problem 
               ('*    ' || INITCAP (rec.object_type) || ' of ' ||
                rec.object_name || ' is marked INVALID.');
         END IF;
      END LOOP;
   END;

   PROCEDURE check_programs (tp IN VARCHAR2)
   IS
      v_tp VARCHAR2(100);
      objind PLS_INTEGER := objtab.FIRST;
   BEGIN
      IF tp = 'PACKAGE' 
      THEN
         v_tp := 'SPECIFICATION';
         
      ELSIF tp = 'PACKAGE BODY'
      THEN
         v_tp := 'BODY';
      ELSE
         v_tp := tp;
      END IF;

      LOOP
         EXIT WHEN objind IS NULL;
         
         IF objfound (objtab(objind), tp)
         THEN
            NULL;
            --addtext ('found ' || tp || ' ' || objtab(objind));
         ELSE
            IF (objtab(objind) = 'plggenv' AND tp = 'PACKAGE BODY')
               OR
               (objtab(objind) != 'plghash' AND tp = 'FUNCTION')
            THEN
               NULL;
            ELSIF objtab(objind) = 'plghash' AND tp = 'FUNCTION'
            THEN
               show_problem 
                  ('*    ' || v_tp || objtab(objind) || ' is NOT present.');
            ELSIF objtab(objind) != 'plghash'
            THEN
               show_problem 
                  ('*    ' || v_tp || ' of package ' ||
                   objtab(objind) || ' is NOT present.');
            END IF;
         END IF;
         objind := objtab.NEXT (objind);
      END LOOP;
   END;

   PROCEDURE show_errors
   IS
      CURSOR err_cur
      IS
      SELECT DISTINCT 
             DECODE (type, 'PACKAGE', 'PACKAGE SPECIFICATION', type) 
             || ' ' || 
             name obj
        FROM USER_ERRORS
       WHERE name LIKE 'PLG%';
      v_first BOOLEAN := FALSE;
   BEGIN
      FOR rec IN err_cur
      LOOP
         IF isplgobj (rec.obj)
         THEN
            IF v_first
            THEN
               v_first := FALSE;
               show_problem ('*');
               addtext ('*********************************************************');
               addtext ('*        PL/Generator Compilation Errors Found In       *');
               addtext ('*********************************************************');
            END IF;
            show_problem ('*   ' || rec.obj);   
         END IF;
      END LOOP;
   END;

BEGIN
   fullstr := NULL;
   abbrevstr := 'SUCCESS';
   
   DBMS_OUTPUT.ENABLE (1000000);

   objtab(1) := 'plgdoir';
   objtab(2) := 'plggen';
   objtab(3) := 'plggenv';
   objtab(4) := 'plgte';
   objtab(5) := 'plgval';
   objtab(6) := 'plgpriv';
   objtab(7) := 'plgerr';
   objtab(8) := 'plgdesc';
   objtab(9) := 'plgadmin';
   objtab(10) := 'plgdrv';
   objtab(11) := 'plgcols';
   objtab(12) := 'plgcons';
   objtab(13) := 'plgbool';
   objtab(14) := 'plgfkys';
   objtab(15) := 'plginds';
   objtab(16) := 'plgiseg';
   objtab(17) := 'plglst';
   objtab(18) := 'plgobj';
   objtab(20) := 'plgofst';
   objtab(21) := 'plgpky';
   objtab(22) := 'plgprs';
   objtab(23) := 'plgstk';
   objtab(24) := 'plgstr';
   objtab(25) := 'plgvar';
   objtab(26) := 'plgplsql';
   objtab(27) := 'plgjava';
   objtab(28) := 'plghash';
   objtab(29) := 'plgihash';
   objtab(30) := 'plgcgml';
   objtab(31) := 'plgtype';
   objtab(32) := 'plgsrchash';
   objtab(33) := 'plgnamex';

   check_status;

   check_programs ('PACKAGE');

   check_programs ('PACKAGE BODY');

   check_programs ('FUNCTION');

   show_errors;

   SELECT COUNT(*) INTO v_num
     FROM plg_doir WHERE objid < 0;

   IF v_num < 325
   THEN
      show_problem ('*');
      show_problem (
         '* Object Information Repository does not contain required rows.');
   END IF;
    
   SELECT COUNT(*) INTO v_num
     FROM plg_driver_source;

   IF v_num < 2500
   THEN
      show_problem ('*');
      show_problem (
         '* Drivers have not been loaded properly.');
   END IF;
    
   IF first_problem
   THEN
      addtext ('**************************************************************');
      addtext ('* PL/Generator is Installed Successfully in Schema ' || USER);
      addtext ('**************************************************************');
   END IF;
   
   
END;
/

CREATE OR REPLACE PROCEDURE plgconfirm
IS
   str1 VARCHAR2(32767);
   str2 VARCHAR2(32767);
   
   PROCEDURE disp
   IS
      loc PLS_INTEGER;
      startloc PLS_INTEGER := 1;
   BEGIN
      LOOP
         loc := INSTR (str1, CHR(10), startloc);
         EXIT WHEN loc IS NULL;
         
         IF loc = 0
         THEN
            DBMS_OUTPUT.PUT_LINE (RTRIM (SUBSTR (str1, startloc), CHR(10)));
            EXIT;
         ELSE
            DBMS_OUTPUT.PUT_LINE (
               RTRIM (SUBSTR (str1, startloc, loc - startloc), CHR(10)));
         END IF;
         startloc := loc+1;
      END LOOP;
   END;
BEGIN
   plgconfirm_eval (str1, str2);
   disp;
END;
/
REM exec plgconfirm