An exception is an error condition during a program execution. PL/SQL supports programmers to catch such conditions using EXCEPTION block in the program and an appropriate action is taken against the error condition. There are two types of exceptions −

• System-defined exceptions
• User-defined exceptions

Syntax for Exception Handling

The general syntax for exception handling is as follows. Here you can list down as many exceptions as you can handle. The default exception will be handled using WHEN others THEN −

DECLARE 
   <declarations section> 
BEGIN 
   <executable command(s)> 
EXCEPTION 
   <exception handling goes here > 
   WHEN exception1 THEN  
      exception1-handling-statements  
   WHEN exception2  THEN  
      exception2-handling-statements  
   WHEN exception3 THEN  
      exception3-handling-statements 
   ........ 
   WHEN others THEN 
      exception3-handling-statements 
END;

Exception example

DECLARE 
   c_id ukaccoun.ukcourse.id%type := &cc_id; 
   c_name ukaccoun.ukcourse.Name%type; 
   ex_invalid_id  EXCEPTION; 
BEGIN 
   IF c_id <= 0 THEN 
      RAISE ex_invalid_id; 
   ELSE 
      SELECT  name INTO  c_name
      FROM ukaccoun.ukcourse 
      WHERE id = c_id;
      DBMS_OUTPUT.PUT_LINE ('Name: '||  c_name);  
   END IF; 

EXCEPTION 
   WHEN ex_invalid_id THEN 
      dbms_output.put_line('ID must be greater than zero!'); 
   WHEN no_data_found THEN 
      dbms_output.put_line('No such customer!'); 
   WHEN others THEN 
      dbms_output.put_line('Error!');  
END; 
/

CREATE OR REPLACE function DALERP.get_care_language_translation (p_care_label_id number,P_language_code varchar2) return NVARCHAR2 is 
		cursor all_fabrication is
		select a1.sl_no, a1.ENGLISH,a1.FABRIC_PERCNT
		from
		(
		select a.sl_no,A.LANGUAGE ENGLISH,to_char(a.FABRIC_PERCNT) FABRIC_PERCNT
		From CARE_LABEL_FABRICATION a
		where A.MASTER_ID=p_care_label_id
		and LANGUAGE is not null
		union all
		select 99 sl_no,english,'' FABRIC_PERCNT from CARE_LANGUAGES where id=(select decode(IS_PART_OF_SETS,460,460,null) from CARE_LABEL_MASTER where ID=P_Care_Label_Id)
		union all
		select 100 sl_no,english,'' FABRIC_PERCNT from CARE_LANGUAGES where id=(select decode(EXCLUSIVE_TRIM,418,418,null) from CARE_LABEL_MASTER where ID=P_Care_Label_Id)
		)a1
		order by sl_no asc;

		cursor all_wash is
		select sl_no,ENGLISH
		from
		(
		select a.sl_no,(A.INSTRUCTION) ENGLISH 
		From CARE_LABEL_WASH_INSTRUCTION a
		where A.MASTER_ID=p_care_label_id
		and A.INSTRUCTION is not null
		)
		order by sl_no asc;



		v_fabrication NVARCHAR2(1000);
		v_translation1 NVARCHAR2(1000);

		v_1st_time number;

		v_wash NVARCHAR2(1000);
		v_translation2 NVARCHAR2(1000);

		v_final_translation NVARCHAR2(1000);

		begin


		v_translation1:='';
		v_1st_time:=0;


		for j in all_fabrication loop

				
        BEGIN
		   
		select decode(j.FABRIC_PERCNT,null,upper(substr(max(nvl(TRANSLATIONS,'.....')),1,1)) || substr(max(nvl(TRANSLATIONS,'.....')),2,9999),j.FABRIC_PERCNT||'% ' || upper(substr(max(nvl(TRANSLATIONS,'.....')),1,1)) || substr(max(nvl(TRANSLATIONS,'.....')),2,9999))  
		into v_fabrication
		from TESCO_CARE_LANGUAGES_V a  where a.ENGLISH=j.english and a.language_code=P_language_code;
		  
		  EXCEPTION
				 WHEN OTHERS THEN
				    v_fabrication :='.....';
		END; 
		   
		   
		   if v_1st_time=0 then
			v_translation1:=v_fabrication;
			v_1st_time:=1;
			else
			v_translation1:=concat(concat(v_translation1,' / '),v_fabrication);
			end if;
					 
		end loop;

		for i in all_wash loop

		BEGIN
		
		   select nvl(TRANSLATIONS,'.....') into v_wash from TESCO_CARE_LANGUAGES_V a  where trim(upper(a.english))=trim(upper(i.english)) and language_code=P_language_code;
		   
		   EXCEPTION
				 WHEN OTHERS THEN
				    v_wash :='.....';
		END; 
		   
		   if v_1st_time=0 then
			v_translation2:=v_wash;
			v_1st_time:=1;
			else
			v_translation2:=concat(concat(v_translation2,' / '),v_wash);
			end if;
					 
		end loop;


		v_final_translation:=v_translation1|| v_translation2;

		return trim(v_final_translation);
end;
/

implicit cursor in pl/sql

DECLARE  
   total_rows number(2); 
BEGIN 
   UPDATE ukaccount.ukcourse 
   SET name ='bangladesh' WHERE ID =1; 
   IF sql%notfound THEN 
      dbms_output.put_line('no ukcourse selected'); 
   ELSIF sql%found THEN 
      total_rows := sql%rowcount;
      dbms_output.put_line( total_rows || ' ukcourse selected '); 
   END IF;
    COMMIT;   
END; 
/