CODEWITHSUNDEEP
oracle-databaseplsqloracleformsraise
DARK_A
DARK_A

Reputation: 575

RAISE_APPLICATION_ERROR doesn't return the message

IF l_value = 'FALSE' THEN
  RAISE_APPLICATION_ERROR(-20299, 'some error message');
END IF;

This is part of table trigger. It should return me a error number and message, but when alert pops out it returns only message number. No 'some error message'. Whats wrong

Upvotes: 0

Views: 11089

Answers (2)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60312

The alert in your form has been raised by some trigger code on your form. Have a look at your ON-ERROR trigger - what code does it have?

You may need to augment it to show DBMS_ERROR_TEXT in the alert.

Upvotes: 2

hol
hol

Reputation: 8423

Maybe the name RAISE_APPLICATION_ERROR is misleading for you. It will not pop up something onto your GUI. That you program yourself depending on what client you are using. Put you can use RAISE_APPLICATION_ERROR to create your own SQL errors on which you act upon.

Example

-- a example table
create table mytest (col_a number, col_b char(20));

-- a example trigger
CREATE OR REPLACE TRIGGER mytest_before
BEFORE UPDATE
    ON mytest
    FOR EACH ROW
DECLARE
BEGIN
    if :new.col_a < 0 then
      RAISE_APPLICATION_ERROR(-20299, 'negative value not allowed for column A');
    end if;
END;

insert into mytest values (1,'hallo');

set serveroutput on
DECLARE
  negative_value EXCEPTION; -- declare exception
  PRAGMA EXCEPTION_INIT (negative_value, -20299); -- assign error code to exception
BEGIN
  update mytest set col_a = -1 where col_b = 'hallo';
EXCEPTION
  WHEN negative_value THEN -- handle exception
    -- do whatever you need to do to bring the error to the user
    DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQLERRM(-20299)));
END;
/

The above will bring you the output in SQL*Plus or SQL Developer of that sort. 

table MYTEST created.
TRIGGER mytest_before compiled
1 rows inserted.
anonymous block completed
ORA-20299: negative value not allowed for column A
ORA-06512: at "DEMO.MYTEST_BEFORE", line 4
ORA-04088: error during execution of trigger 'DEMO.MYTEST_BEFORE

Instead of DBMS_OUTPUT.PUT_LINE you can do whatever you need to do to show the user whatever you want him to show.

Upvotes: 2

Related Questions