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

## Calling Java from PL/SQL

You can call Java stored procedures from any PL/SQL block, subprogram, or package. For example, assume that the executable for the following Java class is stored in the Oracle database:

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

    public class Adjuster {
      public static void raiseSalary (int empNo, float percent)
      throws SQLException {
        Connection conn =
          DriverManager.getConnection("jdbc:default:connection:");
        String sql = "UPDATE emp SET sal = sal * ? WHERE empno = ?";
        try {
          PreparedStatement pstmt = conn.prepareStatement(sql);
          pstmt.setFloat(1, (1 + percent / 100));
          pstmt.setInt(2, empNo);
          pstmt.executeUpdate();
          pstmt.close();
        } catch (SQLException e) {System.err.println(e.getMessage());}
      }
    }

The class `Adjuster` has one method, which raises the salary of an employee by a given percentage. Because `raiseSalary` is a `void` method, you publish it as a procedure, as follows:

    CREATE OR REPLACE PROCEDURE raise_salary (empno NUMBER, pct NUMBER)
    AS LANGUAGE JAVA
    NAME 'Adjuster.raiseSalary(int, float)';

In the following example, you call the procedure `raise_salary` from an anonymous PL/SQL block:

    DECLARE
       emp_id  NUMBER;
       percent NUMBER;
    BEGIN
       -- get values for emp_id and percent
       raise_salary(emp_id, percent);
       ...
    END;

In the next example, you call the function `row_count` (defined in "Example 3") from a stand-alone PL/SQL stored procedure:

    CREATE PROCEDURE calc_bonus (emp_id NUMBER, bonus OUT NUMBER) AS
      emp_count NUMBER;
      ...
    BEGIN
      emp_count := row_count('emp');
      ...
    END;

In the final example, you call the `raise_sal` method of object type `Employee` (defined in "Implementing Object Type Methods") from an anonymous PL/SQL block:

    DECLARE
      emp_id NUMBER(4);
      v emp_type;
    BEGIN
      -- assign a value to emp_id
      SELECT VALUE(e) INTO v FROM emps e WHERE empno = emp_id;
      v.raise_sal(500);
      UPDATE emps e SET e = v WHERE empno = emp_id;
      ...
    END;

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