Get All PLSQL Errors


I developed a function to get all PLSQL errors in schema.
This function should be used after calling program units so that can get PLSQL errors and you can use for logging and tracing.


Function Code


CREATE OR REPLACE FUNCTION GET_PLSQL_ERROS
   RETURN VARCHAR2
IS
   LC$RETVALUE   VARCHAR2 (4000);

   CURSOR LCUR$ERRORS
   IS
        SELECT DISTINCT NAME, TYPE
          FROM USER_ERRORS
      ORDER BY 1, 2;

   PROCEDURE ADD_LINE (IN_LINE IN VARCHAR2)
   IS
   BEGIN
      LC$RETVALUE := SUBSTR (LC$RETVALUE || IN_LINE || CHR (10), 1, 4000);
   END ADD_LINE;
BEGIN
   FOR LREC$ERRORS IN LCUR$ERRORS
   LOOP
      ADD_LINE (LREC$ERRORS.NAME || ' ' || LREC$ERRORS.TYPE);

      ADD_LINE (
         RPAD ('-', LENGTH (LREC$ERRORS.NAME || LREC$ERRORS.TYPE) + 1, '-'));

      FOR LREC$ERROR_DET
         IN (  SELECT LINE, POSITION, SUBSTR (TEXT, 1, 128) TEXT
                 FROM USER_ERRORS
                WHERE NAME = LREC$ERRORS.NAME AND TYPE = LREC$ERRORS.TYPE
             ORDER BY SEQUENCE)
      LOOP
         ADD_LINE (
               LPAD (LREC$ERROR_DET.LINE, 4)
            || ' '
            || LPAD (LREC$ERROR_DET.POSITION, 3)
            || ' '
            || LREC$ERROR_DET.TEXT);
      END LOOP;

      ADD_LINE ('*******************' || CHR (10));
   END LOOP;

   IF LENGTH (LC$RETVALUE) = 4000
   THEN
      LC$RETVALUE := SUBSTR (LC$RETVALUE, 1, 3996) || CHR (10) || '...';
   END IF;

   RETURN NVL (LC$RETVALUE, 'No Errors');
END;
/


Function Test


   SELECT GET_PLSQL_ERROS FROM DUAL;

   DBMS_OUTPUT.PUT_LINE (GET_PLSQL_ERROS);

Thanks

Popular posts from this blog

ADF : Get Current Logged User Name

OAF : Get Current Row in Table

ADF : Working with ViewCriteria