1.

Procedure to display employee details using empno

CREATE OR REPLACE PROCEDURE emp_details_proc

(
p_empno IN [Link]%TYPE
)
IS
v_ename [Link]%TYPE;
v_sal [Link]%TYPE;
v_deptno [Link]%TYPE;
BEGIN
SELECT ename, sal, deptno
INTO v_ename, v_sal, v_deptno
FROM emp
WHERE empno = p_empno;

DBMS_OUTPUT.PUT_LINE('Employee Name : ' || v_ename);

DBMS_OUTPUT.PUT_LINE('Salary : ' || v_sal);
DBMS_OUTPUT.PUT_LINE('Department No : ' || v_deptno);

EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee not found');
END;
/

2. Function to add two numbers

CREATE OR REPLACE FUNCTION Add_Num

(
a NUMBER,
b NUMBER
)
RETURN NUMBER
IS
BEGIN
RETURN a + b;
END;
/

Function Call

SELECT Add_Num(10,20) FROM dual;

3. Procedure to display all procedures and functions using cursor

CREATE OR REPLACE PROCEDURE list_objects

IS
CURSOR c1 IS
SELECT DISTINCT name, type
FROM user_source
WHERE type IN ('PROCEDURE','FUNCTION');

v_name user_source.name%TYPE;
v_type user_source.type%TYPE;

BEGIN
OPEN c1;

LOOP
FETCH c1 INTO v_name, v_type;
EXIT WHEN c1%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(v_type || ' : ' || v_name);

END LOOP;

CLOSE c1;
END;
/

Procedure:

1. Create a procedure ADD_DEPT. This procedure will take 3 IN

arguments DEPTNO, DNAME, LOC. Insert these values into the
DEPT table. Handle the exception if the user tries to insert a
duplicate row using User Defined Exception Handler.

CREATE OR REPLACE PROCEDURE ADD_DEPT

(
p_deptno IN [Link]%TYPE,
p_dname IN [Link]%TYPE,
p_loc IN [Link]%TYPE
)
IS
duplicate_dept EXCEPTION;
BEGIN
INSERT INTO dept(deptno,dname,loc)
 VALUES(p_deptno,p_dname,p_loc);

DBMS_OUTPUT.PUT_LINE('Department Inserted');

EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
RAISE duplicate_dept;

WHEN duplicate_dept THEN

DBMS_OUTPUT.PUT_LINE('Duplicate Department Number');
END;
/

2. Create a procedure UPDATE_DEPT and takes 3 arguments

DEPTNO, DNAME, LOC. Update the DNAME, LOC for the
corresponding DEPTNO. Handle exception when the user enters
DEPTNO that does not exist.

CREATE OR REPLACE PROCEDURE UPDATE_DEPT

(
p_deptno IN [Link]%TYPE,
p_dname IN [Link]%TYPE,
p_loc IN [Link]%TYPE
)
IS
BEGIN
UPDATE dept
SET dname = p_dname,
loc = p_loc
WHERE deptno = p_deptno;

IF SQL%ROWCOUNT = 0 THEN
RAISE NO_DATA_FOUND;
END IF;

DBMS_OUTPUT.PUT_LINE('Department Updated');

EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Department Number Not Found');
END;
/
3. Create a procedure DELETE_DEPT which takes 1 argument
DEPTNO. Delete the record for the corresponding DEPTNO. Handle
the required exception when the user enters a non-existing
DEPTNO.

CREATE OR REPLACE PROCEDURE DELETE_DEPT

(
p_deptno IN [Link]%TYPE
)
IS
BEGIN
DELETE FROM dept
WHERE deptno = p_deptno;

IF SQL%ROWCOUNT = 0 THEN
RAISE NO_DATA_FOUND;
END IF;

DBMS_OUTPUT.PUT_LINE('Department Deleted');

EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Department Number Does Not Exist');
END;
/

4. Create a user WIPRO. Modify your procedure ADD_DEPT to

