How to handle errors in Stored Procedures.
Answers were Sorted based on User's Feedback
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 |
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 |
How to remove duplicate rows from table?
How to restart SQL Server in single user mode? How to start SQL Server in minimal configuration mode?
explain different types of constraints? : Sql server database administration
What is 2nf example?
What is the disadvantages of index?
How do you trace the traffic hitting a sql server?
Normalization and denormalization
2) Consider a Table name A which has below records ID --- 5 5 5 5 5 Consider another table B which has below records ID -- 5 5 5 5 5 5 5 5 How many rows will be returned by each of the below queries a) select * from A inner join B on A.id = b.ID b) select * from A left join B on A.id = b.ID c) select * from A right join B on A.id = b.ID
How would you use user_constraints table in DB?
How to view existing indexes on an given table using sys.indexes?
What is Cross Join and in which scenario do we use Cross Join?
Why normalization is used?