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

## Defining Call Specs: Basic Requirements

A call spec and the Java method it publishes must reside in the same schema (unless the Java method has a `PUBLIC` synonym). You can declare the call spec as a:

- stand-alone (top-level) PL/SQL function or procedure

- packaged PL/SQL function or procedure

- member method of a SQL object type

A call spec exposes a Java method's top-level entry point to Oracle. Therefore, you can publish only `public` `static` methods--with one exception. You can publish instance methods as member methods of a SQL object type.

Packaged call specs perform as well as top-level call specs. So, to ease maintenance, you might want to place call specs in a package body. That way, you can modify them without invalidating other schema objects. Also, you can overload them.

### Setting Parameter Modes

In Java and other object-oriented languages, a method cannot assign values to objects passed as arguments. So, when calling a method from SQL or PL/SQL, to change the value of an argument, you must declare it as an `OUT` or `IN` `OUT` parameter in the call spec. The corresponding Java parameter must be a one-element array.

You can replace the element value with another Java object of the appropriate type, or (with `IN` `OUT` parameters) you can modify the value if the Java type permits. Either way, the new value propagates back to the caller. For example, you might map a call spec `OUT` parameter of type `NUMBER` to a Java parameter declared as `float[] p`, then assign a new value to `p[0]`.

Note: A function that declares `OUT` or `IN` `OUT` parameters cannot be called from SQL DML statements.

### Mapping Datatypes

In a call spec, corresponding SQL and Java parameters (and function results) must have compatible datatypes. Table 3-1 contains all the legal datatype mappings. Oracle converts between the SQL types and Java classes automatically.

##### Table 3-1 Legal Datatype Mappings

SQL Type | Java Class
--- | ---
`CHAR`, `NCHAR`, `LONG`, `VARCHAR2`, `NVARCHAR2` | `oracle.sql.CHAR java.lang.String java.sql.Date java.sql.Time java.sql.Timestamp java.lang.Byte java.lang.Short java.lang.Integer java.lang.Long java.lang.Float java.lang.Double java.math.BigDecimal byte,` `short,` `int,` `long,` `float,` `double`
`DATE` | `oracle.sql.DATE java.sql.Date java.sql.Time java.sql.Timestamp java.lang.String`
`NUMBER` | `oracle.sql.NUMBER java.lang.Byte java.lang.Short java.lang.Integer java.lang.Long java.lang.Float java.lang.Double java.math.BigDecimal byte,` `short,` `int,` `long,` `float,` `double`
`RAW`, `LONG` `RAW` | `oracle.sql.RAW byte[]`
`ROWID` | `oracle.sql.CHAR oracle.sql.ROWID java.lang.String`
`BFILE` | `oracle.sql.BFILE`
`BLOB` | `oracle.sql.BLOB oracle.jdbc2.Blob`
`CLOB`, `NCLOB` | `oracle.sql.CLOB oracle.jdbc2.Clob`
`OBJECT` | `oracle.sql.STRUCT oracle.SqljData oracle.jdbc2.Struct`
`REF` | `oracle.sql.REF oracle.jdbc2.Ref`
`TABLE`, `VARRAY` | `oracle.sql.ARRAY oracle.jdbc2.Array`
any of the above SQL types | `oracle.sql.CustomDatum oracle.sql.Datum`
4. Notes: The type `UROWID` and the `NUMBER` subtypes (`INTEGER`, `REAL`, and so on) are not supported. You cannot retrieve a value larger than 32KB from a `LONG` or `LONG` `RAW` database column into a Java stored procedure. The Java wrapper classes (`java.lang.Byte`, `java.lang.Short`, and so on) are useful for returning nulls from SQL. When you use the class `oracle.sql.CustomDatum` to declare parameters, it must define the following member:
       public static oracle.sql.CustomDatumFactory.getFactory();
`oracle.sql.Datum` is an abstract class. The value passed to a parameter of type `oracle.sql.Datum` must belong to a Java class compatible with the SQL type. Likewise, the value returned by a method with return type `oracle.sql.Datum` must belong to a Java class compatible with the SQL type. The mappings to `oracle.sql` classes are optimal because they preserve data formats and require no character-set conversions (apart from the usual network conversions). Those classes are especially useful in applications that "shovel" data between SQL and Java. For information about the supplied packages `oracle.jdbc2` and `oracle.sql`, see the Oracle8i JDBC Developer's Guide and Reference.

### Using the Server-Side Internal JDBC Driver

Normally, with JDBC, you establish a connection to the database using the `DriverManager` class, which manages a set of JDBC drivers. Once the JDBC drivers are loaded, you call the method `getConnection`. When it finds the right driver, `getConnection` returns a `Connection` object, which represents a database session. All SQL statements are executed within the context of that session.

However, the server-side internal JDBC driver runs within a default session and default transaction context. So, you are already "connected" to the database, and all your SQL operations are part of the default transaction. You need not register the driver because it comes pre-registered. To get a `Connection` object, simply execute the following statement:

    Connection conn =
      DriverManager.getConnection("jdbc:default:connection:");

Use class `Statement` for SQL statements that take no `IN` parameters and are executed only once. When invoked on a `Connection` object, method `createStatement` returns a new `Statement` object. An example follows:

    String sql = "DROP " + object_type + " " + object_name;
    Statement stmt = conn.createStatement();
    stmt.executeUpdate(sql);

