PL/SQL Program When Exception Throws Then Insert Messages Into Table

Q. Write PL/SQL Block For When Exception Throws Then Insert Messages Into Table.

Output:

CREATE TABLE emp
(
	EMPNO NUMBER(4) PRIMARY KEY,
	ENAME VARCHAR2(10),
	JOB VARCHAR2(9),
	MGR NUMBER(4),
	HIREDATE DATE,
	SAL NUMBER(7, 2),
	COMM NUMBER(7, 2),
	DEPTNO NUMBER(2)
);

INSERT INTO EMP VALUES (7369, 'SMITH', 'CLERK',    7902, TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800,NULL, 20);

INSERT INTO EMP VALUES (7499, 'ALLEN', 'SALESMAN', 7698, TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30);

INSERT INTO EMP VALUES (7521, 'WARD',  'SALESMAN', 7698, TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30);

INSERT INTO EMP VALUES (7566, 'JONES', 'MANAGER',  7839, TO_DATE('2-APR-1981',  'DD-MON-YYYY'), 2975, NULL, 20);

INSERT INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN', 7698,TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);

INSERT INTO EMP VALUES (7698, 'BLAKE', 'MANAGER', 7839,TO_DATE('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30);

INSERT INTO EMP VALUES (7782, 'CLARK', 'MANAGER', 7839,TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10);

INSERT INTO EMP VALUES (7788, 'SCOTT', 'ANALYST', 7566,TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);

INSERT INTO EMP VALUES (7839, 'KING', 'PRESIDENT', NULL,TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);

INSERT INTO EMP VALUES (7844, 'TURNER', 'SALESMAN', 7698,TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500, 0, 30);

INSERT INTO EMP VALUES (7876, 'ADAMS', 'CLERK', 7788,TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);

INSERT INTO EMP VALUES (7900, 'JAMES', 'CLERK', 7698,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 950, NULL, 30);

INSERT INTO EMP VALUES (7902, 'FORD', 'ANALYST', 7566,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20);

INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK', 7782,TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);

CREATE TABLE messages
(results VARCHAR2(100));

Table created.

SELECT sal FROM emp;

       SAL
----------
       800
      1600
      1250
      2975
      1250
      2850
      2450
      3000
      5000
      1500
      1100

       SAL
----------
       950
      3000
      1300

14 rows selected.

DECLARE
	emp_nm emp.ename%TYPE;
	emp_sal emp.sal%TYPE:='&Input_salary';
BEGIN
	SELECT ename INTO emp_nm
	FROM emp
	WHERE sal = emp_sal;
	INSERT INTO messages(results)
	VALUES(emp_nm||'-'||emp_sal);
EXCEPTION
	WHEN NO_DATA_FOUND THEN
		INSERT INTO messages(results)
		VALUES('No Employee With A Salary Of '||to_char(emp_sal));
	WHEN TOO_MANY_ROWS THEN
		INSERT INTO messages(results)
		VALUES('More Than One Eemployee With A Salary Of '||to_char(emp_sal));
	WHEN others THEN
		INSERT INTO messages (results)
		VALUES('Some Other Error Occured !');
END;
/

Enter value for input_salary: 1000      -- Here 1000 Salary Is Not Alloted To Any Employee In Employee Table.
			              Then NO_DATA_FOUND Exception Throws.

PL/SQL procedure successfully completed.

SELECT * FROM messages;

RESULTS
--------------------------------------------------------------------------------
No Employee With A Salary Of 1000

================================================================================================================================

Enter value for input_salary: 1250   -- Here 1250 salary Is Alloted To More Than One Employees In Employee Table.
			           Then TOO_MANY_ROWS Exception Throws.

PL/SQL procedure successfully completed.

SELECT * FROM messages;

RESULTS
--------------------------------------------------------------------------------
No Employee With A Salary Of 1000

More Than One Eemployee With A Salary Of 1250
================================================================================================================================

Enter value for input_salary: 800     -- Here 800 Salary Is Present Therefore Employee Name And Salary Insert Into Message Table.

PL/SQL procedure successfully completed.

SQL> SELECT * FROM messages;

RESULTS
--------------------------------------------------------------------------------
No Employee With A Salary Of 1000

More Than One Eemployee With A Salary Of 1250

SMITH-800

Compatibility (Tested on):

Oracle Database 10g Express Edition Release 10.2.0.1.0 – Product

Download Now… (.Zip File – 3KB)