Using exception with raise_application_error

oracleoracle-11gplsql

I'm developing with Oracle 11g. How can I use a named exception in RAISE_APPLICATION_ERROR like this:

DECLARE
  my_fault EXCEPTION;
  PRAGMA EXCEPTION_INIT (my_fault, -20000);
BEGIN
  RAISE_APPLICATION_ERROR(my_fault, 'my error message');
END;

But I got an error. I have to use the error code explicity.
The Oracle Database Documentation says:

Restriction on exception

You can use exception only in an EXCEPTION_INIT pragma, RAISE statement, RAISE_APPLICATION_ERROR invocation, or exception handler.

I'm confused.

Best Answer

There are two ways to raise exceptions in Oracle. If you want to specify your own message, you wouldn't declare a local variable of type exception. You'd simply put the error code in your raise_application_error call

BEGIN
  raise_applicaton_error( -20001, 'Some message' );
END;

If you want to declare a local exception type, then you wouldn't use raise_application_error to throw it. You'd just use raise. But then you won't be able to specify a custom message.

DECLARE
  my_fault EXCEPTION;
  PRAGMA EXCEPTION_INIT (my_fault, -20000);
BEGIN
  RAISE my_fault;
END;

If you just don't like hard-coding the error code in your raise_application_error call, you can put that in a local integer variable

declare
  myfault_code integer := -20001;
begin
  raise_application_error( myfault_code, 'Some error' );
end;

Related Solutions

In Oracle how to save a sequence.nextval in a variable to be reused in multiple inserts

I think it goes like this

DECLARE
    ENABLED_USER_ID PLS_INTEGER;
    DISABLED_USER_ID PLS_INTEGER;
BEGIN
    ENABLED_USER_ID := SEQ.NEXTVAL;
    DISABLED_USER_ID := SEQ.NEXTVAL;

    INSERT INTO USERS (ID, USR_NAME)
    VALUES  (ENABLED_USER_ID, 'ANDREW');

    INSERT INTO CAR (CAR_ID, CAR_NAME, USR_ID)
    VALUES (CARSEQ.NEXTVAL, 'FORD', ENABLED_USER_ID);

    INSERT INTO USERS (ID, USR_NAME)
    VALUES  (DISABLED_USER_ID, 'ANDREW');

    INSERT INTO CAR (CAR_ID, CAR_NAME, USR_ID)
    VALUES (CARSEQ.NEXTVAL, 'FORD', DISABLED_USER_ID);
END;
/

Oracle 11g – Why No Mutating Table Error in Trigger

There is an exception. When you define a before insert, row-level trigger on a table and issue a single row INSERT statement, the table is mutating error will not be raised. But if you define the same kind of trigger and issue a multi-row INSERT statement, the error will be raised. Here is an example:

SQL> create table TB_TR_TEST(
  2    col1 number,
  3    col2 number
  4  )
  5  ;

Table created

SQL> create or replace trigger TR_TB_TR_TEST
  2  before insert on TB_TR_TEST
  3  for each row
  4  begin
  5    SELECT max(col1) + 1 INTO :NEW.col1
  6      FROM TB_TR_TEST;
  7    UPDATE TB_TR_TEST SET col2 = 5000;
  8  end;
  9  /

Trigger created

Here is a single-row insert statement, which won't raise mutating table error:

SQL> insert into TB_TR_TEST(col1, col2) values(1,2);

1 row inserted

SQL> insert into TB_TR_TEST(col1, col2) values(3,5);

1 row inserted

SQL> commit;

Commit complete

Here is a multi-row insert statement, which will raise mutating table error:

SQL> insert into TB_TR_TEST(col1, col2)
  2    select 1, 2
  3      from dual;

insert into TB_TR_TEST(col1, col2)
  select 1, 2
    from dual

ORA-04091: table HR.TB_TR_TEST is mutating, trigger/function may not see it
ORA-06512: at "HR.TR_TB_TR_TEST", line 2
ORA-04088: error during execution of trigger 'HR.TR_TB_TR_TEST'

Best Answer

Related Solutions

In Oracle how to save a sequence.nextval in a variable to be reused in multiple inserts

Oracle 11g – Why No Mutating Table Error in Trigger

Related Question