1. You can groups logical related subprogram (procedures and functions)
2. It consist of two parts
I) specification
II) Body
3. It allows the oracle server to read multiple object in to a memory once
4. You can declare global variable,cursor, user define exeption
5. Overloading
6. we can't create anonyms block inside the package
create table err_log(sno NUMBER, u_name VARCHAR2(30), error_msg CLOB, hap_tm TIMESTAMP);
select * from err_log;
--stand alone procedure
CREATE OR REPLACE PROCEDURE emp_sal_sp(p_employee_id IN employees.employee_id%TYPE) IS
v_salary employees.salary%TYPE;
v_first_name employees.first_name%TYPE;
v_error VARCHAR2(1000);
BEGIN
SELECT salary,
first_name
INTO v_salary,
v_first_name
FROM employees
WHERE employee_id = p_employee_id;
dbms_output.put_line('Salary of ' || v_first_name || ' is ' || v_salary);
EXCEPTION
WHEN OTHERS THEN
v_error := 'Error while fetching salary. Error : ' || SQLERRM;
INSERT INTO err_log
VALUES
(1,
USER,
v_error,
systimestamp);
commit;
END emp_sal_sp;
BEGIN
-- Call the procedure
emp_sal_sp(p_employee_id => :p_employee_id);
END;
CREATE OR REPLACE PROCEDURE emp_hdt_sp(p_employee_id IN employees.employee_id%TYPE) IS
v_hire_date DATE;
v_first_name employees.first_name%TYPE;
v_error VARCHAR2(1000);
BEGIN
SELECT hire_date,
first_name
INTO v_hire_date,
v_first_name
FROM employees
WHERE employee_id = p_employee_id;
dbms_output.put_line(v_first_name || ' hired on ' || to_char(v_hire_date,'month ddth, yyyy'));
EXCEPTION
WHEN OTHERS THEN
v_error := 'Error while fetching salary. Error : ' || SQLERRM;
INSERT INTO err_log
VALUES
(1,
USER,
v_error,
systimestamp);
commit;
END emp_hdt_sp;
BEGIN
-- Call the procedure
emp_hdt_sp(p_employee_id => :p_employee_id);
END;
create or replace function sum_fn (p_a IN NUMBER, p_b IN NUMBER)
RETURN NUMBER
IS
v_c NUMBER;
BEGIN
v_c := p_a + p_b;
RETURN v_c;
END;
/
--Specification Part
CREATE OR REPLACE PACKAGE emp_pkg
IS
PROCEDURE emp_sal_sp(p_employee_id IN employees.employee_id%TYPE);
PROCEDURE emp_hdt_sp(p_employee_id IN employees.employee_id%TYPE);
FUNCTION sum_fn (p_a IN NUMBER, p_b IN NUMBER)
RETURN NUMBER;
END emp_pkg ;
--Body Part
CREATE OR REPLACE PACKAGE BODY emp_pkg
IS
PROCEDURE emp_sal_sp(p_employee_id IN employees.employee_id%TYPE) IS
v_salary employees.salary%TYPE;
v_first_name employees.first_name%TYPE;
v_error VARCHAR2(1000);
BEGIN
SELECT salary,
first_name
INTO v_salary,
v_first_name
FROM employees
WHERE employee_id = p_employee_id;
dbms_output.put_line('Salary of ' || v_first_name || ' is ' || v_salary);
EXCEPTION
WHEN OTHERS THEN
v_error := 'Error while fetching salary. Error : ' || SQLERRM;
INSERT INTO err_log
VALUES
(1,
USER,
v_error,
systimestamp);
commit;
END emp_sal_sp;
PROCEDURE emp_hdt_sp(p_employee_id IN employees.employee_id%TYPE) IS
v_hire_date DATE;
v_first_name employees.first_name%TYPE;
v_error VARCHAR2(1000);
BEGIN
SELECT hire_date,
first_name
INTO v_hire_date,
v_first_name
FROM employees
WHERE employee_id = p_employee_id;
dbms_output.put_line(v_first_name || ' hired on ' || to_char(v_hire_date,'month ddth, yyyy'));
EXCEPTION
WHEN OTHERS THEN
v_error := 'Error while fetching salary. Error : ' || SQLERRM;
INSERT INTO err_log
VALUES
(1,
USER,
v_error,
systimestamp);
commit;
END emp_hdt_sp;
FUNCTION sum_fn (p_a IN NUMBER, p_b IN NUMBER)
RETURN NUMBER
IS
v_c NUMBER;
BEGIN
v_c := p_a + p_b;
RETURN v_c;
END;
END emp_pkg;
/
A package specification can exist without a package body, but
a package body can't exist without a package specification.
--Executing procedure inside the package
BEGIN
emp_pkg.emp_sal_sp(120);
END;
--Executing function inside the package
SELECT emp_pkg.sum_fn(23,567) FROM dual;
-- You can declare global variable,cursor, user define exeption
create or replace package all_detail
as
PROCEDURE emp2sal (a IN NUMBER);
PROCEDURE emp2exep (a IN NUMBER);
FUNCTION add2num (a IN NUMBER, b IN NUMBER)
RETURN NUMBER;
c NUMBER(8); --global declaration
abort_ex EXCEPTION; --global exception declaration
CURSOR emp_rec --global cursor declaration
IS
SELECT first_name, salary, hire_date, department_id
FROM employees;
End all_detail;
create or replace package body all_detail
as
PROCEDURE emp2sal (a IN NUMBER)
AS
BEGIN
SELECT salary
INTO c
FROM Employees
WHERE Employee_id = a;
Dbms_output.put_line('Salary of Employee ' || a || ' is ' || c);
EXCEPTION
WHEN no_data_found THEN
Dbms_output.put_line('Please enter valid id');
END emp2sal;
PROCEDURE emp2exep (a IN NUMBER)
AS
BEGIN
SELECT Round(Months_between(sysdate,hire_date)/12)
INTO c
FROM Employees
WHERE Employee_id = a;
Dbms_output.put_line( c || ' Years');
EXCEPTION
WHEN no_data_found THEN
Dbms_output.put_line('Please enter valid id');
END emp2exep;
FUNCTION add2num (a IN NUMBER, b IN NUMBER)
RETURN NUMBER
AS
BEGIN
c := a+b;
RETURN C;
END;
End all_detail;
/*Declaring a Bodiless Package */
CREATE OR REPLACE PACKAGE global_constant
IS
mile_2_kilo CONSTANT NUMBER := 1.6093;
kilo_2_mile CONSTANT NUMBER := 0.6214;
yard_2_meter CONSTANT NUMBER := 0.9144;
meter_2_yard CONSTANT NUMBER := 1.0936;
END global_constant;
BEGIN
DBMS_OUTPUT.PUT_LINE('20 miles = ' || 20*global_constant.mile_2_kilo||' km');
END;
/*Forward Declaration in package */
DECLARE
PROCEDURE P2; -- forward declaration
PROCEDURE P3;
PROCEDURE P1 IS
BEGIN
dbms_output.put_line('From procedure p1');
p2;
END P1;
PROCEDURE P2 IS
BEGIN
dbms_output.put_line('From procedure p2');
p3;
END P2;
PROCEDURE P3 IS
BEGIN
dbms_output.put_line('From procedure p3');
END P3;
BEGIN
p1;
END;
sample output:
From procedure p1
From procedure p2
From procedure p3
Drop package package_name;
Drop package body package_name;
SELECT text FROM user_source u
WHERE u.name = 'EMP_PKG';
Interview Questions:
What is package?
Advantage of package
Is it possible to create package body with out package specification?
what is package overloading?
what is forward declaration in package?
which data dictionary table contain source code of package?
How to declare global variable, exception and cursor?
How to execute procedure and function inside the package?
