Oracle Package Features
A Package is a PL/SQL construct that allows related objects to be stored together
A Package is a stored database object
Groups together PROCEDURES, FUNCTIONS, CURSORS, types and variables
Other PL/SQL blocks can reference packages
Package Creation
Package has two components:
Package Specification
Package body
Package Specification:
Declares types, variables, constants, exceptions, cursors and subprograms available for use Constructs declared in the package specifications are publicPackage Specification Syntax:
CREATE OR REPLACE PACKAGE package_name IS
procedure_specification
function_specification
variable_declaration
cursor_declaration etc.
END package_name;
Package Body:
It defines cursors and subprograms and so implements the specification
It can not be successfully compiled unless the package specification has already been successfully compiled
Package Body Syntax:
CREATE OR REPLACE PACKAGE BODY package_name IS
private_variable
procedure_definition
function_definition etc.
private function or procedure definition
END package_name;
Access Rules for Package Members
Data members and methods that are declared within the package specification become public members of the package
Data members and methods that are not declared within the package specification, but are implemented in the package body, become private to the package
Public package members can be accessed by the methods belonging to the package as well as by methods outside the package
Private package members can be accessed only by the methods defined in the package itself
Data members of a package (variables, constants, cursors etc.), both public and private, act like global data members and persist within a single Oracle session or connection.
When a packaged subprogram opens a cursor, that cursor remains open and is available to other packaged methods throughout the session. You do not have to explicitly define the cursor in each method. You can open it in one module and fetch it in another module.
Oracle Packages – Advantages
Advantages of Packages
Modularity: encapsulates logically related programming structures in a named module
Easier Application Design: initially only the interface information in the package specification is required
Information hiding: constructs can be public or private
Better Performance: when a packaged subprogram is called the first time, the entire package is loaded into memory
Overloading: packages allow you to overload procedures and functions
Packages: Example
Creating a Package Specification
CREATE OR REPLACE PACKAGE emp_pack
IS
--Two procedures added to the specification become global objects
PROCEDURE emp_add (
p_empno IN emp.empno%TYPE,
p_ename IN emp.ename%TYPE,
p_esal IN emp.sal%TYPE,
p_deptno IN emp.deptno%TYPE
);
PROCEDURE emp_del (p_empno IN emp.empno%TYPE);
END emp_pack;
Creating a Package Body
CREATE OR REPLACE PACKAGE BODY emp_pack
IS
-- Private function with deptno as parameter to check the existence of deptno value in dept table
FUNCTION valid_deptno (p_deptno dept.deptno%TYPE)
RETURN BOOLEAN
IS
dummy NUMBER (2);
BEGIN
SELECT deptno
INTO dummy
FROM dept
WHERE deptno = p_deptno;
RETURN TRUE;
--Exception Handling if the deptno value is not found
EXCEPTION
WHEN NO_DATA_FOUND
THEN
RETURN FALSE;
END valid_deptno; -- End of the private function
--Implementing the procedure defined in the specification
PROCEDURE emp_add (
p_empno IN emp.empno%TYPE,
p_ename IN emp.ename%TYPE,
p_esal IN emp.sal%TYPE,
p_deptno IN emp.deptno%TYPE
)
IS
BEGIN
--Function valid_deptno is called by procedure emp_add
IF valid_deptno (p_deptno)
THEN
INSERT INTO emp
(empno, ename, sal, deptno
)
VALUES (p_empno, p_ename, p_esal, p_deptno
);
DBMS_OUTPUT.put_line
( 'Emp# : '
|| p_empno
|| ' added to table'
);
COMMIT;
ELSE
raise_application_error
(-20010,
'Invalid
department…Try again'
);
END IF;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line(SQLERRM);
END emp_add; -- End of the public procedure
--Implementing the procedure defined in the specification
PROCEDURE emp_del (p_empno IN emp.empno%TYPE)
IS
BEGIN
DELETE FROM emp
WHERE empno = p_empno;
IF SQL%NOTFOUND
THEN
raise_application_error (-20120, 'Employee does not exist');
ELSE
DBMS_OUTPUT.put_line(SQL%ROWCOUNT || ' ROWS DELETED');
END IF;
END emp_del; -- End of the public procedure
ENDemp_pack; -- End of Package
Calling a packaged procedure or function from SQL*Plus
EXECUTE emp_pack.emp_del(1001)
Concept of Global Variable in a Package
PL/SQL packages offer the ability to implement global data in your application environment.
If you have access to the package, you can modify package variables in one method and then reference those changed variables in another method. The values of packaged data members persist for the duration of a user session (connection to the database).
Package variables can carry data across the boundaries of transactions, since they are tied to the session itself and not to a transaction.
Oracle Packages: Use of Global Variable
Package Specification
The variable v_sal is a packaged public variable that can act as a global variable for the package members as well as outside methods, that have right to execute the package
CREATE OR REPLACE PACKAGE increment_pack
IS
-- Global Variable declared inside the specification
v_sal emp.sal%TYPE;
PROCEDURE show_increment (p_empno emp.empno%TYPE);
END increment_pack;
Package Body
CREATE OR REPLACE PACKAGE BODY increment_pack
IS
--Private procedure
PROCEDURE emp_sal (v_eno IN emp.empno%TYPE)
IS
BEGIN
SELECT sal
INTO v_sal
FROM emp
WHERE empno = v_eno;
END emp_sal;
-- End of the private procedure within the package
PROCEDURE show_increment (p_empno emp.empno%TYPE)
IS
BEGIN
emp_sal (p_empno); -- Call to the private procedure
-- Value of the variable v_sal is set by the procedure emp_sal and is used here
IF v_sal > 2000
THEN
DBMS_OUTPUT.put_line('no salary increase');
ELSE
UPDATE emp
SET sal = sal + sal * .1
WHERE empno = p_empno;
DBMS_OUTPUT.put_line('salary increased');
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line('Wrong employee number');
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line(SQLERRM);
END show_increment;
END increment_pack;
-- Call 1
BEGIN
increment_pack.show_increment(5678);
END;
/-- Wrong employee number
-- call 2
BEGIN
increment_pack.show_increment(7369);
END;
/
-- ORA-06502: PL/SQL: numeric or value error: number precision too large
-- call 3
BEGIN
increment_pack.show_increment (7521);
END;
-- salary increased
Oracle Packages: Forward Declarations
Identifiers must be declared before referencing them
CREATE OR REPLACE PACKAGE BODY forward_pack
IS
PROCEDURE award_bonus (. . .)
IS
BEGIN
calc_rating(. . .);--illegal reference
END;
FUNCTION calc_rating ( . . . )
RETURN NUMBER
IS
BEGIN
RETURN …;
END;
END forward_pack;
CREATE OR REPLACE PACKAGE BODY forward_pack
IS
-- Forward declaration
FUNCTION calc_rating(. . .) RETURN NUMBER;
PROCEDURE award_bonus (. . .)
IS
BEGIN
calc_rating(. . .);-- Legal reference
END;
FUNCTION calc_rating ( . . . )
RETURN NUMBER
IS
BEGIN
RETURN …;
END;
END forward_pack;
Oracle Packages – Overloading
Allows you to use the same name for different subprograms inside a package
Requires formal parameters of the subprogram to differ in number, order or data type family
For functions if only the return type is different but parameters are same then overloading is not permitted
CREATE OR REPLACE PACKAGE PACK_MARKETING AS
PROCEDURE MKT_PROJECTION;
PROCEDURE MKT_PROJECTION(MK_ID NUMBER);
PROCEDURE MKT_PROJECTION (MK_NAME VARCHAR2);
PROCEDURE MKT_PROJECTION(MK_ID NUMBER,MK_NAME
VARCHAR2);
PROCEDURE MKT_PROJECTION(MK_NAME VARCHAR2,
MK_ID NUMBER);
END PACK_MARKETING;
Overloading: Example
CREATE OR REPLACE PACKAGE overloadpack
AS
PROCEDURE increasesal (eno emp.empno%TYPE);
PROCEDURE increasesal (ename emp.ename%TYPE);
ENDoverloadpack;
/
CREATE OR REPLACE PACKAGE BODY overloadpack
AS
PROCEDURE increasesal (eno emp.empno%TYPE)
AS
BEGIN
UPDATE emp
SET sal = sal + 1000
WHERE empno = eno;
IF SQL%NOTFOUND
THEN
DBMS_OUTPUT.put_line('Wrong employee number');
END IF;
END increasesal;
PROCEDURE increasesal (ename emp.ename%TYPE)
AS
cnt NUMBER;
CURSOR empcur
IS
SELECT empno
FROM emp
WHERE ename = increasesal.ename;
BEGIN
SELECT COUNT (empno)
INTO cnt
FROM emp
WHERE ename = increasesal.ename;
IF cnt = 0
THEN
DBMS_OUTPUT.put_line('Wrong employee name');
ELSE
FOR eno IN empcur
LOOP
UPDATE emp
SET sal = sal + 1000
WHERE empno = eno.empno;
END LOOP;
END IF;
END increasesal;
END overloadpack;
Package Dropping and Recompiling
Alter package
ALTER PACKAGE package_name COMPILE;
Dropping package specification
DROP PACKAGE package_name;
Dropping package body
DROP PACKAGE BODY package_name;
Data Dictionary Views
The data dictionary views used for stand-alone procedures and functions are also available for packaged procedures and functions
USER_SOURCE Is used to obtain the text of a stored object in the users schema
USER_OBJECTS Is used to display the list of all objects in the users schema
USER_PROCEDURESIs used to display the list of all procedures and functions in that users schema
You May Like:
PLSQL Record
Hello There,
ReplyDeleteOracle PL/SQL Packages being contrived to exist for many projects simply so it can be run will be the first to hit the wall, but those projects where the functions to make existing transactions cheaper in real world applications will find the elusive real world demand.
It was cool to see your article pop up in my google search for the process yesterday. Great Guide.
Keep up the good work!
Thank you,
Vikas
Hello Mate,
ReplyDeleteIn debt to you for making my learning on the Oracle PL/SQL Packages area so hassle-free! I lay my faith on your writings.
I am facing the below issue(Screen shot) with CREATE_JOB procedure . The issue is with CREDENTIAL_NAME attribute.
More information on this:
I tried to create the job for one of the predefined chain.
why I am using CREDENTIAL_NAME attribute is, in one of the chain step I am calling LINUX script.
I read multiple articles and watched many videos about how to use this tool - and was still confused! Your instructions were easy to understand and made the process simple.
Many Thanks,
Radhey
SEO or search engine optimization is a hot career option. Careers in the SEO assure lucrative salary packages along with decent career progression. Moreover, there is no paucity of good SEO jobs both in India and abroad. https://sites.google.com/view/seoservicesindelhiindia
ReplyDelete