accept CURRENT USER as an IN parameter. Grant EXECUTE
permission on your procedure to WIPRO user. WIPRO user must
have the DEPT table. Connect to WIPRO user and call the
procedure ADD_DEPT. Check that a new dept is added to the
WIPRO user and not to SCOTT user.

Create User

CREATE USER wipro IDENTIFIED BY wipro;

GRANT CONNECT, RESOURCE TO wipro;

GRANT CREATE SESSION TO wipro;

Modified Procedure
CREATE OR REPLACE PROCEDURE ADD_DEPT
(
p_user IN VARCHAR2,
p_deptno IN NUMBER,
p_dname IN VARCHAR2,
p_loc IN VARCHAR2
)
IS
BEGIN
EXECUTE IMMEDIATE
'INSERT INTO '||p_user||'.dept(deptno,dname,loc)
VALUES(:1,:2,:3)'
USING p_deptno,p_dname,p_loc;

DBMS_OUTPUT.PUT_LINE('Department Added');
END;
/

Grant Permission

GRANT EXECUTE ON ADD_DEPT TO wipro;

Connect and Execute

CONNECT wipro/wipro;

EXEC scott.ADD_DEPT('WIPRO',50,'HR','CHENNAI');

5. Modify the DEPT table to add a new column X. This will make
the dependent procedures INVALID. Write a query and check the
STATUS of ADD_DEPT, UPDATE_DEPT and DELETE_DEPT. Now make
all these procedures VALID and check the STATUS once again.
Drop Column X.

Add Column

ALTER TABLE dept ADD x NUMBER;

Check Status

SELECT object_name,status
FROM user_objects
WHERE object_name IN
('ADD_DEPT','UPDATE_DEPT','DELETE_DEPT');

Recompile Procedures
ALTER PROCEDURE ADD_DEPT COMPILE;

ALTER PROCEDURE UPDATE_DEPT COMPILE;

ALTER PROCEDURE DELETE_DEPT COMPILE;

Check Status Again

SELECT object_name,status
FROM user_objects
WHERE object_name IN
('ADD_DEPT','UPDATE_DEPT','DELETE_DEPT');

Drop Column

ALTER TABLE dept DROP COLUMN x;

6. WRAP the ADD_DEPT procedure and test your procedure.

wrap iname=add_dept.sql oname=add_dept.plb

7. Create a Procedure that will insert into the EMP_TEST table the
employees working in a given deptno and also update the
employees salary in EMP table by 20% in EMP table. Use
Parameter Cursor, FOR LOOP Cursor, UPDATE clause Cursor.

CREATE OR REPLACE PROCEDURE EMP_PROC

(
p_deptno NUMBER
)
IS
CURSOR c1(v_deptno NUMBER) IS
SELECT *
FROM emp
WHERE deptno = v_deptno
FOR UPDATE;

BEGIN
FOR i IN c1(p_deptno)
LOOP
INSERT INTO emp_test
VALUES([Link],[Link],[Link],[Link],
[Link],[Link],[Link],[Link]);
 UPDATE emp
SET sal = sal + sal*0.20
WHERE CURRENT OF c1;
END LOOP;

DBMS_OUTPUT.PUT_LINE('Records Inserted and Salary Updated');

END;
/

8. Create a Procedure that will delete all the employees from EMP
table for a given deptno and also insert those employees in TEST
table. Use Parameter Cursor, UPDATE Clause Cursor, FOR LOOP
Cursor.

CREATE OR REPLACE PROCEDURE DELETE_EMP_PROC

(
p_deptno NUMBER
)
IS
CURSOR c1(v_deptno NUMBER) IS
SELECT *
FROM emp
WHERE deptno = v_deptno
FOR UPDATE;

BEGIN
FOR i IN c1(p_deptno)
LOOP
INSERT INTO test
VALUES([Link],[Link],[Link],[Link],
[Link],[Link],[Link],[Link]);

DELETE FROM emp

WHERE CURRENT OF c1;
END LOOP;

