A package is a schema object that can store variables, functions, procedures, constants, and other objects. The purpose of the package is to improve the modularity, performance, and security of data.
The main 4 benefits of using packages
- Encapsulation / modularity
- packages allow you encapsulate dependent types or subprograms together to simplify the use
- Hiding the logic / information / data / database structure
- you can easily hide any business logic inside the subprograms
- you can as well protect data leakage or unauthorized manipulation
- it is database-structure-change-proof (as any other stored block); meaning, an application does not care about a database structure and whether some table name was changed. This a separation of the application and database layer.
- Overloading
- you can define the same procedures but with different parameters (or data types) which will allow you to use the same “process” but in multiple ways
- Performance
- during the first run, it is loaded in the memory and therefore no I/O is done with every other use
Every package consists of two parts
- Specification
- Body
Specification
The package specification is a mandatory standalone part. In the specification, you declare public procedures, cursors, variables, constants, etc. All that is declared here is and will be publicly accessible from outside of the package. When the package is executed, an instance is created and all components will exist during the instance duration until the end of the session. This part (package specification) interacts with an application.
Body
The package body is a non-mandatory part and contains the definition of public procedures, cursors, etc … defined in the specification. On top of that, it contains private procedures, subprograms, variables and so on, that cannot be called from outside of the package. This is a typical “black box” and it is the place where you can implement your security and information “hiding” logic.
Code sample
Package specification
CREATE OR REPLACE PACKAGE my_schema.pckg_my_sample_package AS
/*
%author OracleWorld
%purpose Demonstrate the features of packages
%desc ........
%created 2018-10-28
*/
-- global variables
g_output_only NUMBER(1) := 0;
g_debug NUMBER(1) := 0;
-------------------------------------------------------------------------------
-- SET_OUTPUT_ONLY - set package global variable
-------------------------------------------------------------------------------
PROCEDURE set_output_only(p_output_only IN NUMBER);
PROCEDURE set_debug(p_debug IN NUMBER);
/***
* Constants
***/
k#ddl_type_run VARCHAR2(15) := 'SOFT';
k#ddl_object_type VARCHAR2(15) := 'USER';
-- collections definiton
TYPE t_data_tab_type IS TABLE OF VARCHAR2(35);
data_tab t_data_tab_type;
------------------------------------------------
/**
* main procedure
***/
PROCEDURE run_all(p_day_run date);
------------------------------------------------
------------------------------------------------
-- manual run only!
PROCEDURE single_run(p_value varchar2);
------------------------------------------------
END;
As you can see above, there are several types and definitions inside the specification. Everything that is defined here is publicly accessible. Let’s have a look at a body of such a package.
Package body
CREATE OR REPLACE PACKAGE BODY my_schema.pckg_my_sample_package AS
/****************************************************************************************************************************
NAME: PCKG_MY_SAMPLE_PACKAGE
PURPOSE: Demonstrate the features of packages
REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ---------------------------------------------------------------------------------
1.0 2018-10-28 OracleWorld 1. Created this package.
****************************************************************************************************************************/
-- local variables
v_day_to_check date;
v_step_start date;
v_step_desc varchar2(100);
v_error_note varchar2(1000);
v_run_name varchar2(100) := 'SampleRun_TEST';
/***-----------------------------------------------------------------
* SET_OUTPUT_ONLY - set package global variable
***/
PROCEDURE set_output_only(p_output_only IN NUMBER)
IS
BEGIN
g_output_only := p_output_only;
END set_output_only;
FUNCTION get_output_only RETURN NUMBER
IS
BEGIN
RETURN g_output_only;
END get_output_only;
--------------------------------------------------------------------
/***-----------------------------------------------------------------
* DEBUG
***/
PROCEDURE set_debug(p_debug IN NUMBER)
IS
BEGIN
g_debug := p_debug;
END set_debug;
FUNCTION get_debug RETURN NUMBER
IS
BEGIN
RETURN g_debug;
END get_debug;
--------------------------------------------------------------------
/***
* PRIVATE PROCEDURE
***/
PROCEDURE p_drop_first (
v_error_code OUT varchar2
)
IS
BEGIN
-- debug only
IF g_debug = 1 THEN dbms_output.put_line('Getting data ...'); END IF;
-- get all proxy accesses assigned to regular users (not application accounts -> based on profile selection)
cmd := q'[SELECT *
FROM sample_table]';
-- debug only
IF g_debug = 1 THEN dbms_output.put_line('Check if there is ... '); END IF;
IF .....
THEN
RETURN;
END IF;
-- debug only
IF g_debug = 1 THEN dbms_output.put_line('Some found .. proceeding'); END IF;
FOR i IN -- some range
LOOP
-- do something here
END LOOP;
v_sql_rowcount := sql%rowcount;
commit;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
v_error_code := sqlcode ||';'|| sqlerrm;
v_error_note := 'Unexpected error';
END p_drop_first;
/***
* PRIVATE PROCEDURE
***/
PROCEDURE p_process_data(p_user_name VARCHAR2) IS
BEGIN
-- do something here
END p_process_data;
/****
*
* main RUN CONTROL procedure
*
***/
PROCEDURE run_all (p_day_run date)
IS
v_error_code varchar2(1000);
v_ex_custom EXCEPTION;
v_ex_already_loaded EXCEPTION;
BEGIN
-- check output setting
IF get_output_only() = 1
THEN dbms_output.put_line('Debug and output ON');
ELSE dbms_output.put_line('Debug and output OFF');
END IF;
v_step_start := sysdate;
v_day_to_check := p_day_run;
p_drop_first (v_error_code);
IF v_error_code IS NOT NULL THEN RAISE v_ex_custom; END IF;
-- for ALL data in the ??? table
FOR dat_block IN (SELECT DISTINCT column_name as my_col
FROM my_table t
WHERE t.col_data IN (SELECT another_data FROM another_table)
)
LOOP
IF dat_block.my_col != 'DBA_DATA'
THEN
-- check output setting
IF g_debug = 1
THEN
dbms_output.put_line(' ');
dbms_output.put_line('----------------------------------------------');
dbms_output.put_line('Data: '||dat_block.my_col);
END IF;
-- start processing
p_process_data(dat_block.my_col);
END IF;
END LOOP;
EXCEPTION
WHEN v_ex_custom THEN
v_error_note := 'ERROR - Custom exception';
dbms_output.put_line(v_error_note);
WHEN v_ex_already_loaded THEN
v_error_note := 'ERROR - Date already loaded - aborting script';
dbms_output.put_line(v_error_note);
WHEN OTHERS THEN
ROLLBACK;
v_error_code := sqlcode ||';'|| sqlerrm;
v_error_note := 'Unexpected error';
dbms_output.put_line(v_error_code||' >> '||v_error_note);
-- finish
END run_all;
/****
*
* MANUAL ONLY !!!
* check single value only
*
****/
PROCEDURE single_run (p_value varchar2)
IS
v_error_code varchar2(1000);
v_ex_no_user EXCEPTION;
v_ex_custom EXCEPTION;
v_ex_already_loaded EXCEPTION;
BEGIN
-- check output setting
IF g_debug = 1
THEN dbms_output.put_line('Debug and output ON');
ELSE dbms_output.put_line('Debug and output OFF');
END IF;
IF nvl(p_value, '') = '' THEN
RAISE v_ex_no_value;
END IF;
v_step_start := sysdate;
v_day_to_check := v_step_start;
p_drop_first (v_error_code);
IF v_error_code IS NOT NULL THEN RAISE v_ex_custom; END IF;
-- for ALL data in the ??? table
FOR dat_block IN (SELECT DISTINCT column_name as my_col
FROM my_table t
WHERE 1=1
AND t.data_val = p_value -- run for a particular value only
AND t.col_data IN (SELECT another_data FROM another_table)
)
LOOP
IF dat_block.my_col != 'DBA_DATA'
THEN
-- check output setting
IF g_debug = 1
THEN
dbms_output.put_line(' ');
dbms_output.put_line('----------------------------------------------');
dbms_output.put_line('Data: '||dat_block.my_col);
END IF;
-- start processing
p_process_data(dat_block.my_col);
END IF;
END LOOP;
EXCEPTION
WHEN v_ex_custom THEN
v_error_note := 'ERROR - Custom exception';
dbms_output.put_line(v_error_note);
WHEN v_ex_no_value THEN
v_error_note := 'ERROR - No value provided';
dbms_output.put_line(v_error_note);
WHEN v_ex_already_loaded THEN
v_error_note := 'ERROR - Date already loaded - aborting script';
dbms_output.put_line(v_error_note);
WHEN OTHERS THEN
ROLLBACK;
v_error_code := sqlcode ||';'|| sqlerrm;
v_error_note := 'Unexpected error';
dbms_output.put_line(v_error_code||' >> '||v_error_note);
-- finish
END single_run;
END;
Here you can see how some procedures are hidden inside the package; they are private. You can also overload them by changing the input parameters or their data types.
Overloading
CREATE PACKAGE pckg_load_emps AS
PROCEDURE p_store_employee (id_employee NUMBER, name_full VARCHAR2, salary NUMBER);
PROCEDURE p_store_employee (id_employee VARCHAR2, name_full VARCHAR2, salary INTEGER);
PROCEDURE p_store_employee (id_employee INTEGER, name_full VARCHAR2);
END pckg_load_emps;
CREATE PACKAGE BODY pckg_load_emps AS
PROCEDURE p_store_employee (id_employee NUMBER, name_full VARCHAR2, salary NUMBER) IS
BEGIN
INSERT INTO employee
VALUES (id_employee, name_full, trunc(salary));
END p_store_employee;
PROCEDURE p_store_employee (id_employee VARCHAR2, name_full VARCHAR2, salary INTEGER) IS
BEGIN
INSERT INTO employee
VALUES (regexp_replace(id_employee, '[^[:digit:]]', ''), name_full, salary);
END p_store_employee;
PROCEDURE p_store_employee (id_employee INTEGER, name_full VARCHAR2) IS
BEGIN
INSERT INTO employee
VALUES (id_employee, name_full, 0);
END p_store_employee;
END pckg_load_emps;
Now, when you have your package created, you can easily call it as follows:
BEGIN
my_schema.pckg_my_sample_package.set_output_only(1);
my_schema.pckg_my_sample_package.set_debug(1);
my_schema.pckg_my_sample_package.single_run('Sample');
END;