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

## Writing Top-Level Call Specs

In SQL*Plus, you can define top-level call specs interactively using the following syntax:

    CREATE [OR REPLACE]
    {  PROCEDURE procedure_name [(param[, param]...)]
     | FUNCTION function_name [(param[, param]...)] RETURN sql_type}
    [AUTHID {DEFINER | CURRENT_USER}]
    [PARALLEL_ENABLE]
    [DETERMINISTIC]
    {IS | AS} LANGUAGE JAVA
    NAME 'method_fullname (java_type_fullname[, java_type_fullname]...)
      [return java_type_fullname]';

where `param` stands for the following syntax:

    parameter_name [IN | OUT | IN OUT] sql_type

The `AUTHID` clause determines whether a stored procedure executes with the privileges of its definer or invoker (the default) and whether its unqualified references to schema objects are resolved in the schema of the definer or invoker. You can override the default behavior by specifying `DEFINER`. (However, you cannot override the `loadjava` option `-definer` by specifying `CURRENT_USER`.)

The `PARALLEL_ENABLE` option declares that a stored function can be used safely in the slave sessions of parallel DML evaluations. The state of a main (logon) session is never shared with slave sessions. Each slave session has its own state, which is initialized when the session begins. The function result should not depend on the state of session (`static`) variables. Otherwise, results might vary across sessions.

The hint `DETERMINISTIC` helps the optimizer avoid redundant function calls. If a stored function was called previously with the same arguments, the optimizer can elect to use the previous result. The function result should not depend on the state of session variables or schema objects. Otherwise, results might vary across calls. Only `DETERMINISTIC` functions can be called from a function-based index or a materialized view that has query-rewrite enabled. For more information, see the statements `CREATE` `INDEX` and `CREATE` `MATERIALIZED` `VIEW` in the Oracle8i SQL Reference.

The `NAME`-clause string uniquely identifies the Java method. The Java full names and the call spec parameters, which are mapped by position, must correspond one to one. (This rule does not apply to method `main`. See Example 2.) If the Java method takes no arguments, code an empty parameter list for it but not for the function or procedure.

As usual, you write Java full names using dot notation. The following example shows that long names can be broken across lines at dot boundaries:

    artificialIntelligence.neuralNetworks.patternClassification.
      RadarSignatureClassifier.computeRange()

### Example 1

Assume that the executable for the following Java class has been loaded into the Oracle database:

    import java.sql.*;
    import java.io.*;
    import oracle.jdbc.driver.*;

    public class GenericDrop {
      public static void dropIt (String object_type, String object_name)
      throws SQLException {
        // Connect to Oracle using JDBC driver
        Connection conn =
          DriverManager.getConnection("jdbc:default:connection:");
        // Build SQL statement
        String sql = "DROP " + object_type + " " + object_name;
        try {
          Statement stmt = conn.createStatement();
          stmt.executeUpdate(sql);
          stmt.close();
        } catch (SQLException e) {System.err.println(e.getMessage());}
      }
    }

Class `GenericDrop` has one method named `dropIt`, which drops any kind of schema object. For example, if you pass the arguments `'table'` and `'emp'` to `dropIt`, the method drops database table `emp` from your schema. Let's write a call spec for this method.

    CREATE OR REPLACE PROCEDURE drop_it (
      obj_type VARCHAR2,
      obj_name VARCHAR2)
    AS LANGUAGE JAVA
    NAME 'GenericDrop.dropIt(java.lang.String, java.lang.String)';

Notice that you must fully qualify the reference to class `String`. Package `java.lang` is automatically available to Java programs but must be named explicitly in call specs.

### Example 2

As a rule, Java names and call spec parameters must correspond one to one. However, that rule does not apply to method `main`. Its `String[]` parameter can be mapped to multiple `CHAR` and/or `VARCHAR2` call spec parameters. Suppose you want to publish the following method `main`, which prints its arguments:

    public class EchoInput {
      public static void main (String[] args) {
        for (int i = 0; i < args.length; i++)
          System.out.println(args[i]);
      }
    }

To publish method `main`, you might write the following call spec:

    CREATE OR REPLACE PROCEDURE echo_input (
      s1 VARCHAR2,
      s2 VARCHAR2,
      s3 VARCHAR2)
    AS LANGUAGE JAVA
    NAME 'EchoInput.main(java.lang.String[])';

You cannot impose constraints (such as precision, size, or `NOT` `NULL`) on call spec parameters. So, you cannot specify a maximum size for the `VARCHAR2` parameters, even though you must do so for `VARCHAR2` variables, as in:

    DECLARE
      last_name VARCHAR2(20);  -- size constraint required

### Example 3

Next, you publish Java method `rowCount`, which returns the number of rows in a given database table.

    import java.sql.*;
    import java.io.*;
    import oracle.jdbc.driver.*;

    public class RowCounter {
      public static int rowCount (String tabName) throws SQLException {
        Connection conn =
          DriverManager.getConnection("jdbc:default:connection:");
        String sql = "SELECT COUNT(*) FROM " + tabName;
        int rows = 0;
        try {
          Statement stmt = conn.createStatement();
          ResultSet rset = stmt.executeQuery(sql);
          while (rset.next()) {rows = rset.getInt(1);}
          rset.close();
          stmt.close();
        } catch (SQLException e) {System.err.println(e.getMessage());}
        return rows;
      }
    }

In the following call spec, the return type is `NUMBER`, not `INTEGER`, because `NUMBER` subtypes (such as `INTEGER`, `REAL`, and `POSITIVE`) are not allowed in a call spec:

    CREATE FUNCTION row_count (tab_name VARCHAR2) RETURN NUMBER
    AS LANGUAGE JAVA
    NAME 'RowCounter.rowCount(java.lang.String) return int';

### Example 4

Suppose you want to publish the following Java method named `swap`, which switches the values of its arguments:

    public class Swapper {
      public static void swap (int[] x, int[] y) {
        int hold = x[0];
        x[0] = y[0];
        y[0] = hold;
      }
    }

The call spec below publishes Java method `swap` as call spec `swap`. The call spec declares `IN` `OUT` formal parameters because values must be passed in and out. All call spec `OUT` and `IN` `OUT` parameters must map to Java array parameters.

    CREATE PROCEDURE swap (x IN OUT NUMBER, y IN OUT NUMBER)
    AS LANGUAGE JAVA
    NAME 'Swapper.swap(int[], int[])';

Notice that a Java method and its call spec can have the same name.

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