8.15 External Subprograms
If a C procedure or Java method is stored in the database, you can publish it as an external subprogram and then invoke it from PL/SQL.
To publish an external subprogram, define a stored PL/SQL subprogram with a call specification. The call specification maps the name, parameter types, and return type of the external subprogram to PL/SQL equivalents. Invoke the published external subprogram by its PL/SQL name.
For example, suppose that this Java class, Adjuster
, is stored in the database:
import java.sql.*; import oracle.jdbc.driver.*; public class Adjuster { public static void raiseSalary (int empNo, float percent) throws SQLException { Connection conn = new OracleDriver().defaultConnection(); String sql = "UPDATE employees SET salary = salary * ? WHERE employee_id = ?"; 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 Java class Adjuster
has one method, raiseSalary
, which raises the salary of a specified employee by a specified percentage. Because raiseSalary
is a void
method, you publish it as a PL/SQL procedure (rather than a function).
Example 8-44 publishes the stored Java method Adjuster.raiseSalary
as a PL/SQL standalone procedure, mapping the Java method name Adjuster.raiseSalary
to the PL/SQL procedure name raise_salary
and the Java data types int
and float
to the PL/SQL data type NUMBER
. Then the anonymous block invokes raise_salary
.
Example 8-45 publishes the stored Java method java.lang.Thread.sleep
as a PL/SQL standalone procedure, mapping the Java method name to the PL/SQL procedure name java_sleep
and the Java data type long
to the PL/SQL data type NUMBER
. The PL/SQL standalone procedure sleep
invokes java_sleep
.
See Also:
Oracle Database Development Guide for more information about calling external programs
Example 8-44 PL/SQL Anonymous Block Invokes External Procedure
-- Publish Adjuster.raiseSalary as standalone PL/SQL procedure: CREATE OR REPLACE PROCEDURE raise_salary ( empid NUMBER, pct NUMBER ) AS LANGUAGE JAVA NAME 'Adjuster.raiseSalary (int, float)'; -- call specification / BEGIN raise_salary(120, 10); -- invoke Adjuster.raiseSalary by PL/SQL name END; /
Example 8-45 PL/SQL Standalone Procedure Invokes External Procedure
-- Java call specification: CREATE PROCEDURE java_sleep ( milli_seconds IN NUMBER ) AS LANGUAGE JAVA NAME 'java.lang.Thread.sleep(long)'; / CREATE OR REPLACE PROCEDURE sleep ( milli_seconds IN NUMBER ) AUTHID DEFINER IS BEGIN DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.get_time()); java_sleep (milli_seconds); DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.get_time()); END; /