PL/SQL Developer - How to Create First Sample Package - Oracle 10g

Here is the Steps to create first Package in Oracle 10g using PL/SQL developer.

Step 1: Open PL/SQL developer and login to database.

Step 2: Go to tools menu of PL/SQL Developer and check the Browser option, A Browser window will appear.

 

Step 3: Now Go to Packages folder and right click and select new.

 

Step 4: Give the name of package and Purpose of package. Click ok.

 

Now packages contains two part declaration and body part. declaration contains the declaration of procedures and functions while body part contains the all logic for the function and procedures.

 

Declaration part of Package contains the Procedure and cursor declaration.

Package

Code Snippet

1. CREATE OR REPLACE PACKAGE PKG_DEMO is
2.  
3.   -- Author  : DEMO_AUTHOR
4.   -- Created : 1/04.2010 10:00:33 AM
5.   -- Purpose : DEMO
6.  
7.   TYPE refcur IS REF CURSOR;
8.   -- Public type declarations
9.   v_const_partial VARCHAR2(10) := 'Partial';
10.   v_const_full    VARCHAR2(10) := 'Full';
11.  
12.   PROCEDURE PRC_DEMO(ID IN NUMBER,
13.                      CUR_RESULT OUT REFCUR);
14.  
15. end PKG_DEMO;

Definition part of Package contains the Procedure and cursor declaration.

Package Body

Code Snippet

1. create or replace package body PKG_DEMO is
2.  
3.     PROCEDURE PRC_DEMO(ID IN NUMBER, CUR_RESULT OUT REFCUR) AS
4.  
5.     v_Sql LONG;
6.      
7.     BEGIN
8.       OPEN  CUR_RESULT FOR 'SELECT * FROM TB_NAME';
9.     END  PRC_DEMO;
10. end PKG_DEMO;