Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Home
Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients

 

Free Oracle Tips


 
HTML Text AOL

Free Oracle App Server Tips


 
HTML Text

Oracle support

Oracle training

Oracle tuning

Rednecks!

Remote Oracle

Custom Oracle Training

 

   
  Oracle Tips by Burleson

Using PL/SQL Packages

A PL/SQL package is a grouping of related procedures, functions, definitions, and cursors.  PL/SQL packages  have these characteristics:

  • The code placed in a package can see and use the other code inside the package. 

  •  A cursor, variable, or type definition can be defined once and used anywhere in the package.

  • Like the ADA  programming language, PL/SQL packages have a specification and a body declaration. 

  • The code objects, definitions, procedures and functions defined in the package specification are accessible from outside the package

  • The code objects defined in the package body, but not in the specification, can not be seen or accessed outside the package.  This allows a developer to hide code that he does not want the user to see.

A PL/SQL packages  specification follows the format shown in the example below.

SQL> create or replace package myapp is
  2    procedure update_book_date
  3      (v_book_key in book.book_key%type,
  4       v_pub_date in book.book_date_published%type);
  5    function get_author
  6      (v_book_key in book.book_key%type)
                                  return varchar2;
  7  end myapp;
  8  / 

Package created.

The example specification above defines a package called myapp that contains one procedure and one function.  Notice that the procedure and function body are not included.  Only the name and passed parameters (or the signature) is included in the specification.  The specification defines what is included the package that is exposed for use outside the package.  To call a package object, include the package name using the dot (.) notation.

begin
  myapp.update_book_date('B101',SYSDATE);
  -- all the other suff

So where is the code for myapp’s objects?  The code is in the package body, which is a separate database object.  Any code object in the package specification is accessible from outside of the package.  Any additional code objects defined in the package body (but not in the specification) are hidden and are not accessible from outside the package.  Any code object in the package body can access any other code object in the package body. 

In the example below, the procedure and function specified in the previous example is defined on lines 9 through 28. 

SQL> create or replace package body myapp is
  2
  3    type number_var is varray(1000) of number;
  4
  5    cursor c1 is
  6      select order_number,quantity
  7      from sales;
  8
  9    procedure update_book_date
 10      (v_book_key in book.book_key%type,
 11       v_pub_date in
                     book.book_date_published%type)
 12    is
 13    begin
 14      update book
 15        set book_date_published = v_pub_date
 16        where book_key = v_book_key;
 17    end;
 18
 19    function get_author
 20      (v_book_key in book.book_key%type)
          return varchar2
 21    is
 22      auth_store author.author_last_name%type;
 23    begin
 24      select author_last_name into auth_store
 25      from author join book_author
           using (author_key)
 26      where book_key = v_book_key;
 27      return auth_store;
 28    end;
 29
 30  end myapp;
 31  /

Package body created.

Notice that the example defines a VARRAY type on line 3 and a cursor on line 5.  These objects can be used anywhere in the package but can not be accessed outside the package.  Since code objects defined in the package can be shared, you do not have to redefine them when used.  Here is a partial listing of the package body for the example package specification. 

The entire listing is in the code depot.

SQL> create or replace package body myapp is
  2
  3    type number_var is varray(1000) of number;
  4    type string_var is varray(1000)
                                 of varchar2(80);
  5
  6    cursor c1(v_key varchar2) is
  7      select order_number,quantity
  8      from sales where store_key = v_key;
  9
 10
 11    function get_average_sales
 12       (v_store_key in varchar2) return number
 13    is
 14      a_order string_var;
 15      a_qty   number_var;
 16      n_avg   number;
 17    begin
 18      open c1(v_store_key);
 19      fetch c1 bulk collect into a_order, a_qty;
 20      for indx in
            a_order.first..a_order.last loop
 21        n_avg := n_avg + a_qty(indx);
 22      end loop;
 23      return n_avg/a_order.count;
 24    end;
 25
procedure update_book_date
…  -- code continues
 

Two VARRAYs are defined on lines 3 and 4 and they are used in the function defined on lines 14 and 15.  The function does not have to redefine the types.  The cursor defined on line 6 is again used on line 18 without having to redefine it inside the function. 

We have been using packages throughout this book, mostly the dbms_output package supplied by Oracle.  We have made extensive use of the put_line procedure inside of that package, but that is not the only procedure in the package. 

But how do you determine the code objects that a package exposes in the package specification?  Just like a database table, you describe it:

SQL> desc dbms_output 

PROCEDURE DISABLE 

PROCEDURE ENABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 BUFFER_SIZE                    NUMBER(38)              IN     DEFAULT

