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

On the server, the default output device is a trace file, not the user screen. As a result, System.out and System.err print to the current trace files. To redirect output to the SQL*Plus text buffer, call the procedure set_output() in package DBMS_JAVA, as follows:

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

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

---