DBMS_OUTPUT.PUT_LINE('Employees Deleted and Stored in TEST');

END;
/
9. Create a procedure GET_EMP that will take EMPNO (IN), ENAME
(OUT), SAL (OUT). Handle the required exceptions. Call the
procedure from SQL Prompt by defining 2 session variables and
display the output for EMPNO 7788.

Procedure

CREATE OR REPLACE PROCEDURE GET_EMP

(
p_empno IN [Link]%TYPE,
p_ename OUT [Link]%TYPE,
p_sal OUT [Link]%TYPE
)
IS
BEGIN
SELECT ename,sal
INTO p_ename,p_sal
FROM emp
WHERE empno = p_empno;

EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee Not Found');
END;
/

Execute from SQL Prompt

VARIABLE v_name VARCHAR2(20);

VARIABLE v_sal NUMBER;

EXEC GET_EMP(7788,:v_name,:v_sal);

PRINT v_name;

PRINT v_sal;

10. Write a procedure that takes an IN OUT argument for Mobile

Number. Pass the Mobile Number as 9999999999 and the
procedure should return (999)999-9999.

CREATE OR REPLACE PROCEDURE FORMAT_MOBILE

(
p_mobile IN OUT VARCHAR2
)
IS
BEGIN
p_mobile :=
'(' || SUBSTR(p_mobile,1,3) || ')' ||
SUBSTR(p_mobile,4,3) || '-' ||
SUBSTR(p_mobile,7,4);
END;
/

Execute Procedure

DECLARE
v_mobile VARCHAR2(20) := '9999999999';
BEGIN
FORMAT_MOBILE(v_mobile);

DBMS_OUTPUT.PUT_LINE(v_mobile);
END;
/

Function:

1. Create a Function GET_JOB that returns JOB of an Employee.

Define a session variable TITLE and print the value.

CREATE OR REPLACE FUNCTION GET_JOB

(
p_empno NUMBER
)
RETURN VARCHAR2
IS
v_job [Link]%TYPE;
BEGIN
SELECT job
INTO v_job
FROM emp
WHERE empno = p_empno;

RETURN v_job;
END;
/

Execute

VARIABLE title VARCHAR2(35);

EXEC :title := GET_JOB(7788);

PRINT title;

2. Create a function GET_ANNUAL_SAL that calculates annual

salary.

CREATE OR REPLACE FUNCTION GET_ANNUAL_SAL

(
p_sal NUMBER
)
RETURN NUMBER
IS
BEGIN
RETURN p_sal * 12;
END;
/

Call Function

SELECT empno,ename,sal,
GET_ANNUAL_SAL(sal) annual_salary
FROM emp
WHERE deptno = 30;

3. Create a function VALID_DEPTNO that returns TRUE if DEPTNO

exists.

CREATE OR REPLACE FUNCTION VALID_DEPTNO

(
p_deptno NUMBER
)
RETURN BOOLEAN
IS
v_count NUMBER;
BEGIN
SELECT COUNT(*)
INTO v_count
FROM dept
WHERE deptno = p_deptno;

IF v_count > 0 THEN

RETURN TRUE;
 ELSE
RETURN FALSE;
END IF;
END;
/

4. Create a procedure ADD_EMP using VALID_DEPTNO function.

CREATE OR REPLACE PROCEDURE ADD_EMP

(
p_empno NUMBER,
p_ename VARCHAR2,
p_sal NUMBER,
p_deptno NUMBER
)
IS
BEGIN
IF VALID_DEPTNO(p_deptno) THEN

INSERT INTO emp(empno,ename,sal,deptno)

VALUES(p_empno,p_ename,p_sal,p_deptno);

DBMS_OUTPUT.PUT_LINE('Employee Inserted');

ELSE
DBMS_OUTPUT.PUT_LINE('Invalid Department Number');
END IF;
END;
/

5. Create a Function CAL_REVERSE without using REVERSE

function.

CREATE OR REPLACE FUNCTION CAL_REVERSE

