pl/sql exception handling

By ukmodak | June 6th 2022 12:23:03 PM | viewed 190 times

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; 
/ 

Exception Oracle Error SQLCODE Description
ACCESS_INTO_NULL 06530 -6530 It is raised when a null object is automatically assigned a value.
CASE_NOT_FOUND 06592 -6592 It is raised when none of the choices in the WHEN clause of a CASE statement is selected, and there is no ELSE clause
COLLECTION_IS_NULL 06531 -6531 It is raised when a program attempts to apply collection methods other than EXISTS to an uninitialized nested table or varray, or the program attempts to assign values to the elements of an uninitialized nested table or varray.
DUP_VAL_ON_INDEX 00001 -1 It is raised when duplicate values are attempted to be stored in a column with unique index.
INVALID_CURSOR 01001 -1001 It is raised when attempts are made to make a cursor operation that is not allowed, such as closing an unopened cursor.
INVALID_NUMBER 01722 -1722 It is raised when the conversion of a character string into a number fails because the string does not represent a valid number.
LOGIN_DENIED 01017 -1017 It is raised when a program attempts to log on to the database with an invalid username or password.
NO_DATA_FOUND 01403 +100 It is raised when a SELECT INTO statement returns no rows.
NOT_LOGGED_ON 01012 -1012 It is raised when a database call is issued without being connected to the database.
PROGRAM_ERROR 06501 -6501 It is raised when PL/SQL has an internal problem.
ROWTYPE_MISMATCH 06504 -6504 It is raised when a cursor fetches value in a variable having incompatible data type.
SELF_IS_NULL 30625 -30625 It is raised when a member method is invoked, but the instance of the object type was not initialized.
STORAGE_ERROR 06500 -6500 It is raised when PL/SQL ran out of memory or memory was corrupted.
TOO_MANY_ROWS 01422 -1422 It is raised when a SELECT INTO statement returns more than one row.
VALUE_ERROR 06502 -6502 It is raised when an arithmetic, conversion, truncation, or sizeconstraint error occurs.
ZERO_DIVIDE 01476 1476 It is raised when an attempt is made to divide a number by zero.
bONEandALL
Visitor

Total : 18976

Today :5

Today Visit Country :

  • Germany
  • Singapore
  • United States
  • Russia