Package

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?