Tag Archives: Oracle_APPLICATION_Error

Raise in Oracle_APPLICATION_Error Usage

Probably not many people know what the purpose of RAISE_APPLICATION_ERROR is, although you have guessed literally what this function is for. Normally, we output the exception information through DBMS_output_line for the exception handling used to test, but in the actual application, we need to return the exception information to the calling client.
actually RAISE_APPLICATION_ERROR is the declaration that the application-specific errors are conveyed from the server side to the client application (SQLPLUS on another machine or other front-end development language)
RAISE_APPLICATION_ERROR:
PROCEDURE RAISE_APPLICATION_ERROR( error_number_in IN NUMBER, error_msg_in IN VARCHAR2);
inside the error code and content, are custom. Custom, of course, is not the system has named the existence of the error category, is a custom transaction error type, only called this function. Error_number_in allows tolerance between -20,000 and -20999 so that it does not conflict with any ORACLE error code. The length of error_MSG_in cannot exceed 2k, otherwise intercept 2k. For example,
prevents users under 18 years of age from adding to the database table temp_age.

-- building a watch
create table temp_age(
age_id number(5),
age number(3)
);
-- Build Trigger
create or replace trigger t_temp_age_check
before insert on temp_age
  for each row
    begin
      if :new.age < 18
        then
          raise_application_error(-20001,'age must at least 18 years old');
        end if;
    end;

 

-- client program
declare
  no_baby_allowed exception;
  pragma exception_init(no_baby_allowed,-20001);
  begin
    insert into temp_age(age_id,age) values(1,20);
    insert into temp_age(age_id,age) values(2,17);
    insert into temp_age(age_id,age) values(3,18);
    exception
      when no_baby_allowed
        then
          dbms_output.put_line(sqlerrm);
  end;

Client program execution output results:

Ora-20001: age must at least 18 years old
ora-06512: error during ‘lcam_develop.t_temp_age_check’, line 4
ora-04088: trigger ‘lcam_develop.t_temp_age_check’ execution