Oracle PL/SQL-使用自定义SQLERRM引发用户定义的异常 [英] Oracle PL/SQL - Raise User-Defined Exception With Custom 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;
/

实际上，这些子块有数十个.我想知道是否有一种方法可以针对每个要提出的子块有一个用户定义的异常，但又要给出不同的消息，而不是为每个子块创建单独的用户定义的异常. /p>

在.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!");
        }

解决方案

是.您只需要使用RAISE_APPLICATION_ERROR函数.如果还想命名异常，则需要使用EXCEPTION_INIT pragma以便将错误号与命名异常关联.像

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.

Is it possible to create user-defined exceptions and be able to change the SQLERRM?

For example:

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

The output is "User-Defined Exception". Is it possible to change that message?

EDIT: Here is some more detail.

I hope this one illustrates what I'm trying to do better.

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;
/

In reality, there are dozens of those sub-blocks. I'm wondering if there's a way to have a single user-defined exception for each of those sub-blocks to raise, but have it give a different message, instead of creating a separate user-defined exception for each sub-block.

In .NET, it would be sort of like having a custom exception like this:

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

And then, a method would have something like this:

        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!");
        }

解决方案

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.

这篇关于Oracle PL/SQL-使用自定义SQLERRM引发用户定义的异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！