(
p_str VARCHAR2
)
RETURN VARCHAR2
IS
v_rev VARCHAR2(100) := '';
BEGIN
FOR i IN REVERSE 1..LENGTH(p_str)
 LOOP
v_rev := v_rev || SUBSTR(p_str,i,1);
END LOOP;

RETURN v_rev;
END;
/

Execute

SELECT CAL_REVERSE('ORACLE')
FROM dual;

6. Create a function CAL_PCT that calculates employee

percentage salary.

CREATE OR REPLACE FUNCTION CAL_PCT

(
p_empno NUMBER,
p_pct NUMBER DEFAULT 16
)
RETURN NUMBER
IS
v_sal NUMBER;
BEGIN
SELECT sal
INTO v_sal
FROM emp
WHERE empno = p_empno;

RETURN v_sal * p_pct/100;

END;
/

Execute

SELECT empno,ename,
CAL_PCT(empno) percentage_salary
FROM emp;

7. Create a function that returns TRUE if SAL is less than average

salary.
CREATE OR REPLACE FUNCTION CHECK_SAL
(
p_sal NUMBER
)
RETURN BOOLEAN
IS
v_avg NUMBER;
BEGIN
SELECT AVG(sal)
INTO v_avg
FROM emp;

IF p_sal < v_avg THEN

RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END;
/

8. Create procedure REC_INSUPD.

CREATE OR REPLACE PROCEDURE REC_INSUPD

(
p_empno NUMBER,
p_ename VARCHAR2,
p_sal NUMBER
)
IS
BEGIN
IF CHECK_SAL(p_sal) THEN

MERGE INTO emp e

USING dual
ON ([Link] = p_empno)

WHEN MATCHED THEN

UPDATE SET
ename = p_ename,
sal = p_sal

WHEN NOT MATCHED THEN

 INSERT(empno,ename,sal)
VALUES(p_empno,p_ename,p_sal);

DBMS_OUTPUT.PUT_LINE('Record Inserted/Updated');

ELSE
DBMS_OUTPUT.PUT_LINE('Salary Greater than Average Salary');
END IF;
END;
/

9. Create a function CAL_LDATE that returns joining date in

German.

CREATE OR REPLACE FUNCTION CAL_LDATE

(
p_empno NUMBER
)
RETURN VARCHAR2
IS
v_date VARCHAR2(100);
BEGIN
SELECT TO_CHAR(hiredate,
'DAY, MONTH DD, YYYY',
'NLS_DATE_LANGUAGE=GERMAN')
INTO v_date
FROM emp
WHERE empno = p_empno;

RETURN v_date;
END;
/

Execute

SELECT CAL_LDATE(7788)
FROM dual;

10. Create a function CAL_WORDS that returns numeric value in

words.

CREATE OR REPLACE FUNCTION CAL_WORDS

(
 p_num NUMBER
)
RETURN VARCHAR2
IS
BEGIN
RETURN TO_CHAR(TO_DATE(p_num,'J'),'JSP');
END;
/

Execute

SELECT CAL_WORDS(125)
FROM dual;

11. Wrap the function GET_JOB and test using USER_SOURCE.

Wrap Command

wrap iname=get_job.sql oname=get_job.plb

Check USER_SOURCE

SELECT text
FROM user_source
WHERE name='GET_JOB';

Packages:

1. Create Package PK1 with Procedures ADD_DEPT, UPDATE_DEPT,

DELETE_DEPT and Local Function CAL_CNT

Package Specification

CREATE OR REPLACE PACKAGE PK1

IS
PROCEDURE ADD_DEPT
(
dno NUMBER,
name VARCHAR2,
lo VARCHAR2
);

PROCEDURE UPDATE_DEPT
(
dno NUMBER,
name VARCHAR2,
 lo VARCHAR2
);

PROCEDURE DELETE_DEPT
(
dno NUMBER
);

PROCEDURE TOTAL_ROWS;
END PK1;
/

Package Body

