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

## 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

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