Release 8.1.5
A64686-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8i
Java Stored Procedures Developer's Guide Release 8.1.5 A64686-01 |
|
A call spec and the Java method it publishes must reside in the same schema. You can define the call spec as a:
A call spec exposes a Java method's top-level entry point to Oracle. So, 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.
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 (for IN OUT parameters only) 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].
In a call spec, corresponding SQL and Java parameters (and function results) must have compatible datatypes. Table 3-1 gives all the legal datatype mappings. Oracle converts between the SQL types and Java classes automatically.
| SQL Type | Java Class |
|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
any of the above SQL types |
|
|
Notes:
|
|
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 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. To get a Connection object, simply execute the following statement:
Connection conn = new OracleDriver().defaultConnection();
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. (A question mark serves as a placeholder.) 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 getXXX 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 a variable 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(?,?)}");
When developing JDBC stored procedure applications, keep the following points in mind:
defaultConnection() always returns the same connection object. So, you need not supply a connect string.
OPEN_CURSORS.
close() on the connection has no effect.
For more information, see the Oracle8i JDBC Developer's Guide and Reference.
The SQLJ translator lets you embed SQL statements in your Java source files. For example, the SQLJ input file (.sqlj file) below embeds SELECT and CALL statements in the definition of the 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}
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 RDBMS. 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 some Translator options are not available on the server side.
When developing SQLJ stored procedure applications, keep the following points in mind:
#sql statements.
JAVA$OPTIONS. You can get and set the option values using functions and procedures in package DBMS_JAVA.
-ser2class. So, it always generates profiles as serialized resource files (.ser files), never as class files.
For more information, see the Oracle8i SQLJ Developer's Guide and Reference.
