How to handle errors in Stored Procedures.

Answers were Sorted based on User's Feedback



How to handle errors in Stored Procedures...

Answer / senthil

Error handling means if our stored procedure generates any error while running,we can handle that error.So that it will not show the error.

For eg.

insert into emp(sno,name,age) values(10,'Vasanth',26);

Consider field "sno" is primary key.When we are giving duplicate input to the sno it will show the error.

If you dont want to show the error ,you can capture the error and display as below.

BEGIN TRY
insert into emp(sno,name,age) values(10,'Vasanth',26);

END TRY

BEGIN CATCH
SELECT @err = @@error
IF @err <> 0
BEGIN
RETURN @err
END
END CATCH

Here in this case,it will capture the error and display.

Is This Answer Correct ?    12 Yes 1 No

How to handle errors in Stored Procedures...

Answer / saraswathi muthuraman

If the procedure execution fails the oracle will quit the
execution with an error.

This error can be handled with in store procedure using
"exception".

declare
test_excep_name exception;
x number;
Begin
select emp_no into x from emp_test where emp_no=1;
If SQL%NOTFOUND then
raise test_excep_name;
end if;

exception
when test_excep_name then
dbms_output.put_line(' Error occurred during execution' || '
SQL error code is ' || sqlcode || ' SQL error maessage '||
sqlerrm);
when others then
dbms_output.put_line(' Error occurred during execution- This
is unknown error ' || ' SQL error code is ' || sqlcode || '
SQL error maessage '|| sqlerrm);
end;
/

Result :

Error occurred during execution- This is unknown error SQL
error code is 100
SQL error maessage ORA-01403: no data found

Is This Answer Correct ?    0 Yes 0 No

Post New Answer

More SQL Server Interview Questions

What is blocking in SQL Server? If this situation occurs how to troubleshoot this issue

2 Answers   IBM,


How many jobs will create for Mirroring, Log Shipping, and Transactional Replication?

4 Answers   IBM,


What is the difference between online clustering and Offline clustering?

0 Answers   IBM,


Explain about protocol layer present in SQL server?

0 Answers  


Explain about unique identifier data type in sql server?

0 Answers  






What is the difference between Stored Procedure , Function and Package, 1. how many blocks in Package and what are they.

5 Answers   IBM,


What do you mean by sql server agent?

0 Answers  


what information is maintained within the msdb database? : Sql server administration

0 Answers  


Why foreign key column allowing null values even a parent tables reference key column not having null value..

2 Answers   IBM,


What value could be assigned to Varchar Type?

3 Answers  


can any body tell me how to know the password of current user in sql server

0 Answers   Crea, HCL,


Does view occupy space?

0 Answers  


Categories