Do you know the easiest way to create a package in Oracle?

There are two steps in creating the package. I am using Toad as an Oracle Developer Software for accessing the database.

Database

After the connection is established, we will see this screen where we need to code for creating a package.

Database

First, we need to create a specification for the package.

  1. CREATEORREPLACEPACKAGE SCHEMA1.PACKAGE1 AS
  2. PROCEDUREGET_PROCEDURE1 (PVARIABLE1 VARCHAR2,C_OUTPUT OUTSYS_REFCURSOR );
  3. PROCEDUREGET_PROCEDURE2 (PVARIABLE1 VARCHAR2,PVARIABLE2 VARCHAR2 C_OUTPUT OUTSYS_REFCURSOR );
  4. END; /

Database

In this specification, we declare a procedure prototype. And in the body, we define the functionality of the package name and procedure. Compiling specification is in Toad.

Then, we need to create the package body.
  1. CREATEORREPLACEPACKAGE BODY SCHEMA1.PACKAGE1 AS
  2. PROCEDUREGET_PROCEDURE1 (PVARIABLE1 VARCHAR2, C_OUTPUT OUTSYS_REFCURSOR )
  3. IS
  4. BEGIN
  5. OPENC_OUTPUT FOR
  6. SELECT* FROMTABLE1 WHEREVARIABLE1=PVARIABLE1 ;
  7. END;
  8. PROCEDUREGET_PROCEDURE2 (PVARIABLE1 VARCHAR2,PVARIABLE2 VARCHAR2 C_OUTPUT OUTSYS_REFCURSOR )
  9. IS
  10. BEGIN
  11. OPENC_OUTPUT FOR
  12. SELECT* FROMTABLE2 WHEREVARIABLE1=PVARIABLE1 ANDVARIABLE1 =PVARIABLE2 ;
  13. END;
  14. END; /

Database

In the package body, we will define the procedure.

Hence, the package is ready to use after compilation.