Introduction:
In this article,i am going to explain about how to handle/manage errorsin oracle pl/sql block.

Main:
Normally in pl/sql we will get two kind of exceptions.

1.compilation errors –> compilation errors when you run an anonymous block program or attempt to build a stored program unit—a function, procedure, or user-defined object type

2.Run-Time errors –> compilation errors when you run an anonymous block program or attempt to build a stored program unit—a function, procedure, or user-defined object type.

Compilation Errors

Add a Note HereCompilation errors are generally typing errors. The parsing of your PL/SQL text file into a set of interpreted instructions, known as p-code, finds lexical errors. Lexical errors occur when you misuse a delimiter, identifier, literal, or comment. You can misuse lexical units by

Add a Note HereForgetting a semicolon (the statement terminator)
Add a Note HereUsing only one delimiter when you should use two, as by failing to enclose a string literal
Add a Note HereMisspelling an identifier (reserved words and keywords)
Add a Note HereCommenting out a lexical value required by the parsing rules

Forex,

SQL> BEGIN 2 dbms_output.put_line('Hello World.') 3 END; 4 /

Add a Note HereThis raises the following error message:

Add a Note Here
END;
*
ERROR at line 3:
ORA-06550: line 3, column 1:
PLS-00103: Encountered the symbol “END” when expecting one of the following:
:= . ( % ;
The symbol “;” was substituted for “END” to continue.

In this examble simply we missed a semicolon in dbms_output statement,

SQL> DECLARE 2 a NUMBER := 0; 3 b NUMBER; 4 c NUMBER; 5 BEGIN 6 c := a b; 7 dbms_output.put_line('['||c||']'); 8 END; 9 /

Add a Note HereThe error message displayed is

Add a Note Here
c := a b;
*
ERROR at line 6:
ORA-06550: line 6, column 11:
PLS-00103: Encountered the symbol “B” when expecting one of the following:
. ( * @ % & = – + ; < / > at in is mod remainder not rem
<> or != or ~= >= <= <> and or like LIKE2_
LIKE4_ LIKEC_ between || multiset member SUBMULTISET_
The symbol “.” was substituted for “B” to continue.

Run-time Errors:
Run time errors are occuring while executing the sql block.we need a exception block for capturing the run time errors.

The following demonstrates handling an assignment error raised by trying to put a two-character string into a one-character variable:

DECLARE a VARCHAR2(1); b VARCHAR2(2) := 'AB'; BEGIN a := b; EXCEPTION WHEN value_error THEN dbms_output.put_line('You can''t put ['||b||'] in a one character string.'); END; /

Add a Note HereRunning this program, you generate the following output message when you’ve enabled SERVEROUTPUT in your session:

Add a Note Here
You can’t put [AB] in a one character string.

If you wants to terminate this sqlblock while runtime error,you need to use raise_exception statement instead of dbms_output.put_line,

Conclusion:
Hope this helps,
Happy Coding.