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.