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);
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.
CREATE OR REPLACE PACKAGE BODY pkg_RebellionRider IS
FUNCTION prnt_strng RETURN VARCHAR2 IS
PROCEDURE proc_superhero(f_name VARCHAR2, l_name VARCHAR2) IS
INSERT INTO new_superheroes (f_name, l_name) VALUES(f_name, l_name);
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
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. You can also click here to quickly share this post on your twitter. Thanks & have a great day.
SQL Script and Presentation used
You can DOWNLOAD SQL script and presentation used in the Video and in this article.
NO SQL Script used in Video and in this article
Do Not Drink & Drive Use My Uber Code "UberRebellionRider"
SignUp using my referral code and get first ride worth $20 FREE