CREATE OR REPLACE PACKAGE BODY PK1

IS

FUNCTION CAL_CNT
(
p_dno NUMBER
)
RETURN BOOLEAN
IS
v_cnt NUMBER;
BEGIN
SELECT COUNT(*)
INTO v_cnt
FROM dept
WHERE deptno = p_dno;

IF v_cnt = 0 THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END;

PROCEDURE ADD_DEPT
(
dno NUMBER,
name VARCHAR2,
lo VARCHAR2
)
IS
BEGIN
IF CAL_CNT(dno) THEN

INSERT INTO dept

VALUES(dno,name,lo);

DBMS_OUTPUT.PUT_LINE('Department Inserted');

ELSE
DBMS_OUTPUT.PUT_LINE('Duplicate Department Number');
END IF;
END;

PROCEDURE UPDATE_DEPT
(
dno NUMBER,
name VARCHAR2,
lo VARCHAR2
)
IS
BEGIN
IF NOT CAL_CNT(dno) THEN

UPDATE dept
SET dname = name,
loc = lo
WHERE deptno = dno;

DBMS_OUTPUT.PUT_LINE('Department Updated');

ELSE
DBMS_OUTPUT.PUT_LINE('Department Number Not Exists');
END IF;
END;

PROCEDURE DELETE_DEPT
(
dno NUMBER
)
IS
BEGIN
 IF NOT CAL_CNT(dno) THEN

DELETE FROM dept

WHERE deptno = dno;

DBMS_OUTPUT.PUT_LINE('Department Deleted');

ELSE
DBMS_OUTPUT.PUT_LINE('Department Number Not Exists');
END IF;
END;

PROCEDURE TOTAL_ROWS
IS
v_total NUMBER;
BEGIN
SELECT COUNT(*)
INTO v_total
FROM dept;

DBMS_OUTPUT.PUT_LINE('Total Rows = ' || v_total);

END;

END PK1;
/

2. Create Package PK2 with Global Cursor

Package Specification

CREATE OR REPLACE PACKAGE PK2

IS
CURSOR c1 IS
SELECT *
FROM emp;

PROCEDURE DISP_FIRST3;

PROCEDURE DISP_FIRST6;
END PK2;
/
Package Body

CREATE OR REPLACE PACKAGE BODY PK2

IS

PROCEDURE DISP_FIRST3
IS
v_count NUMBER := 0;
BEGIN
FOR i IN c1
LOOP
v_count := v_count + 1;

DBMS_OUTPUT.PUT_LINE([Link] || ' ' || [Link]);

EXIT WHEN v_count = 3;

END LOOP;
END;

PROCEDURE DISP_FIRST6
IS
v_count NUMBER := 0;
BEGIN
FOR i IN c1
LOOP
v_count := v_count + 1;

DBMS_OUTPUT.PUT_LINE([Link] || ' ' || [Link]);

EXIT WHEN v_count = 6;

END LOOP;
END;

END PK2;
/

3. Create Sequence S2

CREATE SEQUENCE S2
START WITH 1
INCREMENT BY 1;
4. Create Package PK3 with Overloaded Procedures

Package Specification

CREATE OR REPLACE PACKAGE PK3

IS

PROCEDURE ADD_EMP
(
p_empno NUMBER,
p_ename VARCHAR2 DEFAULT 'UNK',
p_sal NUMBER DEFAULT 1000
);

PROCEDURE ADD_EMP
(
p_ename VARCHAR2 DEFAULT 'UNK',
p_sal NUMBER DEFAULT 1000
);

END PK3;
/

Package Body

CREATE OR REPLACE PACKAGE BODY PK3

IS

PROCEDURE ADD_EMP
(
p_empno NUMBER,
p_ename VARCHAR2 DEFAULT 'UNK',
p_sal NUMBER DEFAULT 1000
)
IS
BEGIN
INSERT INTO emp(empno,ename,sal)
VALUES(p_empno,p_ename,p_sal);

