Oracle8i Java Stored Procedures Developer's Guide Release 2 (8.1.6) Part Number A81358-01 |
Library | Service | Contents | Index

## 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 =
          DriverManager.getConnection("jdbc:default:connection:");
        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 =
          DriverManager.getConnection("jdbc:default:connection:");
        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 =
          DriverManager.getConnection("jdbc:default:connection:");
        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');

| Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
Library | Service | Contents | Index