pl sql tutorial - Package in pl/sql oracle database

pl/sql tutorial, PL/SQL package is a kind of program only however it contains the subprogram which actually achieves the intended objective

Oracle SQL Complete Tutorial/Training/Course for the beginner:

-----------------------------------------------------------------------------------------------------------
pl/sql Theory
-----------------------------------------------------------------------------------------------------------
PL/SQL package is a logical grouping of a related subprogram (procedure/function) into a single element.
A Package is compiled and stored as a database object that can be used later.

PL/SQL package has two components.
1) Package Specification: its has only a declaration
2) Package Body: it has a complete definition of procedure/function/cursor

Access specification:
1) private : Private element( procedure/function/variable) are element which are not accessed ouside of the package.
and those element (procedure/function/variable) not declared in specification that will be private
element
2) public: Public element(procedure/function) can be accessed outside of the package and we declare those
element in specification then it becomes public

key Note :
1) whenever specification recompiled, the package also has to recompile
2) If the cursor is defined in the package specification or in global
part of the package body, then the cursor once opened will persist till
the end of the session
3) overloading is nothing but if procedure/function with different
number of parameters, a different type of parameters, or different retype
then it will be known as overloading
4) all the element in the package will share a common keyspace

syntax of package specification:
CREATE [OR REPLACE] PACKAGE package_name
IS
sub_program and public element declaration
.
.
END package name

syntax of package body:
CREATE [OR REPLACE] PACKAGE BODY package_name
IS
Private element definition
sub_program and public element definition
.
BEGIN
Package Initialization
END package_name

Package related table :
ALL_OBJECT
USER_OBJECT
ALL_SOURCE
USER_SOURCE
ALL_PROCEDURES
USER_PROCEDURES

Example :
CREATE OR REPLACE PACKAGE employee_pkg AS
errorMess varchar2(100);
CURSOR c1 RETURN employees%ROWTYPE;
END employee_pkg;

CREATE OR REPLACE PACKAGE BODY employee_pkg AS

CURSOR c1 RETURN employees%ROWTYPE IS
SELECT * FROM employees WHERE salary = 2500;

BEGIN
SELECT salary INTO emp_sal
FROM employees
WHERE employee_id = emp_id;
END find_sal;

END employee_pkg;
/

set SERVEROUTPUT on;
begin
END;
/

advantages :
1) Organized code management.
2) Easy (top-down) application design.
3) Easy changes to the implementation.
4) Security and maintainability.
5) Session-wide persistence of variables.
6) Better performance.

follow me on:
Facebook Page:

[Sama_Landge]

Thank you Rakesh Sir....i ever seen this kind of fabulous teaching style in my life❤

[Aakhibsk]

Thank you for teaching SQL and PLSQL till here, really i could feel potential among other while handling my projects

[kishorekumar-vspu]

Very useful content with clear explanation...

[TheOracleGuy_AI]

Hi Sir, I am just curious why did you stop making videos. I have just started my YouTube journey, hoping to reach your number of subscribers some day. I am more into Oracle ERP functional videos.

[rozankhader]

I hope you make some projects for some schemas to implement these concepts

[amitbiswas]

Cursor portion is not correct, package body returns error.

[rakshithgowda]

Hi sir.. can you make videos on ETL & Snowflake?

[vadlakuntanaveen]

may be package specification is not found

[maheshramteke]

Sir, please guide me on this issue after oracle 19c installation. If I start SQLPlus with username and password then adapter issue is showing and if I go to services then I want to start oracle service, oracle is starting then error is showing that windows is local computer Could not start OracleServiceORCL. The system can not find the file specified.

[sachinpanghal]

Sir i am fresher. And i have done b.sc. but i want to get into IT sector. I have learnt pl sql from ur videos. So can i join any IT company. Plz provide some guidance what to do.. how can i join.

[mohmmed]

Hi sir please share where to practice plsql any suggestions please...
Practice questions topicwise and some projects for plsql where can we get???

[muralikrishna-msxu]

How to write query, first 3 characters should display in india and output INDia

[ratankumar-equw]

How to display second max highest salary... department wise... Explain concept sir

[challasaibharath]

Sir could you please explain how to jump from knowing MS SQL server to oracle db. How about the syntax and how much time would it take me to learn oracle db knowing ms sql server .

[ganeshshinde]

Could you please make videos Unix command and shell scripting language