Oracle Exception

Oracle Exception

The Oracle database contains the following types of exceptions:

Internally defined are automatically raises by the runtime system. An internally defined exception always has an error code, but does not have a name.

Predefined are internally defined exceptions that PL/SQL has given a name. For example, ORA-01403 (PL/SQL: NO_DATA_FOUND).

The most used predefined exceptions are:

• NO_DATA_FOUND
• TOO_MANY_ROWS
• OTHERS
• DUP_VAL_ON_INDEX
• INVALID_NUMBER
• VALUE_ERROR

User-defined are exceptions declared by user in the declarative part of any PL/SQL anonymous block, subprogram, or package.

Oracle Predefined Exception

declare
   v_name varchar2(255);
begin
   select name into v_name 
   from customers
   where id in (3,4);
exception
  when no_data_found then
  dbms_output.put_line('error: '||sqlerrm);
  when too_many_rows then
  dbms_output.put_line('error: '||sqlerrm);  
end;

Oracle User-defined Exception

declare
	v_count number;
	exception_user_defined exception;
begin

	select count(*) 
	into v_count 
	from customers
	where id = 341;
   
	if v_count=0 then
	raise exception_user_defined;
	end if;
	
	dbms_output.put_line('The count > 0');
	
exception
	when exception_user_defined then
		dbms_output.put_line('The count = 0');
end;