Calling Java from the Top Level

The SQL CALL statement lets you call Java methods published at the top level, in PL/SQL packages, or in SQL object types. In SQL*Plus, you can execute the CALL statement interactively using the syntax:

CALL [schema_name.][{package_name | object_type_name}][@dblink_name]
{  procedure_name ([param[, param]...])
 | function_name ([param[, param]...]) INTO :host_variable};

where param stands for the following syntax:

{literal | :host_variable}

Host variables (that is, variables declared in a host environment) must be prefixed with a colon. The following examples show that a host variable cannot appear twice in the same CALL statement, and that a parameterless subprogram must be called with an empty parameter list:

CALL swap(:x, :x);  -- illegal: duplicate host variables
CALL balance() INTO :current_balance;  -- () required

Redirecting Output

System.out and System.err print to the current trace files. To redirect output to the SQL*Plus text buffer, use this simple workaround:

SQL> SET SERVEROUTPUT ON
SQL> CALL dbms_java.set_output(2000);

The minimum (and default) buffer size is 2,000 bytes; the maximum size is 1,000,000 bytes. In the following example, the buffer size is increased to 5,000 bytes:

SQL> SET SERVEROUTPUT ON SIZE 5000
SQL> CALL dbms_java.set_output(5000);

Output is printed when the stored procedure exits.

For more information about SQL*Plus, see the SQL*Plus User's Guide and Reference.

Example 1

In the example below, the method main accepts the name of a database table (such as 'emp') and an optional WHERE clause condition (such as 'sal > 1500'). If you omit the condition, the method deletes all rows from the table. Otherwise, the method deletes only those rows that meet the condition.

import java.sql.*;
import oracle.jdbc.driver.*;
 
public class Deleter {
  public static void main (String[] args) throws SQLException { 
    Connection conn = new OracleDriver().defaultConnection();
    String sql = "DELETE FROM " + args[0];
    if (args.length > 1) sql += " WHERE " + args[1];
    try {
      Statement stmt = conn.createStatement();
      stmt.executeUpdate(sql);
      stmt.close();
    } catch (SQLException e) {System.err.println(e.getMessage());}
  }
}

The method main can take either one or two arguments. Normally, the DEFAULT clause is used to vary the number of arguments passed to a PL/SQL subprogram. However, that clause is not allowed in a call spec. So, you must overload two packaged procedures (you cannot overload top-level procedures), as follows:

CREATE OR REPLACE PACKAGE pkg AS
  PROCEDURE delete_rows (table_name VARCHAR2);
  PROCEDURE delete_rows (table_name VARCHAR2, condition VARCHAR2);
END;

CREATE OR REPLACE PACKAGE BODY pkg AS
  PROCEDURE delete_rows (table_name VARCHAR2)
  AS LANGUAGE JAVA
  NAME 'Deleter.main(java.lang.String[])';

  PROCEDURE delete_rows (table_name VARCHAR2, condition VARCHAR2) 
  AS LANGUAGE JAVA
  NAME 'Deleter.main(java.lang.String[])';
END;

Now, you are ready to call the procedure delete_rows:

SQL> CALL pkg.delete_rows('emp', 'sal > 1500');

Call completed.

SQL> SELECT ename, sal FROM emp;

ENAME          SAL
--------- --------
SMITH          800
WARD          1250
MARTIN        1250
TURNER        1500
ADAMS         1100
JAMES          950
MILLER        1300

7 rows selected.

Example 2

Assume that the executable for the following Java class is stored in the RDBMS:

public class Fibonacci {
  public static int fib (int n) {
    if (n == 1 || n == 2)
      return 1;
    else 
      return fib(n - 1) + fib(n - 2);
  }
}

The class Fibonacci has one method named fib, which returns the nth Fibonacci number. The Fibonacci sequence (1, 1, 2, 3, 5, 8, 13, 21, ...), which was first used to model the growth of a rabbit colony, is recursive. Each term in the sequence (after the second) is the sum of the two terms that immediately precede it. Because the method fib returns a value, you publish it as a function:

CREATE OR REPLACE FUNCTION fib (n NUMBER) RETURN NUMBER
AS LANGUAGE JAVA
NAME 'Fibonacci.fib(int) return int';

Next, you declare two SQL*Plus host variables, then initialize the first one:

SQL> VARIABLE n NUMBER
SQL> VARIABLE f NUMBER
SQL> EXECUTE :n := 7;

PL/SQL procedure successfully completed.

Finally, you are ready to call the function fib. Remember, in a CALL statement, host variables must be prefixed with a colon.

SQL> CALL fib(:n) INTO :f;

Call completed.

SQL> PRINT f

         F
----------
        13

---