What is Exception Handling in PL/SQL?

An Exception case happens when the PL/SQL, experiences guidance which it can't execute because of an error, that happens at run-time. These errors won't be caught at the hour of gathering, and hence these expected to deal with just at the run-time.

For instance, if PL/SQL Engine gets guidance to partition any number by '0', at that point the PL/SQL treats it as a special case. The special case is just raised at the run-time, by the PL/SQL Engine.

Exceptions will prevent the program, from executing further, so to maintain a strategic distance from such a condition, they should be caught and deal with independence.

This procedure is called Exception-Handling, in which the software engineer handles the special case, that can happen at the run time. get more on pl sql throguh pl sql training

In this article we will learn the following:

1) Important points to note in Exception

2) PL/SQL Raise Exception

3) User-defined Exception

4) Predefined Exceptions

5) Exception-Handling Syntax.

In Function, an Exception ought to in every value, either return value or raise the exception further. else Oracle throws 'Capacity returned without a worth' mistake at run-time.

Transaction control Statements were given, by the Exception handling block.

SQLERRM and SQLCODE are the in-constructed Functions, that will give the exception notification and Script.

On the off chance that an Exception is n't taken care of, at that point as a matter, of course, all the active transactions in that session will be moved back.

RAISE_APPLICATION_ERROR (- <error_code>, <error_message>) can be utilized, rather than RAISE to up the Error with client code and message. Error Script ought to be more worthy than 20000 and prefixed with '- '.

PL/SQL Raise Exception

All the predefined Exceptions are raised certainly, at whatever point the mistake happens. However, the client characterized exceptions should be raised Especially. This can be gifted utilizing the Key phrase 'RAISE'.

On the little chance that 'RAISE' is utilized independently in the program, at that point it will grow the effectively raised exception, to the parent block. Just in Exception block can be utilized. get more through pl sql training online

Note:

While raising the exception to the parent block, the Exception case that is getting raised ought to likewise, be noticed at parent block, else oracle will throw a Notification Error.

We can utilize catchphrase 'RAISE' result by the Exception case, name to raise that specific client characterized/predefined exception. This can be utilized in both the execution part and in an Exception case, dealing to raise the exception.

User-defined Exception

In Oracle, other than the predefined Exception cases, the software engineer can make their own exception and manage them. They can make a subprogram level in the presentation part. These exceptions are noticeable just in that subprogram. The exception that is characterized in the bundle is open Exception, and it is Seen any place, where the bundle is available.

Predefined Exceptions

Oracle has predefined Exceptions. These Exceptions have a one exception name and Error Tag. These Exceptions are now characterized in the 'STANDARD' bundle in Oracle. In code, we can duly utilize these predefined Exception name, to deal with them.

Exception-Handling Syntax

Exceptions are managed with at the block, level, i.e., once on the off chance that any exception happens in a block, at that point the control will leave execution part of that Block.

The Exception case will at that point be managed, with at the exception taking care of part of that block. In the wake of taking care of the exception, it is beyond the domain of imagination to resend control back to the execution segment of that Block.

BEGIN

<execution block>

.

.EXCEPTION 

WHEN <exceptionl_name>

THEN

  <Exception handling code for the “exception 1 _name’' >

WHEN OTHERS

THEN

  <Default exception handling code for all exceptions >

END;

Syntax Explanation 

In the above syntax structure, the Exception deal with the block contains arrangement, of WHEN conditions to deal with the exception. learn on pl sql through pl sql online training

Every WHEN condition is trailed by the exception name, which is built upon to be raised at the run time.

At the point when an Exception is raised at runtime, at that point, the PL/SQL will look in the exception taking care of part, for that specific exception case. It will begin from the main 'WHEN' provision.