Oracle – Easy Way to Create Package in a Oracle Database

Do you know what is the easy way to Create package in a oracle ?

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

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

  1. First we need to create a specification for package.

 

 CREATE OR REPLACE PACKAGE SCHEMA1.PACKAGE1 AS 
PROCEDURE GET_PROCEDURE1 (PVARIABLE1 VARCHAR2,C_OUTPUT OUT SYS_REFCURSOR ); 
PROCEDURE GET_PROCEDURE2 (PVARIABLE1 VARCHAR2,PVARIABLE2 VARCHAR2 C_OUTPUT OUT SYS_REFCURSOR );
 END; /

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

2.  Then we need to create the package body.

 

CREATE OR REPLACE PACKAGE BODY SCHEMA1.PACKAGE1 AS
 PROCEDURE GET_PROCEDURE1 (PVARIABLE1 VARCHAR2, C_OUTPUT OUT SYS_REFCURSOR )
 IS 
BEGIN 
OPEN C_OUTPUT FOR 
SELECT * FROM TABLE1 WHERE VARIABLE1=PVARIABLE1 ; END;
 PROCEDURE GET_PROCEDURE2 (PVARIABLE1 VARCHAR2,PVARIABLE2 VARCHAR2 C_OUTPUT OUT SYS_REFCURSOR )
 IS
 BEGIN 
OPEN C_OUTPUT FOR
 SELECT * FROM TABLE2 WHERE VARIABLE1=PVARIABLE1 AND VARIABLE1 =PVARIABLE2 ; 
END; 
END; /

In the package body we will define the procedure.

 

Hence , the package is ready to use after compilation.

Tags :

About the Author

Prashant Goswami

I am the blog writer who will teach how to love programming. My experience in programming is about 6 years. And i am the lover of programming , so i will make you people.

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: