genxconfirm.sql

Oracle PL/SQL procedure generator (second generator type)

CREATE OR REPLACE PROCEDURE genxconfirm_eval (fullstr IN OUT VARCHAR2, abbrevstr IN OUT VARCHAR2)
/* 
     GenX Installation Confirmation

     Run this script IN THE SCHEMA THAT OWNS GenX 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 ('* GenX 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 ('*            GenX 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;
    
   /* Not for Genx, only PL/G 
   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 ('* GenX is Installed Successfully in Schema ' || USER);
      addtext ('**************************************************************');
   END IF;
   
   
END;
/

CREATE OR REPLACE PROCEDURE genxconfirm
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
   genxconfirm_eval (str1, str2);
   disp;
END;
/