Answer Posted / harinadh bolisetti
PL/SQL allows developers to define their own exceptions.
User can define the error/exception programmatically based on the business rule.
1. Define Exception
---------------------
We need to define the exception before we raise and handle. User Exceptions are defined using keyword EXCEPTION in declaration section of the block.
The syntax is as follows
<exception_name> EXCEPTION ;
2. Raise the Exception
--------------------------
Once the exceptions are defined , they need to be raised anywhere in the body depending upon predefined logic. User exceptions are raised using the keyword RAISE.
Syntax is as shown below
RAISE <exception_name>
3. Handle the Exception.
--------------------------
User exception are handled in the same way predefined exceptions are handled. They are handled in exception block using WHEN .. THEN keyword
Syntax is as shown below
WHEN <exception_name> THEN
example
----------------
DECLARE
low_sal EXCEPTION;
min_sal NUMBER:= 10000;
new_sal NUMBER:= 8000;
BEGIN
INSERT INTO EMP_EXC_DEMO(EMPNO, DEPTNO, SAL)
VALUES (4000,20,new_sal);
IF new_sal < min_sal THEN
RAISE low_sal;
END IF;
commit;
EXCEPTION
WHEN low_sal THEN
Rollback;
DBMS_OUTPUT.PUT_LINE ('Salary is less than '||min_sal);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE (SQLERRM);
END;
Is This Answer Correct ? | 2 Yes | 0 No |
Post New Answer View All Answers
Why plvtab is considered as the easiest way to access the pl/sql table?
What is graph sql?
What is plpgsql language?
Why do we use subquery?
write an sql query to find names of employee start with 'a'? : Sql dba
Can you selectively load only those records that you need? : aql loader
Is sql procedural language?
How do I start sql from command line?
Which data type is a composite type?
What are its different types of dbms?
Why primary key is required?
What are analytical functions in sql?
what is data control language? : Sql dba
Can we group by two columns in sql?
Is sql a microsoft product?