DBMS_OUTPUT.PUT_LINE('Employee Inserted');
END;

PROCEDURE ADD_EMP
 (
p_ename VARCHAR2 DEFAULT 'UNK',
p_sal NUMBER DEFAULT 1000
)
IS
BEGIN
INSERT INTO emp(empno,ename,sal)
VALUES([Link],p_ename,p_sal);

DBMS_OUTPUT.PUT_LINE('Employee Inserted using Sequence');

END;

END PK3;
/

Execute Procedures

PK1

EXEC PK1.ADD_DEPT(50,'HR','CHENNAI');

EXEC PK1.UPDATE_DEPT(50,'SALES','DELHI');

EXEC PK1.DELETE_DEPT(50);

EXEC PK1.TOTAL_ROWS;

PK2

EXEC PK2.DISP_FIRST3;

EXEC PK2.DISP_FIRST6;

PK3

EXEC PK3.ADD_EMP(1001,'RAM',5000);

EXEC PK3.ADD_EMP('RAJA',3000);

Trigger:

1. Create a BEFORE Trigger that restricts DML operations between

9 AM to 17 PM on EMP table.
CREATE OR REPLACE TRIGGER TRG_EMP_RESTRICT
BEFORE INSERT OR UPDATE OR DELETE
ON emp
BEGIN
IF TO_NUMBER(TO_CHAR(SYSDATE,'HH24'))
BETWEEN 9 AND 17 THEN

RAISE_APPLICATION_ERROR
(-20001,
'DML Operations Not Allowed Between 9 AM and 5 PM');
END IF;
END;
/

2. Create AUDIT_EMP table.

CREATE TABLE audit_emp

(
empno NUMBER,
username VARCHAR2(30),
trdate DATE,
trtype CHAR(1)
);

3. Create Trigger for INSERT, UPDATE, DELETE operations on EMP

table using Autonomous Transaction.

CREATE OR REPLACE TRIGGER TRG_AUDIT_EMP

AFTER INSERT OR UPDATE OR DELETE
ON emp
FOR EACH ROW

DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

IF INSERTING THEN

INSERT INTO audit_emp

VALUES
(
 :[Link],
USER,
SYSDATE,
'I'
);

ELSIF UPDATING THEN

INSERT INTO audit_emp

VALUES
(
:[Link],
USER,
SYSDATE,
'U'
);

ELSIF DELETING THEN

INSERT INTO audit_emp

VALUES
(
:[Link],
USER,
SYSDATE,
'D'
);

END IF;

COMMIT;
END;
/

4. Create Complex View V3 using EMP and DEPT tables.

CREATE OR REPLACE VIEW V3

AS
SELECT
[Link],
[Link],
[Link],
 [Link],
[Link],
[Link],
[Link]
FROM emp e, dept d
WHERE [Link] = [Link];

5. Create INSTEAD OF Trigger on V3 for INSERT OR UPDATE.

CREATE OR REPLACE TRIGGER TRG_V3

INSTEAD OF INSERT OR UPDATE
ON V3
FOR EACH ROW

DECLARE
v_cnt NUMBER;

BEGIN

SELECT COUNT(*)
INTO v_cnt
FROM dept
WHERE deptno = :[Link];

IF v_cnt = 0 THEN

INSERT INTO dept

(
deptno,
dname,
loc
)
VALUES
(
:[Link],
:[Link],
:[Link]
);

ELSE

UPDATE dept
 SET dname = :[Link],
loc = :[Link]
WHERE deptno = :[Link];

END IF;

SELECT COUNT(*)
INTO v_cnt
FROM emp
WHERE empno = :[Link];

IF v_cnt = 0 THEN

INSERT INTO emp

(
empno,
ename,
job,
sal,
deptno
)
VALUES
(
:[Link],
:[Link],
:[Link],
:[Link],
:[Link]
);

ELSE

UPDATE emp
SET ename = :[Link],
sal = :[Link],
deptno = :[Link]
WHERE empno = :[Link];

END IF;

END;
/