This discussion is archived
3 Replies Latest reply: Jan 8, 2013 10:14 PM by 831674 RSS

Why we have to create Package instead of procedure or function in Oracle

831674 Newbie
Currently Being Moderated
Hi ,

Recently , i have attended one interview, they have asked me,
why we have to create Package instead of procedure or function in oracle. Is any specific advantage ,if we create package .
I am not sure . can u please tell me any one .

Thanks&Regards,
Sanjeev.
  • 1. Re: Why we have to create Package instead of procedure or function in Oracle
    SalmanQureshi Expert
    Currently Being Moderated
    Hi,

    http://docs.oracle.com/cd/B10501_01/appdev.920/a96624/09_packs.htm#401

    Salman
  • 2. Re: Why we have to create Package instead of procedure or function in Oracle
    Purvesh K Guru
    Currently Being Moderated
    user13483989 wrote:
    Hi ,

    Recently , i have attended one interview, they have asked me,
    why we have to create Package instead of procedure or function in oracle. Is any specific advantage ,if we create package .
    I am not sure . can u please tell me any one .

    Thanks&Regards,
    Sanjeev.
    There isn't a reason to create a Package instead of Function or Procedure.

    Package is an object to Group logically related sub-programs (function/procedures). So, instead of creating 20 different sub-programs that are logically related and help in achieving a result, it would be suggested to group them into a package. It helps keep code maintainable.

    Also, Oracle mentions,
    Packages have a long history in software engineering, offering important features for reliable, maintainable, reusable code, often in team development efforts for large systems.

    For more information on Oracle PL/SQL Packages.

    Edited by: Purvesh K on Jan 9, 2013 12:07 PM
  • 3. Re: Why we have to create Package instead of procedure or function in Oracle
    Chanchal Wankhade Journeyer
    Currently Being Moderated
    Hi,

    Some part from oracle documentation.

    What Is a PL/SQL Package?
    A package is a schema object that groups logically related PL/SQL types, variables, and subprograms. Packages usually have two parts, a specification (spec) and a body; sometimes the body is unnecessary. The specification is the interface to the package. It declares the types, variables, constants, exceptions, cursors, and subprograms that can be referenced from outside the package. The body defines the queries for the cursors and the code for the subprograms.

    Advantages of PL/SQL Packages
    Packages have a long history in software engineering, offering important features for reliable, maintainable, reusable code, often in team development efforts for large systems.

    Modularity

    Packages let you encapsulate logically related types, items, and subprograms in a named PL/SQL module. Each package is easy to understand, and the interfaces between packages are simple, clear, and well defined. This aids application development.

    Easier Application Design

    When designing an application, all you need initially is the interface information in the package specs. You can code and compile a spec without its body. Then, stored subprograms that reference the package can be compiled as well. You need not define the package bodies fully until you are ready to complete the application.

    Information Hiding

    With packages, you can specify which types, items, and subprograms are public (visible and accessible) or private (hidden and inaccessible). For example, if a package contains four subprograms, three might be public and one private. The package hides the implementation of the private subprogram so that only the package (not your application) is affected if the implementation changes. This simplifies maintenance and enhancement. Also, by hiding implementation details from users, you protect the integrity of the package.

    Added Functionality

    Packaged public variables and cursors persist for the duration of a session. They can be shared by all subprograms that execute in the environment. They let you maintain data across transactions without storing it in the database.

    Better Performance

    When you call a packaged subprogram for the first time, the whole package is loaded into memory. Later calls to related subprograms in the package require no disk I/O.

    Packages stop cascading dependencies and avoid unnecessary recompiling. For example, if you change the body of a packaged function, Oracle does not recompile other subprograms that call the function; these subprograms only depend on the parameters and return value that are declared in the spec, so they are only recompiled if the spec changes.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points