Oracle PL/SQL 引发用户定义的异常和自定义 SQLERRM

是否有可能创建用户定义的异常并能够更改 SQLERRM？

例如:

DECLARE
ex_custom       EXCEPTION;
BEGIN
RAISE ex_custom;
EXCEPTION
WHEN ex_custom THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/

输出为“用户定义的异常”。是否可以更改该消息？

编辑: 这里是一些更多的细节。

我希望这个例子能说明我正在努力做得更好。

DECLARE
l_table_status      VARCHAR2(8);
l_index_status      VARCHAR2(8);
l_table_name        VARCHAR2(30) := 'TEST';
l_index_name        VARCHAR2(30) := 'IDX_TEST';
ex_no_metadata      EXCEPTION;
BEGIN


BEGIN
SELECT  STATUS
INTO    l_table_status
FROM    USER_TABLES
WHERE   TABLE_NAME      = l_table_name;
EXCEPTION
WHEN NO_DATA_FOUND THEN
-- raise exception here with message saying
-- "Table metadata does not exist."
RAISE ex_no_metadata;
END;


BEGIN
SELECT  STATUS
INTO    l_index_status
FROM    USER_INDEXES
WHERE   INDEX_NAME      = l_index_name;
EXCEPTION
WHEN NO_DATA_FOUND THEN
-- raise exception here with message saying
-- "Index metadata does not exist."
RAISE ex_no_metadata;
END;


EXCEPTION
WHEN ex_no_metadata THEN
DBMS_OUTPUT.PUT_LINE('Exception will be handled by handle_no_metadata_exception(SQLERRM) procedure here.');
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/

实际上，这样的子块有几十个。我想知道是否有一种方法可以为每个子块引发一个用户定义的异常，但是让它给出一个不同的消息，而不是为每个子块创建一个单独的用户定义的异常。

在.NET 中，这有点像拥有一个这样的自定义异常:

    public class ColorException : Exception
{
public ColorException(string message)
: base(message)
{
}
}

然后，一个方法会有这样的东西:

        if (isRed)
{
throw new ColorException("Red is not allowed!");
}


if (isBlack)
{
throw new ColorException("Black is not allowed!");
}


if (isBlue)
{
throw new ColorException("Blue is not allowed!");
}
338394 次浏览
小开

You could use RAISE_APPLICATION_ERROR like this:

DECLARE
ex_custom       EXCEPTION;
BEGIN
RAISE ex_custom;
EXCEPTION
WHEN ex_custom THEN
RAISE_APPLICATION_ERROR(-20001,'My exception was raised');
END;
/

That will raise an exception that looks like:

ORA-20001: My exception was raised

The error number can be anything between -20001 and -20999.

小开
最佳答案

Yes. You just have to use the RAISE_APPLICATION_ERROR function. If you also want to name your exception, you'll need to use the EXCEPTION_INIT pragma in order to associate the error number to the named exception. Something like

SQL> ed
Wrote file afiedt.buf


1  declare
2    ex_custom EXCEPTION;
3    PRAGMA EXCEPTION_INIT( ex_custom, -20001 );
4  begin
5    raise_application_error( -20001, 'This is a custom error' );
6  exception
7    when ex_custom
8    then
9      dbms_output.put_line( sqlerrm );
10* end;
SQL> /
ORA-20001: This is a custom error


PL/SQL procedure successfully completed.
小开 
declare
z exception;


begin
if to_char(sysdate,'day')='sunday' then
raise z;
end if;


exception
when z then
dbms_output.put_line('to day is sunday');
end;
小开

I usually lose track of all of my -20001-type error codes, so I try to consolidate all my application errors into a nice package like such:

SET SERVEROUTPUT ON


CREATE OR REPLACE PACKAGE errors AS
invalid_foo_err EXCEPTION;
invalid_foo_num NUMBER := -20123;
invalid_foo_msg VARCHAR2(32767) := 'Invalid Foo!';
PRAGMA EXCEPTION_INIT(invalid_foo_err, -20123);  -- can't use var >:O


illegal_bar_err EXCEPTION;
illegal_bar_num NUMBER := -20156;
illegal_bar_msg VARCHAR2(32767) := 'Illegal Bar!';
PRAGMA EXCEPTION_INIT(illegal_bar_err, -20156);  -- can't use var >:O


PROCEDURE raise_err(p_err NUMBER, p_msg VARCHAR2 DEFAULT NULL);
END;
/


CREATE OR REPLACE PACKAGE BODY errors AS
unknown_err EXCEPTION;
unknown_num NUMBER := -20001;
unknown_msg VARCHAR2(32767) := 'Unknown Error Specified!';


PROCEDURE raise_err(p_err NUMBER, p_msg VARCHAR2 DEFAULT NULL) AS
v_msg VARCHAR2(32767);
BEGIN
IF p_err = unknown_num THEN
v_msg := unknown_msg;
ELSIF p_err = invalid_foo_num THEN
v_msg := invalid_foo_msg;
ELSIF p_err = illegal_bar_num THEN
v_msg := illegal_bar_msg;
ELSE
raise_err(unknown_num, 'USR' || p_err || ': ' || p_msg);
END IF;


IF p_msg IS NOT NULL THEN
v_msg := v_msg || ' - '||p_msg;
END IF;


RAISE_APPLICATION_ERROR(p_err, v_msg);
END;
END;
/

Then call errors.raise_err(errors.invalid_foo_num, 'optional extra text') to use it, like such:

BEGIN
BEGIN
errors.raise_err(errors.invalid_foo_num, 'Insufficient Foo-age!');
EXCEPTION
WHEN errors.invalid_foo_err THEN
dbms_output.put_line(SQLERRM);
END;


BEGIN
errors.raise_err(errors.illegal_bar_num, 'Insufficient Bar-age!');
EXCEPTION
WHEN errors.illegal_bar_err THEN
dbms_output.put_line(SQLERRM);
END;


BEGIN
errors.raise_err(-10000, 'This Doesn''t Exist!!');
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
END;
END;
/

produces this output:

ORA-20123: Invalid Foo! - Insufficient Foo-age!
ORA-20156: Illegal Bar! - Insufficient Bar-age!
ORA-20001: Unknown Error Specified! - USR-10000: This Doesn't Exist!!
小开 
create or replace PROCEDURE PROC_USER_EXP
AS
duplicate_exp EXCEPTION;
PRAGMA EXCEPTION_INIT( duplicate_exp, -20001 );
LVCOUNT NUMBER;
BEGIN
SELECT COUNT(*) INTO LVCOUNT FROM JOBS WHERE JOB_TITLE='President';
IF LVCOUNT >1 THEN
raise_application_error( -20001, 'Duplicate president customer excetpion' );
END IF;


EXCEPTION
WHEN duplicate_exp THEN
DBMS_OUTPUT.PUT_LINE(sqlerrm);
END PROC_USER_EXP;

ORACLE 11g output will be like this:

Connecting to the database HR.
ORA-20001: Duplicate president customer excetpion
Process exited.
Disconnecting from the database HR

提交答案