Easy Learning... (Web Development,.NET Technology,Oracle,Silverlight,REST,SQL,Oracle,MySQL,Data Science, Python and many more ...)
Thursday, October 01, 2020
Wednesday, September 30, 2020
RDBMS PLSQL Program 30 Program to handle multiple exceptions
set serveroutput on
DECLARE
veno emp.eno%type;
vename emp.ename%type;
vesalary emp.esalary%type;
BEGIN
veno := '&veno';
SELECT ename, esalary INTO vename, vesalary
FROM emp
WHERE eno = veno;
DBMS_OUTPUT.PUT_LINE ('Name: '|| vename);
DBMS_OUTPUT.PUT_LINE ('Salary: ' || vesalary);
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('No such employee!');
WHEN NOT_LOGGED_ON THEN
dbms_output.put_line('user not logged in ');
WHEN TOO_MANY_ROWS THEN
dbms_output.put_line('multiple records found');
WHEN VALUE_ERROR THEN
dbms_output.put_line('value type of size not appropriate');
WHEN others THEN
dbms_output.put_line('Error!');
END;
/
RDBMS PLSQL Program 29 Program to handle User defined exception
SET serveroutput On
DECLARE
veno emp.eno%type;
vename emp.ename%type;
ex_invalid_no EXCEPTION;
BEGIN
veno := '&veno';
IF substr(veno,1,1) <> 'E' THEN
RAISE ex_invalid_no;
ELSE
SELECT ename INTO vename
FROM emp
WHERE eno = veno;
DBMS_OUTPUT.PUT_LINE ('Name: '|| vename);
END IF;
EXCEPTION
WHEN ex_invalid_no THEN
dbms_output.put_line('ID must start with E');
END;
/
RDBMS PLSQL Program 28 Program to handle User named exception
DECLARE
resource_busy_nowait EXCEPTION;
PRAGMA EXCEPTION_INIT (resource_busy_nowait, -00054);
veno emp.eno%type;
vename emp.ename%type;
BEGIN
veno := '&veno';
vename := '&vename';
SELECT eno, ename into veno, vename
FROM emp
Where eno = veno
FOR UPDATE NOWAIT;
UPDATE emp SET ename = vename WHERE eno = veno;
EXCEPTION
WHEN resource_busy_nowait THEN
Dbms_output.Put_line('emp is locked - please try later');
END;
/
RDBMS PLSQL Program 27 Program to handle oracle named exception
set serveroutput on
DECLARE
veno emp.eno%type;
vename emp.ename%type;
vesalary emp.esalary%type;
BEGIN
veno := '&veno';
SELECT ename, esalary INTO vename, vesalary
FROM emp
WHERE eno = veno;
DBMS_OUTPUT.PUT_LINE ('Name: '|| vename);
DBMS_OUTPUT.PUT_LINE ('Salary: ' || vesalary);
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('No such employee!');
END;
/
RDBMS PLSQL Program 26 Procedure with in and out parameters and program to call it
Procedure
=======================================================
CREATE or REPLACE PROCEDURE pro1(veno in char,temp out emp%rowtype)
IS
BEGIN
SELECT * INTO temp FROM emp WHERE Eno = veno;
END;
/
Program to call above procedure
=======================================================
set serveroutput on
DECLARE
temp emp%rowtype;
veno emp.eno%type;
BEGIN
veno :='&veno';
pro1(veno,temp);
dbms_output.put_line(temp.eno||' -> '||temp.ename||' -> '||temp.edno||' -> '||temp.esalary);
END;
/