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 fully qualified Java names and the call spec parameters, which are mapped by position, must correspond one to one. (That 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, fully qualified Java names are written 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 following Java class has been loaded into the RDBMS:

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 = new OracleDriver().defaultConnection();
    // 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 the 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 = new OracleDriver().defaultConnection();
    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.

---