Writing Packaged Call Specs

A PL/SQL package is a schema object that groups logically related types, items, and subprograms. Usually, packages have two parts, a specification (spec) and a body (sometimes the body is unnecessary). The spec is the interface to your applications; it declares the types, constants, variables, exceptions, cursors, and subprograms available for use. The body fully defines cursors and subprograms, thereby implementing the spec. (For details, see the PL/SQL User's Guide and Reference.)

In SQL*Plus, you can define PL/SQL packages interactively using this syntax:

CREATE [OR REPLACE] PACKAGE package_name
  [AUTHID {CURRENT_USER | DEFINER}] {IS | AS}
  [type_definition [type_definition] ...]
  [cursor_spec [cursor_spec] ...]
  [item_declaration [item_declaration] ...]
  [{subprogram_spec | call_spec} [{subprogram_spec | call_spec}]...]
END [package_name];

[CREATE [OR REPLACE] PACKAGE BODY package_name {IS | AS}
  [type_definition [type_definition] ...]
  [cursor_body [cursor_body] ...]
  [item_declaration [item_declaration] ...]
  [{subprogram_spec | call_spec} [{subprogram_spec | call_spec}]...]
[BEGIN
  sequence_of_statements]
END [package_name];]

The spec holds public declarations, which are visible to your application. The body contains implementation details and private declarations, which are hidden from your application. Following the declarative part of the package body is the optional initialization part, which typically holds statements that initialize package variables. It is run only once, the first time you reference the package.

A call spec declared in a package spec cannot have the same signature (name and parameter list) as a subprogram in the package body. If you declare all the subprograms in a package spec as call specs, the package body is unnecessary (unless you want to define a cursor or use the initialization part).

The AUTHID clause determines whether all the packaged subprograms execute with the privileges of their definer (the default) or invoker, and whether their unqualified references to schema objects are resolved in the schema of the definer or invoker.

An Example

Consider the Java class DeptManager, which has methods for adding a new department, dropping a department, and changing the location of a department. Notice that method addDept uses a database sequence to get the next department number. The three methods are logically related, so you might want to group their call specs in a PL/SQL package.

import java.sql.*;
import java.io.*;
import oracle.jdbc.driver.*;

public class DeptManager {
  public static void addDept (String deptName, String deptLoc)
  throws SQLException {
    Connection conn = new OracleDriver().defaultConnection();
    String sql = "SELECT deptnos.NEXTVAL FROM dual";
    String sql2 = "INSERT INTO dept VALUES (?, ?, ?)";
    int deptID = 0;
    try {
      PreparedStatement pstmt = conn.prepareStatement(sql);
      ResultSet rset = pstmt.executeQuery();
      while (rset.next()) {deptID = rset.getInt(1);}
      pstmt = conn.prepareStatement(sql2);
      pstmt.setInt(1, deptID);
      pstmt.setString(2, deptName);
      pstmt.setString(3, deptLoc);
      pstmt.executeUpdate(); 
      rset.close();
      pstmt.close();
    } catch (SQLException e) {System.err.println(e.getMessage());}
  }
 
  public static void dropDept (int deptID) throws SQLException {
    Connection conn = new OracleDriver().defaultConnection();
    String sql = "DELETE FROM dept WHERE deptno = ?";
    try { 
      PreparedStatement pstmt = conn.prepareStatement(sql);
      pstmt.setInt(1, deptID);
      pstmt.executeUpdate(); 
      pstmt.close();
    } catch (SQLException e) {System.err.println(e.getMessage());}
  }
 
  public static void changeLoc (int deptID, String newLoc)
  throws SQLException {
    Connection conn = new OracleDriver().defaultConnection();
    String sql = "UPDATE dept SET loc = ? WHERE deptno = ?";
    try { 
      PreparedStatement pstmt = conn.prepareStatement(sql);
      pstmt.setString(1, newLoc);
      pstmt.setInt(2, deptID);
      pstmt.executeUpdate(); 
      pstmt.close();
    } catch (SQLException e) {System.err.println(e.getMessage());}
  }
}

Suppose you want to package methods addDept, dropDept, and changeLoc. First, you create the package spec, as follows:

CREATE OR REPLACE PACKAGE dept_mgmt AS
  PROCEDURE add_dept (dept_name VARCHAR2, dept_loc VARCHAR2);
  PROCEDURE drop_dept (dept_id NUMBER);
  PROCEDURE change_loc (dept_id NUMBER, new_loc VARCHAR2);
END dept_mgmt;

Then, you create the package body by writing call specs for the Java methods:

CREATE OR REPLACE PACKAGE BODY dept_mgmt AS
  PROCEDURE add_dept (dept_name VARCHAR2, dept_loc VARCHAR2)
  AS LANGUAGE JAVA
  NAME 'DeptManager.addDept(java.lang.String, java.lang.String)';
 
  PROCEDURE drop_dept (dept_id NUMBER) 
  AS LANGUAGE JAVA
  NAME 'DeptManager.dropDept(int)';
 
  PROCEDURE change_loc (dept_id NUMBER, new_loc VARCHAR2)
  AS LANGUAGE JAVA
  NAME 'DeptManager.changeLoc(int, java.lang.String)';
END dept_mgmt;

To reference the stored procedures in the package dept_mgmt, you must use dot notation, as the following example shows:

CALL dept_mgmt.add_dept('PUBLICITY', 'DALLAS');

---