[Ad-Oracle-Sql] 10. Packages

📋 This is my note-taking from what I learned in the class “Advanced Database Concepts”

Objective

• Creating packages
• Invoking program units in packages
• Including a forward declaration
• Creating one-time-only procedures
• Overloading program units
• Managing restrictions on packaged functions used in SQL
• Using a cursor variable in a package
• Granting execute privileges
• Finding package information with data dictionary views
• Deleting or removing packages

Packages

• Containers that can hold multiple program units

• Add functionality

  • Private program units

  • Sharing variable values

  • Overloading

  • Ease privilege granting

  • Improve performance

Brewbean’s Challenge

• Organize the many program units developed for the application
• Store values throughout a user session
• Enable a program unit to handle different data types for arguments
• Ease the granting of privileges to users

Package Specification

• Contains declarations for program units, variables, exceptions, cursors, an types
• Declare program units with the header only
• Order of declarations is important if one construct refers to another in the specification

CREATE OR REPLACE PACKAGE ordering_pkg
  IS
    pv_total_num NUMBER(3,2);
    PROCEDURE order_total_pp
      (p_bsktid IN NUMBER,
       p_cnt OUT NUMBER,
       p_sub OUT NUMBER,
       p_ship OUT NUMBER,
       p_total OUT NUMBER);
    FUNCTION ship_calc_pf
      (p_qty IN NUMBER)
      RETURN NUMBER;
END;

-- complied

Package Body

• Contains the entire program unit code for those declared in the specification
• Use program unit name in END statement to make more readable

• Also can declare any new constructs not in the specification; however, these can only be used inside this package

  We can define additional procedures, functions, or other constructs within the package body that were not declared in the specification, but these new constructs are only accessible and usable within the scope of this package, not externally

How to get the Body:

Connections -> Packages -> Package name -> Right click -> Create Body

Invoking Package Constructs

• When you want to use a function or procedure that’s inside a package, you call it just like you would if it were not in a package, but you must precede the name of the function or procedure with the name of the package followed by a period

  package_name.program_unit_name(args,…);

• If you need to use a variable that’s defined within a package, you refer to it by writing the package name first, then a period, and then the variable name

  package_name.variable_name

DELCARE
  lv_bask_num bb_basketitem.idbasket%TYPE := 12;
  lv_cnt_num NUMBER(3);
  lv_sub_num NUMBER(8,2);
  lv_ship_num NUMBER(8,2);
  lv_total_num NUMBER(8,2);
BEGIN
  ordering_pkg.order_total_pp(lv_bask_num, lv_cnt_num, lv_sub_num, lv_ship_num, lv_total_num);
  DBMS_OUTPUT.PUT_LINE(lv_cnt_num);
  DBMS_OUTPUT.PUT_LINE(lv_sub_num);
  DBMS_OUTPUT.PUT_LINE(lv_ship_num);
  DBMS_OUTPUT.PUT_LINE(lv_total_num);
END;

-- 7
-- 72.4
-- 8
-- 80.4

Package Construct Scope

• Any constructs declared in the specification are public and can be referenced from inside or outside the package
• Any constructs in the body only are private and can only be referenced by other constructs within the same package body

Package Global Constructs

• Constructs declared in the specification such as variables, cursors, types, and exceptions are global
• Global means that the value will persist throughout a user session
• Each user session maintains a separate instance of the packaged construct

Package Specification

• A specification can exist without a body
• Used to store often referenced static values

CREATE OR REPLACE PACKAGE metric_pkg IS
  cup_to_liter CONSTANT NUMBER := .24;
  pint_to_liter CONSTANT NUMBER := .47;
  qrt_to_liter CONSTANT NUMBER := .95;
END;

Improving Processing Efficiency

• Packaged constructs such as variables and cursors are stored in memory
• After the initial call, values can then be retrieved from cache in subsequent calls
• Package code is also cached

Forward Declarations

• Private program units must be ordered so that any referenced unit is located prior to the calling program unit in the package body
• You need a workaround if you want to organize program units in the body
• Forward declarations eliminate the order problem
• A forward declaration is the program unit header at the top of the package body

CREATE OR REPLACE PACKAGE BODY ordering_pkg IS
  FUNCTION ship_calc_pf
    (p_qty IN NUMBER)
    RETURN NUMBER;
  PROCEDURE order_total_pp
    (p_bsktid IN bb_basketitem.idbasket%TYPE,
     p_cnt OUT NUMBER,
     p_sub OUT NUMBER,
     p_ship OUT NUMBER,
     p_total OUT NUMBER)
    IS
  BEGIN

-- Compiled

One Time Only Procedure

• Used when user needs a dynamic action to occur on the initial call to a package
• It is an anonymous block placed at the end of a package body (no END statement!)
• Only executes on initial call to the package
• Typically used to populate global constructs

![img]

Overloading Program Units

• Overloading is the creation of more than one program unit with the same name

• The program units must differ by at least one of the following:

  • Number of parameters

  • Parameter data type families

  • Listed order

• Allows a particular program unit to accept various sets of arguments
• Some Oracle-supplied functions are overloaded, such as TO_CHAR, which can accept various data types as an argument
• Overloading can only be accomplished with a package

![img]

Packaged Function Restrictions

• Function purity level defines what structures the function reads or modifies
• Important to indicate purity level in package specification to discover errors at compile time rather than run time

• Add the following statement in the specification:

  PRAGMA RESTRICT_REFERENCES(program_unit_name, purity levels,…)

Purity Levels

Level Acronym	Level Name	Level Description
WNDS	Writes No Database State	Function does not modify any database tables (No DML)
RNDS	Reads No Database State	Function does not read any tables (No select)
WNPS	Writes No Package State	Function does not modify any packaged variables (packaged variables are variables declared in a package specification)
RNPS	Reads No Package State	Function does not read any packaged variables

![img]

REF CURSOR Parameter

![img]

Execute Privileges

• Avoids issuing privileges to all database objects
• If you issue EXECUTE privilege on a package, the user will assume the package owner rights for the period of execution - Called definer-rights
• You can override this default by adding AUTHID CURRENT_USER in the specification
• Adds security by avoiding the direct access issue of privileges to database objects

CREATE OR REPLACE PACKAGE pack_purity_pkg
AUTHID CURRENT_USER IS
  FUNCTION tax_calc_pf
    (p_amt IN NUMBER)
    RETURN NUMBER;
END;

Data Dictionary Information

• Text column of USER_SOURCE view will display the source code of the entire package – specification and body
• Use a WHERE clause on the name column to select only one package
• The USER_OBJECTS view can be used to determine what packages exist in the database

SELECT text
  FROM user_source
  WHERE name = 'PRODUCT_INFO_PKG'; <- Upper case

![img]

Deleting Packages

• To delete specification and body:

  DROP PACKAGE package_name;

• To delete the body only:

  DROP PACKAGE BODY package_name;

Summary

• A package can have two parts: a specification and a body
• Packages allow both public and private constructs
• Global construct values persist
• Forward declaration enables program unit organization
• One time only procedures only execute on the initial call to the package
• Overloading allows program units to accept different sets of arguments
• Address function purity levels
• Granting the EXECUTE privilege on a package enables definer-rights
• A REF CURSOR can pass a set of data between program units
• The USER_SOURCE data dictionary view is used to retrieve package source code
• The DROP statement is used to delete packages

---

Back to Top

See other articles in Category Ad-Oracle-Sql