CREATE TABLE message_text (
    code INTEGER,
	text VARCHAR2(2000),
	texttype VARCHAR2(20));
	
CREATE OR REPLACE PACKAGE errpkg
IS
   PROCEDURE raise (err_in IN INTEGER := SQLCODE);


   PROCEDURE recNstop (err_in IN INTEGER := SQLCODE,
      msg_in IN VARCHAR2 := NULL);


   PROCEDURE recNgo (err_in IN INTEGER := SQLCODE, 
      msg_in IN VARCHAR2 := NULL);


   FUNCTION errtext (err_in IN INTEGER := SQLCODE) 
     RETURN VARCHAR2;

END errpkg;
/

CREATE OR REPLACE PACKAGE BODY errpkg
IS
   PROCEDURE raise (err_in IN INTEGER := SQLCODE)
   IS
   BEGIN    
      -- London 9/2001
      -- ADD FORMAT_CALL_STACK HERE TO SHOW WHERE RAISE OCCURRED!  
      
      IF err_in BETWEEN -20999 AND -20000 
      THEN
         RAISE_APPLICATION_ERROR (err_in, errtext (err_in));
         
      /* Use positive error numbers -- lots to choose from! */
      ELSIF err_in > 0 AND err_in NOT IN (1, 100)
      THEN
         RAISE_APPLICATION_ERROR (-20000, err_in ||'-'||errtext (err_in));
      
      /* Can't EXCEPTION_INIT -1403 */
      ELSIF err_in IN (100, -1403)
      THEN
         RAISE NO_DATA_FOUND;
         
		/* Re-raise any other exception. */
      ELSE 
         EXECUTE IMMEDIATE
           'DECLARE myexc EXCEPTION; ' ||
           '   PRAGMA EXCEPTION_INIT (myexc, ' || 
                 TO_CHAR (err_in) || ');' ||
           'BEGIN  RAISE myexc; END;'
		   ;
      END IF;
   END;

   PROCEDURE recNgo (err_in IN INTEGER := SQLCODE,
      msg_in IN VARCHAR2 := NULL)
   IS
   BEGIN
      /* We will use DBMS_OUTPUT for logging here, but
	     in your REAL world, you should avoid this package
		 and instead use a logging package that hides all
		 the details, such as where the log message goes  
      logpkg.put (err_in, NVL (msg_in, errtext (err_in))); 
	  */
	  DBMS_OUTPUT.PUT_LINE (
	     'Error ' || err_in || ': ' || NVL (msg_in, errtext (err_in)));
   END;

   PROCEDURE recNstop (err_in IN INTEGER := SQLCODE,
      msg_in IN VARCHAR2 := NULL)
   IS
   BEGIN
      recNgo (err_in, msg_in);
      errpkg.raise (err_in);
   END;
   
   FUNCTION errtext (err_in IN INTEGER := SQLCODE) RETURN VARCHAR2 IS
      CURSOR txt_cur IS
         SELECT text FROM message_text
          WHERE texttype = 'EXCEPTION' 
            AND code = err_in;
      txt_rec txt_cur%ROWTYPE;
   BEGIN
      OPEN txt_cur;
      FETCH txt_cur INTO txt_rec;
      IF txt_cur%NOTFOUND THEN 
         txt_rec.text := SQLERRM (err_in); 
      END IF;
      RETURN txt_rec.text;
   END;
    
END errpkg;
/


/*======================================================================
| Supplement to the third edition of Oracle PL/SQL Programming by Steven
| Feuerstein with Bill Pribyl, Copyright (c) 1997-2002 O'Reilly &
| Associates, Inc. To submit corrections or find more code samples visit
| http://www.oreilly.com/catalog/oraclep3/
*/