PROCEDURE GET_LINE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ -------- LINE                           VARCHAR2                OUT
 STATUS                         NUMBER(38)              OUT 

PROCEDURE GET_LINES
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LINES                          TABLE OF VARCHAR2(32767) OUT
 NUMLINES                       NUMBER(38)              IN/OUT 

PROCEDURE GET_LINES
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LINES                          DBMSOUTPUT_LINESARRAY   OUT
 NUMLINES                       NUMBER(38)              IN/OUT 

PROCEDURE NEW_LINE 

PROCEDURE PUT
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 A                              VARCHAR2                IN

PROCEDURE PUT_LINE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 A                              VARCHAR2                IN

There are eight procedures in the dbms_output package.  Notice that three of the procedures have the same name.  This is called overloading and in PL/SQL can only exist in a package.  Outside of a package a procedure or function is defined by a name.  No two objects can have the same name.  But inside a package, objects are defined by their signature, which consist of the name and the passed parameters. 

If you look closely at the three get_lines procedures you will see that each one requires a different number and/or type of parameters.  Thus, the three procedure names are the same but the three signatures are distinct.  In the myapp package defined in the previous example, there is a function that returns the author’s last name when passed a book_key (the author of the book).  This same function can be used if passed an author_key.  To overload the function, simply define the new function passing the new variables as in the partial listing below.

 36    function get_author
 37      (v_book_key in book.book_key%type)
    return varchar2
 38    is
 39      auth_store author.author_last_name%type;
 40    begin
 41      select author_last_name into auth_store
 42      from author join book_author
           using (author_key)
 43      where book_key = v_book_key;
 44      return auth_store;
 45    end;
 46
 47    function get_author
 48      (v_auth_key in author.author_key%type)
          return varchar2
 49    is
 50      auth_store author.author_last_name%type;
 51    begin
 52      select author_last_name into auth_store
 53      from author
 54      where author_key = v_auth_key;
 55      return auth_store;
 56    end;
 57  end myapp;
 58  /

Package body created.

The function get_author is now overloaded inside the myapp package.  Before anyone can access the overloaded function, it must be added to the specification.

SQL> create or replace package myapp is
  2    procedure update_book_date
  3      (v_book_key in book.book_key%type,
  4       v_pub_date in     
                   book.book_date_published%type);
  5    function get_author
  6      (v_book_key in book.book_key%type)
          return varchar2;
  7    function get_author
  8      (v_auth_key in author.author_key%type)
  9       return varchar2;
 10  end myapp;
 11  /

The new function can now be accessed from outside the package.  When packages are used, it is important to use descriptive names for passed parameters.  When the package is described, these names are exposed.

SQL> desc myapp 

FUNCTION GET_AUTHOR RETURNS VARCHAR2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ -------- V_BOOK_KEY                     VARCHAR2(6)             IN

FUNCTION GET_AUTHOR RETURNS VARCHAR2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 V_AUTH_KEY                     VARCHAR2(11)            IN

PROCEDURE UPDATE_BOOK_DATE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ -------- V_BOOK_KEY                     VARCHAR2(6)             IN
 V_PUB_DATE                     DATE                    IN

Many times the example above is all a user who is attempting to use this package will see.  A descriptive parameter name is important in helping the user know what variable to pass as a parameter.  Notice that the %type parameter datatypes are displayed as the actual variable’s datatype (varchar2(x) in this example), so the name becomes important in displaying to the programmer the makeup of the parameters.

So, when should you use packages?  Always!  You should write the application code in packages.  Small applications can be placed in a single package.  It is easier to write the code in a package than to write and test it as a stand alone object and then move it into a package.  Code written outside of packages and then moved into packages rarely takes advantage of the package’s code, definition sharing, and overloading.  Also, each object will have to be tested and validated again once it is in the package.

Using packages will also allow the database to more efficiently process the code.  When the first code object in a package is called, the database loads the entire package into memory.  So by placing the application into packages, the code will be loaded and maintained in memory, resulting in faster execution and more efficient use of the database resources.  In fact, the DBA can pin the package into the library cache on database startup so that it is loaded even before the first user logs on.


The above book excerpt is from:

Easy Oracle PL/SQL Programming

Get Started Fast with Working PL/SQL Code Examples

ISBN 0-9759135-7-3   

John Garmany 

http://www.rampant-books.com/book_2005_1_easy_plsql.htm


Download your Oracle scripts now:

www.oracle-script.com

The definitive Oracle Script collection for every Oracle professional DBA

 
 
 

Oracle performance tuning book

 

 

Oracle performance tuning software

 
Oracle performance tuning software
 
Oracle performance Tuning 10g reference poster
 
Oracle training in Linux commands
 
Oracle training Excel
 
 
 
 

 

email BC:


Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.