External Subprograms

If a C procedure, Java method, or JavaScript function 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 9-46 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 9-47 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.

Example 9-48 implements the functionality of the Java adjuster example in JavaScript. The JavaScript function raiseSal is mapped to the PL/SQL procedure js_raise_sal, which is then invoked using an anonymous PL/SQL block.

See Also:

Oracle Database Development Guide for more information about calling external programs
Oracle Database JavaScript Developer's Guide for information about using call specifications to publish JavaScript functions

Example 9-46 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 9-47 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;
/

Example 9-48 Implement JavaScript External Procedure

CREATE OR REPLACE MLE MODULE js_adjuster LANGUAGE JAVASCRIPT AS

import oracledb from "mle-js-oracledb";

/**
* Give an employee a raise
* @param {number} empNo - ID of the employee to give a raise
* @param {number} percent - the raise in percent (0 - 100)
* @returns {number} the new salary
*/

export function raiseSal(empNo, percent) {
  if (empNo === undefined || percent === undefined) {
    throw "provide the employee ID and the raise percentage";
  }

  if(percent < 0 || percent > 100){
    throw new Error("raise must be greater than 0 and less than 100");
  }

  const result = session.execute(
    `UPDATE hr.employees
       SET salary = salary * (1 + (:percent / 100))
     WHERE employee_id = :empNo
     RETURNING new salary into :newSal`,
    {
      percent: {
        type: oracledb.NUMBER,
        val: percent,
        dir: oracledb.BIND_IN,
      },
      empNo: {
        type: oracledb.NUMBER,
        val: empNo,
        dir: oracledb.BIND_IN,
      },
      newSal: {
        type: oracledb.NUMBER,
        dir: oracledb.BIND_OUT,
      },
    },
  );

  //report an error in case the update did not affect any rows
  if(result.rowsAffected !== 1){
    throw new Error(`error updating the salary for employee ${empNo}`);
  }

  //outBinds contain the new salary returned by the RETURNING clause
  //the first element indicates the first new salary (there is only 1)
  return result.outBinds.newSal[0];
}
/

The following call specification publishes the JavaScript function raiseSal as a standalone PL/SQL function.

CREATE OR REPLACE FUNCTION js_raise_sal(
  p_empno NUMBER, 
  p_percent NUMBER
) RETURN NUMBER 
AS MLE MODULE js_adjuster
SIGNATURE 'raiseSal';
/

The PL/SQL procedure js_raise_sal is invoked by the following anonymous block.

SET SERVEROUTPUT ON;
DECLARE
  l_new_sal NUMBER;
  l_old_sal NUMBER;
  l_empNo NUMBER := 100;
BEGIN
  SELECT salary
  INTO l_old_sal
  FROM hr.employees
  WHERE employee_id = l_empNo;

DBMS_OUTPUT.PUT_LINE('Current salary for employee ' || l_empNo
                          || ' amounts to ' || l_old_sal);

l_new_sal := js_raise_sal(
  p_empno => l_empNo,
  p_percent => 10
);
 
DBMS_OUTPUT.PUT_LINE('New salary for employee ' || l_empNo
                          || ' increased to ' || l_new_sal);
END;
/

Result:

Current salary for employee 100 amounts to 24000
New salary for employee 100 increased to 26400