PL/SQL block to compare salary using exception 'value_error'

Q. Write a PL/SQL block to check if any employee from table 'emp' is receiving a salary greater than 9999.99 by using exception 'value_error'.

Answer:

Create a table 'emp':

CREATE TABLE emp ( enpno varchar(4), empname varchar(30), designation varchar2(10), category char(1), basicsalary number(4), joined date )


emp table

The following code will raise an exception only if a user tries to insert salary greater than 99999.99 otherwise accept the values.

DECLARE mysal number;
sal NUMBER:=&salary;
BEGIN
INSERT INTO emp
VALUES('1003',
       'kumar',
       'sr.off',
       's',
       sal,
       sysdate);
exception WHEN value_error THEN dbms_output.put_line('salary is limited to 4 digits only');
WHEN others THEN dbms_output.put_line('un identified error occured');
END;
/


Output:

value error
▲