In the previous tutorial we covered all the necessary theories about PL/SQL packages in Oracle Database. There we learnt what are packages, their architecture and the syntax of creating the same. Now that we have seen all the theories required to answer the questions in your certification exam and interview, it’s time to take a step ahead and do an example demonstrating the process of creating a PL/SQL package in Oracle Database.
How to Create Package?
For the demonstration I am going to create a very simple package which will consist of two elements – a function and a stored procedure. I have tried to keep this example as simple as possible in order to keep the concept easy to understand.
As we talked while discussing the architecture of the package in the previous tutorial that the package is divided into two parts, which are
- Package header and
- The package body
So we will start with creating the package header first
CREATE OR REPLACE PACKAGE pkg_RebellionRider IS FUNCTION prnt_strng RETURN VARCHAR2; PROCEDURE proc_superhero(f_name VARCHAR2, l_name VARCHAR2); END pkg_RebellionRider;
By taking a look at the above code we can clearly say that the package is going to hold two package elements which are – a PL/SQL function prnt_strng and a stored procedure proc_superhero.
--Package Body CREATE OR REPLACE PACKAGE BODY pkg_RebellionRider IS --Function Implimentation FUNCTION prnt_strng RETURN VARCHAR2 IS BEGIN RETURN 'RebellionRider.com'; END prnt_strng; --Procedure Implimentation PROCEDURE proc_superhero(f_name VARCHAR2, l_name VARCHAR2) IS BEGIN INSERT INTO new_superheroes (f_name, l_name) VALUES(f_name, l_name); END; END pkg_rrdr;
In the above code for the package body we implemented both the package elements which we defined into the package header.
Both package header and body are individual database objects thus you have to compile them separately in order to put your package to work.
How to access the package elements?
We have our package header and body created and compiled successfully, what’s next? Like every other database object, package serves a unique purpose. Using a package you can group different database objects under one name and as PL/SQL packages are named database blocks thus they can be stored into the database and can be used later when needed.
So the question arises here is how to access the package elements? To access the elements defined and implemented into a package we use dot (.) notation. According to which in order to access the package element you have to first write the name of your package followed by dot (.) operator and then the name of the package element.
For example say you want to call the PL/SQL function prnt_strng of our package pkg_RebellionRider.
--Package Calling Function BEGIN DBMS_OUTPUT.PUT_LINE (PKG_RebellionRider.PRNT_STRNG); END;
In the above code using anonymous block we call the function prnt_strng of the package pkg_RebellionRider.
You can watch the video tutorial on the same topic to see how to call the procedure proc_superhero of our package. Also, in the same video I have explained all the above codes in detail. I hope you will enjoy watching that video. If so then do make sure to Share & Like.
You can also share this blog with your friends or colleagues.
Thanks & have a great day.