This article contains information about Oracle PL/SQL Packages, create , execute and drop packages and also predefined packages.
What is Package in Oracle PL/SQL?
Packages are a structure in which structures such as procedures and functions are gathered together.
Oracle packages definition consists of two parts: definition and body.
Create PACKAGE in Oracle PL/SQL
The keywords to be used when creating the package are as follows.
CREATE [OR REPLACE] PACKAGE <PACKAGE_NAME> IS | AS
An example of creating a package with Oracle PL / SQL is as follows.
CREATE OR REPLACE PACKAGE SAMPLE_PACKAGE
PROCEDURE MyLOOP(Start IN PLS_INTEGER, Finish IN PLS_INTEGER);
FUNCTION CONVERT_FIRST_LETTER_TO_UPPERCASE (MYTEXT VARCHAR2) RETURN VARCHAR2;
After making the necessary definitions for the package, the content of the structures such as procedure and function is written in the BODY section.
CREATE OR REPLACE PACKAGE BODY SAMPLE_PACKAGE
PROCEDURE MyLOOP (
START IN PLS_INTEGER,
FINISH IN PLS_INTEGER
FOR v_number IN START .. FINISH LOOP
DBMS_OUTPUT.put_line('Command executed ' ||v_number || ' times.');
FUNCTION CONVERT_FIRST_LETTER_TO_UPPERCASE (MYTEXT IN VARCHAR2)
RETURN UPPER(SUBSTR(MYTEXT, 1, 1)) || LOWER(SUBSTR(MYTEXT, 2));
Execute PACKAGE in Oracle PL/SQL
We can access the package contents as follows.
For example, let’s run the MyLOOP procedure above.
SET SERVEROUTPUT ON;
EXECUTE SAMPLE_PACKAGE.MyLOOP(1, 10);
Similarly, other objects in the package can be accessed.
The user_objects table is used to get information about the created packages.
SELECT * FROM user_objects WHERE object_type = 'PACKAGE';
The DESC keyword is used to get information about a package.
DROP PACKAGE in Oracle PL/SQL
The DROP keyword is used to delete the package.
DROP PACKAGE PACKAGE_NAME;
Predefined Oracle PL/SQL PACKAGES
There are packages created for various processes within Oracle.
DBMS_OUTPUT in Oracle
This is the package used to display the data on the screen. You can find detailed information in the below link.
DBMS_PIPE in Oracle
This is the package used to process between sessions. You can find detailed information in the below link.
DBMS_LOCK in Oracle
This is the package in which user lock operations are performed. You can find detailed information in the below link.
DBMS_STANDART in Oracle
This is the standard package used in PL/SQL commands.
For example; The RAISE_APPLICATION_ERROR function is included in the DBMS_STANDART package.
The reason for using the packages, as can be understood from the examples, is the collection of commands that do interrelated work.
The use of packages for commands where the same or interrelated operations are performed will be useful for regular development.
You can find more detailed information about below topics in the below link.
You will find below topics in this article.
- What is PL/SQL
- Oracle PL/SQL Data Types and Variables and Literals
- Oracle PL/SQL Operators
- Oracle PL/SQL Conditional Statements
- Oracle PL/SQL Loops
- Oracle PL/SQL Procedures and Procedure Parameters
- Oracle PL/SQL Functions
- Oracle PL/SQL Cursor
- Oracle PL/SQL Records
- Oracle PL/SQL Exception
- Oracle PL/SQL Trigger
- Oracle PL/SQL Packages
- Oracle PL/SQL Collections
You can find more information about packages at docs.oracle.com