Use class `PreparedStatement` for SQL statements that take `IN` parameters or are executed more than once. The SQL statement, which can contain one or more parameter placeholders, is precompiled. (Question marks serve as placeholders.) When invoked on a `Connection` object, method `prepareStatement` returns a new `PreparedStatement` object, which contains the precompiled SQL statement. Here is an example:

    String sql = "DELETE FROM dept WHERE deptno = ?";
    PreparedStatement pstmt = conn.prepareStatement(sql);
    pstmt.setInt(1, deptID);
    pstmt.executeUpdate();

A `ResultSet` object contains SQL query results, that is, the rows that met the search condition. You use the method `next` to move to the next row, which becomes the current row. You use the `get` `XXX` methods to retrieve column values from the current row. An example follows:

    String sql = "SELECT COUNT(*) FROM " + tabName;
    int rows = 0;
    Statement stmt = conn.createStatement();
    ResultSet rset = stmt.executeQuery(sql);
    while (rset.next()) {rows = rset.getInt(1);}

A `CallableStatement` object lets you call stored procedures. It contains the call text, which can include a return parameter and any number of `IN`, `OUT`, and `INOUT` parameters. The call is written using an escape clause, which is delimited by braces. As the following examples show, the escape syntax has three forms:

    // parameterless stored procedure
    CallableStatement cstmt = conn.prepareCall("{CALL proc}");

    // stored procedure
    CallableStatement cstmt = conn.prepareCall("{CALL proc(?,?)}");

    // stored function
    CallableStatement cstmt = conn.prepareCall("{? = CALL func(?,?)}");

#### Important Points

When developing JDBC stored procedure applications, keep the following points in mind:

- The server-side internal JDBC driver runs within a default session and default transaction context. So, you are already "connected" to the database, and all your SQL operations are part of the default transaction.

- Statements and result sets persist across calls, and their finalizers do not release database cursors. So, to avoid running out of cursors, close all statements and result sets when you are done with them. Alternatively, you can ask your DBA to raise the limit set by the Oracle initialization parameter `OPEN_CURSORS`.

- The server-side internal JDBC driver does not support auto-commits. So, your application must explicitly commit or roll back database changes.

- You cannot connect to a remote database using the server-side internal JDBC driver. You can "connect" only to the server running your Java program. For server-to-server connections, use the server-side JDBC Thin driver. (For client-to-server connections, use the client-side JDBC Thin or JDBC OCI driver.)

- You cannot close the physical connection to the database established by the server-side internal JDBC driver. However, if you call method `close()` on the default connection, all connection instances (which, in fact, reference the same object) are cleaned up and closed. To get a new connection object, you must call method `getConnection()` again.

For more information, see the Oracle8i JDBC Developer's Guide and Reference.

### Using the Server-Side SQLJ Translator

The SQLJ translator lets you embed SQL statements in your Java source files using an escape clause, which begins with `#sql`. For example, the SQLJ input file below embeds `SELECT` and `CALL` statements in the definition of Java class `TodaysDate`. No explicit connection handling is required for the server-side execution of SQLJ programs.

    import java.sql.*;
    class TodaysDate {
      public static void main (String[] args) {
        try {
          Date today;
          #sql {SELECT SYSDATE INTO :today FROM dual};
          putLine("Today is " + today);
        } catch (Exception e) {putLine("Run-time error: " + e);}
      }

      static void putLine(String s) {
        try {
          #sql {CALL DBMS_OUTPUT.PUT_LINE(:s)};
        } catch (SQLException e) {}
      }
    }

SQLJ provides the following convenient syntax for calling stored procedures and functions:

    // parameterless stored procedure
    #sql {CALL procedure_name()};

    // stored procedure
    #sql {CALL procedure_name(parameter, parameter, ...)};

    // stored function
    #sql result = {VALUES(function_name(parameter, parameter, ...))};

where `parameter` stands for the following syntax:

    {literal | :[{IN | OUT | INOUT}] host_variable_name}

If `host_variable_name` is a dot-qualified expression (such as `max.Salary`), it must be enclosed in parentheses.

You can use the client-side SQLJ translator to compile source files and customize profiles. Then, you can upload the resulting class and resource file into the database. Alternatively, you can use the server-side SQLJ translator to compile source files after they are uploaded. If you are writing programs on the client side, the first method is more flexible because most SQLJ translator options are not available on the server side.

#### Important Points

When developing SQLJ stored procedure applications, keep the following points in mind:

- The SQLJ run-time packages are available automatically on the server. You need not import them to use the run-time classes.

- The current user has an implicit channel to the database. So, you need not create a SQLJ connection-context instance, register a driver, or specify a default connection for your `#sql` statements.

- You cannot connect to a remote database using the server-side internal JDBC driver. You can "connect" only to the server running your Java program. For server-to-server connections, use the server-side JDBC Thin driver. (For client-to-server connections, use the client-side JDBC Thin or JDBC OCI driver.)

- A SQLJ connection-context instance communicates with the database through the session that is running your Java program, not through a true connection. So, any attempt to close the connection-context instance is ignored, and no exception is thrown.

- Option settings for the server-side SQLJ translator are stored in the database table `JAVA$OPTIONS`. You can get and set the option values using functions and procedures in the utility package `DBMS_JAVA`.

- The server-side SQLJ translator does not support the option `-ser2class`. So, it always generates profiles as resource schema objects, never as class schema objects.

- On the server side (but not on the client side), the SQLJ translator lets you give different names to an input source file and its first public class. However, it is a poor programming practice to use different names.

For more information, see the Oracle8i SQLJ Developer's Guide and Reference.

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