028 - [Oracle PL/SQL] Exceptions - Non predefined error

<샘플코드에서 사용한 데이터는 HR 스키마이고, 오라클 설치시 생성할 수 있는 기본 스키마 입니다>

 

 

 

 

 

 

 

 

 

 

 

# 오류 상황을 만들어서 해당 오류 코드를 확인한다.

begin
insert into departments(DEPARTMENT_ID ,DEPARTMENT_NAME)
values (1,null );

end;
----------------------------------------------
ORA-01400: cannot insert NULL into ("HR"."DEPARTMENTS"."DEPARTMENT_NAME")
ORA-06512: at line 2
01400. 00000 -  "cannot insert NULL into (%s)"
*Cause:    An attempt was made to insert NULL into previously listed objects.
*Action:   These objects cannot accept NULL values.



# 동일한 오류코드를 이번에는 정의해서 예외처리에서 사용하는 샘플
declare
    e_insert exception;
    pragma exception_init(e_insert,-01400);
begin
    insert into departments(DEPARTMENT_ID ,DEPARTMENT_NAME)
    values (1,null );
    
    exception 
    when e_insert then
    dbms_output.put_line('insert failed');
    dbms_output.put_line(sqlcode);
    dbms_output.put_line(sqlerrm);
    
    when others then
    null;
end;
------------------------------------------------
insert failed
-1400
ORA-01400: cannot insert NULL into ("HR"."DEPARTMENTS"."DEPARTMENT_NAME")

 

 

 

# now the way for creating this code is wrong
# the update will not executed when there is exp in the first insert

declare
    e_insert exception;
    pragma exception_init(e_insert,-01400);
begin
    insert into departments(DEPARTMENT_ID ,DEPARTMENT_NAME)
    values (1,null );
    
        -- skip this part
        update employees
        set employee_id='ss'
        where employee_id=100;
    
    exception 
        when e_insert then
        dbms_output.put_line('insert failed');
        dbms_output.put_line(sqlcode);
        dbms_output.put_line(sqlerrm);

end;
------------------------------------------------------------
insert failed
-1400
ORA-01400: cannot insert NULL into ("HR"."DEPARTMENTS"."DEPARTMENT_NAME")



# this is the correct code
# Exception handling for each command
declare
    e_insert exception;
    pragma exception_init(e_insert,-01400);
begin     
    begin
        insert into departments(DEPARTMENT_ID ,DEPARTMENT_NAME)
        values (1,null );

        exception 
            when e_insert then
            dbms_output.put_line('insert failed');
            dbms_output.put_line(sqlcode);
            dbms_output.put_line(sqlerrm);
    end;
    
    begin
        update employees
        set employee_id='ss'
        where employee_id=100;

        exception
            when others then
            dbms_output.put_line('update failed');
            dbms_output.put_line(sqlcode);
            dbms_output.put_line(sqlerrm);
    end;
end;