-- EXCEPTION object type
-- William Robertson 2003, www.williamrobertson.net

CREATE OR REPLACE TYPE exception_ot AS OBJECT
( message     VARCHAR2(32767)
, error_code  INTEGER
, module      VARCHAR2(30)
, action      VARCHAR2(100)
, os_logdir   VARCHAR2(80)
, os_logfile  VARCHAR2(70)
, raised      TIMESTAMP
, MEMBER PROCEDURE include_text
  ( p_word1              VARCHAR2 DEFAULT NULL
  , p_word2              VARCHAR2 DEFAULT NULL
  , p_word3              VARCHAR2 DEFAULT NULL )
, MEMBER PROCEDURE log
  ( p_log_to_database_yn VARCHAR2 DEFAULT 'Y'
  , p_log_to_file_yn     VARCHAR2 DEFAULT 'N'
  , p_send_mail_yn       VARCHAR2 DEFAULT 'N' )
, MEMBER PROCEDURE raise
  ( p_log_to_database_yn VARCHAR2 DEFAULT 'N'
  , p_log_to_file_yn     VARCHAR2 DEFAULT 'N'
  , p_send_mail_yn       VARCHAR2 DEFAULT 'N' )
, CONSTRUCTOR FUNCTION exception_ot
  ( p_message            VARCHAR2
  , p_error_code         INTEGER  DEFAULT -20000
  , p_module             VARCHAR2 DEFAULT NULL
  , p_action             VARCHAR2 DEFAULT NULL
  , p_logdir             VARCHAR2 DEFAULT NULL
  , p_logfile            VARCHAR2 DEFAULT NULL )
  RETURN SELF AS RESULT
, MEMBER PROCEDURE print )
/

show errors

CREATE OR REPLACE TYPE BODY exception_ot
AS
	MEMBER PROCEDURE include_text
		( p_word1              VARCHAR2 DEFAULT NULL
		, p_word2              VARCHAR2 DEFAULT NULL
		, p_word3              VARCHAR2 DEFAULT NULL )
	IS
	BEGIN
		message := REPLACE(REPLACE(REPLACE(message,'[1]',p_word1),'[2]',p_word2),'[3]',p_word3);
	END;


	MEMBER PROCEDURE log
		( p_log_to_database_yn VARCHAR2 DEFAULT 'Y'
		, p_log_to_file_yn     VARCHAR2 DEFAULT 'N'
		, p_send_mail_yn       VARCHAR2 DEFAULT 'N' )
	IS
	BEGIN
		IF UPPER(SUBSTR(p_log_to_database_yn,1,1)) = 'Y' THEN
			LOGGER.WRITE(message, module, action);
		END IF;

		IF UPPER(SUBSTR(p_log_to_file_yn,1,1)) = 'Y' THEN
			LOGGER.WRITE(message, module, action);
		END IF;

		IF UPPER(SUBSTR(p_send_mail_yn,1,1)) = 'Y' THEN
			LOGGER.WRITE(message, module, action);
		END IF;
	END;


	MEMBER PROCEDURE raise
		( p_log_to_database_yn VARCHAR2 DEFAULT 'N'
		, p_log_to_file_yn     VARCHAR2 DEFAULT 'N'
		, p_send_mail_yn       VARCHAR2 DEFAULT 'N' )
	IS
	BEGIN
		RAISE_APPLICATION_ERROR
		( NVL(error_code,-20000)
		, message
		, TRUE );
	END;


	CONSTRUCTOR FUNCTION exception_ot
		( p_message            VARCHAR2
		, p_error_code         INTEGER  DEFAULT -20000
		, p_module             VARCHAR2 DEFAULT NULL
		, p_action             VARCHAR2 DEFAULT NULL
		, p_logdir             VARCHAR2 DEFAULT NULL
		, p_logfile            VARCHAR2 DEFAULT NULL )
		RETURN SELF AS RESULT
	IS
		v_module VARCHAR2(30);
		v_action VARCHAR2(100);
		v_dummy  PLS_INTEGER;
	BEGIN
		message    := p_message;
		error_code := NVL(p_error_code,-20000);

		IF p_logdir IS NOT NULL THEN
			BEGIN
				SELECT directory_name INTO os_logdir
				FROM   all_directories
				WHERE  p_logdir IN (directory_name, directory_path)
				ORDER BY
				       DECODE(p_logdir,
					      directory_name, 1,2);

				BEGIN
					SELECT 1 INTO v_dummy
					FROM   user_tab_privs_recd
					WHERE  table_name = SELF.os_logdir
					AND    privilege = 'WRITE';
				EXCEPTION
					WHEN NO_DATA_FOUND THEN
						RAISE_APPLICATION_ERROR
						( -20999
						, 'User ' || USER
						  || ' lacks write permission to directory "'
						  || p_logdir || '".' );
				END;
			EXCEPTION
				WHEN NO_DATA_FOUND THEN
					RAISE_APPLICATION_ERROR
					( -20999
					, 'No such directory "' || p_logdir ||
					  '" defined in database.' );
			END;
		END IF;

		os_logfile := p_logfile;

		raised     := SYSTIMESTAMP;

		IF p_module IS NULL
		OR p_action IS NULL
		THEN
			DBMS_APPLICATION_INFO.READ_MODULE(v_module, v_action);
			module := NVL(p_module,v_module);
			action := COALESCE(p_action,v_action,'Unknown');
		END IF;
		RETURN;
	END;


	MEMBER PROCEDURE print
	IS
	BEGIN
		ECHO(error_code || ': ' || message);
	END;
END;
/

show errors

CREATE OR REPLACE PACKAGE error
AS
	e_general CONSTANT EXCEPTION_OT := EXCEPTION_OT('An error occurred', -20001);
	e_no_such CONSTANT EXCEPTION_OT := EXCEPTION_OT('No such [1]', -20002);
	e_job     CONSTANT EXCEPTION_OT := EXCEPTION_OT('Job submit error', -20003);
END error;
/

PROMPT Demo EXCEPTION_OT:

CREATE OR REPLACE PROCEDURE test_exception
	( p_test VARCHAR2 )
AS
	e_no_such_thing EXCEPTION_OT := error.e_no_such;
	v_dummy VARCHAR2(1);
BEGIN
	SELECT dummy INTO v_dummy FROM dual WHERE dummy = p_test;
EXCEPTION
	WHEN NO_DATA_FOUND THEN
		-- e_no_such_thing.message := SQLERRM;
		e_no_such_thing.INCLUDE_TEXT('dummy "' || p_test || '"');
		e_no_such_thing.PRINT();
		e_no_such_thing.RAISE();
END;
/

PROMPT Success:
exec TEST_EXCEPTION('X')

PROMPT Failure:
exec TEST_EXCEPTION('Y')