Error Handling in SQL Server

Exception is an error occurred in Run-time.

Whenever the run-time error is occurred use an appropriate exception name in exception handler under the exception section of the PL/SQL Block.

Oracle Provided three types of exception these are:

I. Predefine Exception.
II. Userdefine Exception.
III. Unnamed exception.

If you want to build your career with a SQL Server certified professional, then visit Mindmajix - A Global online training platform: “SQL Server Training”  Course.  This course will help you to achieve excellence in this domain.

Redefine Exception

Oracle Provided 20 predefined exception name for regularly occurred runtime error.
Whenever the run-time error is occurred use corresponding P.exception name in exception handler and exception section of PL/SQL Block.

Syntax :- 

When predefined exception name1 then stmts;
When predefined exception name2 then stmts;
………..
………..
When others then
stmts;

Predefined exception name:-

1.  no_data_found :-

Whenever PL/SQL Block having select into clause and also if requested data is not available then oracle server returns an error ora – 1403 : no data found. For handling this error oracle provided no_data_found exception name.

Ex :-  declare

v_ename varchor2(10);
v_sal number (10);
begin
select ename, sal into
v_ename, v_sal from emp where empno = & no;
dbms_output. Put_line (v_ename ||” “|| v_sal);

exception
when no_data_found then

dbms_output.Put_line (‘your employee doesn’t exists’);
end;

O/P :-  

enter value for no:7902
Ford 3800
Enter value for no:11
Your employee doesn’t exists.

2. Too_many_rows:-

Whenever select into clause try to return multiple records or try to return multiple values in a single columns at a time then oracle server returns an error ora – 1422 : Exact Fetch returns more than requested number of rows.

For handling this type of error oracle provided too_many_rows exceptionname.

Example:-  Declare

v_sal number (10);
begin
select sal into v_sal From emp;
dbms_output . Put_line (v_sal);

exception
when too_many_rows then

dbms_output . Put_line (‘not to return multiple rows’);
end;

Output :- not to return multiple rows.

3. dup_var_on_index :-

In oracle when we try to insert duplicate value into primary key column or when we try to insert duplicate value or unique constraints column then oracle server returns an error ora-0001 : unique constraints violated for handling error oracle provided dup_val_on_index exceptionname

Example :- 

Begin
Insert into
Emp (empno) values (7902);
Exception
When dup_val_on_index then
Dbms_output . put_line (‘not to insert dublicates data’);
End;

O/P: not to insert duplicates data

Check Out SQL Server Tutorials

4. Invalid_cursor:-

In oracle when we are not open in cursor but we are try to perform operation of the cursor there oracle server returns an error ora-1001 : invalid_cursor for handling this error then we are using invalid_cursor exception name.

Ex:-

Declare
Cursor c1 is select * from emp;
I emp % rowtype;
Begin
Loop
Fetch c1 into i;
Exist when c1 % not_found;
dbms_output . put_line (i.e name ||’ ‘|| i.sal);
end loop;
close c1;

exception
when invalid_cursor then

dbms_output . put_line (‘first we must open cursor’);
end;

5. Cursor_already_open :-

Before we are reopen in the cursor we must close the cursor property otherwise oracle server returns an error ; cursor_already open for handling this error oracle provided cursor_already_open exception name.

Ex:-

Declare
Cursor c1 is select * from emp;
I emp % rowtype;
begin
open c1;
loop
Fetch c1 into I;
Exit when c1 % not found;
Dbms_output . put_line (i.e name ||’ ‘|| i.sal)
End loop;
Open c1;

Exception
When cursor_already_open then

Dbms_output . put_line (‘first we must close the cursor before reopen the cursor’);
End;

6. Zero_divide :-

In oracle when we are try to perform division by zero then oracle server returns an error.
ORA – 1476 : divisor is equal to zero

For handling this type of error then we are using zero_divide exception.

Ex:-

Begin
dbms_output . put_line (3/0);

Exception
when zero_divide then

dbms_output . put_line (‘not to perform division with zero’);
end;

7, 8. Invalid_number, Value_error :-

a) Invalid_number

b) Value_error

Ex :-

Declare
Z number (10);
Begin
Z : = ‘ & x’ + ‘& y’;
dbms_output . put_line (Z);

exception
when value_error then

dbms_output . put_line (‘enter numeric data value for x & y only’);
end;

output:- 

Enter value for x : 3
Enter value for y : 2

Enter value for x : a
Enter value for y : b

II. User-defined exception:-

- In oracle we can also create our own exception name and raised when ever necessary those exception explicitly there type of exception are also called as user_defined exception.

- In all DB’s if we want to raise exception based on client business rule then only we are using user_defined exception.

Handling user-defined Exception in oracle :-

Step 1 – declare
Step 2 – raise
Step 3 – handling exception

Step 1 (declare) :-

In declare section of the PL/SQL Block we are creating our own exception name by using exception predefined type through following

Syntax:-

Syntax-userdefinedname exception;

Ex:-

SQL>declare
a exception;

Frequently Asked SQL Server Interview Questions

Step – 2 (raise) :-

Use in raise statement we can raised user defined exception explicitly either executable section or a exception section of the PL/SQL Block.

Syntax -

Raise userdefined exception name;

Step – 3 (handling userdefine exception):-

When we can also handle user defined exception same like a predefined exception by using exception handler. In exception section of in PL/SQL Block.

Syntax –

when userdefined Exception name1 then
Stmts;
When user defined exception name 2 then
Stmts;
………
……..
When others then
Stmts;

III. Un-named exception:-

- In oracle if we want to handle other than oracle 20 predefine exception name error then we must used in named method.
- In this method we are creating our own exception name and they associates this exception name with appropriate error number by using EXCEPTION_INIT function. This function accepts two parameter.

Syntax –

Pragma exception_init (user define exception name, error name);

- This function is used in declare section of the PL/SQL block.

Note:-

Here Pragma is a compiler directive i.e. whenever we are using pragma oracle servers internally associates error no. with exception name at the time of compilation.

SQL > begin
           Insert into
emp (empno, ename) values (null, ‘suneel);
end;

Error: ORA – 1400 : cannot insert NULL into EMPNO

Solution: 

SQL > declare
A exception;
Pragma exception_init (a, - 1400)
Begin
Insert into
Emp (empno, ename) values (null, ‘suneel);

Exception
When a then

dbms_output . put_line (‘not to insert null values’);
end;

Output: not to insert null values.

SQL > begin
Delete from dept where
Deptno = 10;
End;

ORA – 2292: Integrity constraints violated – child record found.

Solution:-

SQL > declare
a exception;
pragma exception _ init (a, -2292);
begin
delete from dept where deptno = 10;

exception
when a then

dbms_output . put_line (‘not to delete master records’)
end;

output: not to delete master records.

List of Related Microsoft Certification Courses: