Release 2 (9.2)
Part Number A96659-01
 Home |
 Book List |
 Contents |
 Index |
 Master Index |
 Feedback |
| Oracle9i Java Stored Procedures Developer's Guide Release 2 (9.2) Part Number A96659-01 |
|
Before calling Java methods from SQL, you must publish them in the Oracle data dictionary. When you load a Java class into the database, its methods are not published automatically because Oracle does not know which methods are safe entrypoints for calls from SQL. To publish the methods, you must write call specifications (call specs), which map Java method names, parameter types, and return types to their SQL counterparts.
To publish Java methods, you write call specs. For a given Java method, you declare a function or procedure call spec using the SQL CREATE FUNCTION or CREATE PROCEDURE statement. Inside a PL/SQL package or SQL object type, you use similar declarations.
You publish value-returning Java methods as functions or procedures and void Java methods as procedures. The function or procedure body contains the LANGUAGE JAVA clause. This clause records information about the Java method including its full name, its parameter types, and its return type. Mismatches are detected only at run time.
As Figure 3-1 shows, applications call the Java method through its call spec, that is, by referencing the call-spec name. The run-time system looks up the call-spec definition in the Oracle data dictionary, then executes the corresponding Java method.
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:
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.
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].
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.
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 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 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(?,?)}");
When developing JDBC stored procedure applications, keep the following points in mind:
OPEN_CURSORS.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 Oracle9i JDBC Developer's Guide and Reference.
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 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.
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 the utility package DBMS_JAVA.-ser2class. So, it always generates profiles as resource schema objects, never as class schema objects.For more information, see the Oracle9i SQLJ Developer's Guide and Reference.
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 Oracle9i 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()
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.*; 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.
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 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
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.*; 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';
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 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.
A PL/SQL package is a schema object that groups logically related types, items, and subprograms. Usually, packages have two parts, a specification (spec) and a body (sometimes the body is unnecessary). The spec is the interface to your applications: it declares the types, constants, variables, exceptions, cursors, and subprograms available for use. The body fully defines cursors and subprograms, thereby implementing the spec. (For details, see the PL/SQL User's Guide and Reference.)
In SQL*Plus, you can define PL/SQL packages interactively using this syntax:
CREATE [OR REPLACE] PACKAGE package_name [AUTHID {CURRENT_USER | DEFINER}] {IS | AS} [type_definition [type_definition] ...] [cursor_spec [cursor_spec] ...] [item_declaration [item_declaration] ...] [{subprogram_spec | call_spec} [{subprogram_spec | call_spec}]...] END [package_name]; [CREATE [OR REPLACE] PACKAGE BODY package_name {IS | AS} [type_definition [type_definition] ...] [cursor_body [cursor_body] ...] [item_declaration [item_declaration] ...] [{subprogram_spec | call_spec} [{subprogram_spec | call_spec}]...] [BEGIN sequence_of_statements] END [package_name];]
The spec holds public declarations, which are visible to your application. The body contains implementation details and private declarations, which are hidden from your application. Following the declarative part of the package body is the optional initialization part, which typically holds statements that initialize package variables. It is run only once, the first time you reference the package.
A call spec declared in a package spec cannot have the same signature (name and parameter list) as a subprogram in the package body. If you declare all the subprograms in a package spec as call specs, the package body is unnecessary (unless you want to define a cursor or use the initialization part).
The AUTHID clause determines whether all the packaged subprograms execute with the privileges of their definer (the default) or invoker, and whether their unqualified references to schema objects are resolved in the schema of the definer or invoker.
Consider the Java class DeptManager, which has methods for adding a new department, dropping a department, and changing the location of a department. Notice that method addDept uses a database sequence to get the next department number. The three methods are logically related, so you might want to group their call specs in a PL/SQL package.
import java.sql.*; import java.io.*; import oracle.jdbc.*; public class DeptManager { public static void addDept (String deptName, String deptLoc) throws SQLException { Connection conn = DriverManager.getConnection("jdbc:default:connection:"); String sql = "SELECT deptnos.NEXTVAL FROM dual"; String sql2 = "INSERT INTO dept VALUES (?, ?, ?)"; int deptID = 0; try { PreparedStatement pstmt = conn.prepareStatement(sql); ResultSet rset = pstmt.executeQuery(); while (rset.next()) {deptID = rset.getInt(1);} pstmt = conn.prepareStatement(sql2); pstmt.setInt(1, deptID); pstmt.setString(2, deptName); pstmt.setString(3, deptLoc); pstmt.executeUpdate(); rset.close(); pstmt.close(); } catch (SQLException e) {System.err.println(e.getMessage());} } public static void dropDept (int deptID) throws SQLException { Connection conn = DriverManager.getConnection("jdbc:default:connection:"); String sql = "DELETE FROM dept WHERE deptno = ?"; try { PreparedStatement pstmt = conn.prepareStatement(sql); pstmt.setInt(1, deptID); pstmt.executeUpdate(); pstmt.close(); } catch (SQLException e) {System.err.println(e.getMessage());} } public static void changeLoc (int deptID, String newLoc) throws SQLException { Connection conn = DriverManager.getConnection("jdbc:default:connection:"); String sql = "UPDATE dept SET loc = ? WHERE deptno = ?"; try { PreparedStatement pstmt = conn.prepareStatement(sql); pstmt.setString(1, newLoc); pstmt.setInt(2, deptID); pstmt.executeUpdate(); pstmt.close(); } catch (SQLException e) {System.err.println(e.getMessage());} } }
Suppose you want to package methods addDept, dropDept, and changeLoc. First, you create the package spec, as follows:
CREATE OR REPLACE PACKAGE dept_mgmt AS PROCEDURE add_dept (dept_name VARCHAR2, dept_loc VARCHAR2); PROCEDURE drop_dept (dept_id NUMBER); PROCEDURE change_loc (dept_id NUMBER, new_loc VARCHAR2); END dept_mgmt;
Then, you create the package body by writing call specs for the Java methods:
CREATE OR REPLACE PACKAGE BODY dept_mgmt AS PROCEDURE add_dept (dept_name VARCHAR2, dept_loc VARCHAR2) AS LANGUAGE JAVA NAME 'DeptManager.addDept(java.lang.String, java.lang.String)'; PROCEDURE drop_dept (dept_id NUMBER) AS LANGUAGE JAVA NAME 'DeptManager.dropDept(int)'; PROCEDURE change_loc (dept_id NUMBER, new_loc VARCHAR2) AS LANGUAGE JAVA NAME 'DeptManager.changeLoc(int, java.lang.String)'; END dept_mgmt;
To reference the stored procedures in the package dept_mgmt, you must use dot notation, as the following example shows:
CALL dept_mgmt.add_dept('PUBLICITY', 'DALLAS');
In SQL, object-oriented programming is based on object types, which are user-defined composite data types that encapsulate a data structure along with the functions and procedures needed to manipulate the data. The variables that form the data structure are known as attributes. The functions and procedures that characterize the behavior of the object type are known as methods, which can be written in Java.
SQLJ Types are implemented to support automatic generation. You can create SQL types that correspond to Java types, which implement SQLData. SQLJ object types are discussed in Oracle9i JDBC Developer's Guide and Reference. In addition, you can use JPublisher as a convenient method for creating classes that implement SQL data. SeeOracle9i JPublisher User's Guide for more information.
As with a package, an object type has two parts: a specification (spec) and a body. The spec is the interface to your applications; it declares a data structure (set of attributes) along with the operations (methods) needed to manipulate the data. The body implements the spec by defining PL/SQL subprogram bodies or call specs. (For details, see the PL/SQL User's Guide and Reference.)
If an object type spec declares only attributes or call specs, then the object type body is unnecessary. (You cannot declare attributes in the body.) So, if you implement all your methods in Java, you can place their call specs in the object type spec and omit the body.
In SQL*Plus, you can define SQL object types interactively using this syntax:
CREATE [OR REPLACE] TYPE type_name [AUTHID {CURRENT_USER | DEFINER}] {IS | AS} OBJECT ( attribute_name datatype[, attribute_name datatype]... [{MAP | ORDER} MEMBER {function_spec | call_spec},] [{MEMBER | STATIC} {subprogram_spec | call_spec} [, {MEMBER | STATIC} {subprogram_spec | call_spec}]...] ); [CREATE [OR REPLACE] TYPE BODY type_name {IS | AS} { {MAP | ORDER} MEMBER function_body; | {MEMBER | STATIC} {subprogram_body | call_spec};} [{MEMBER | STATIC} {subprogram_body | call_spec};]... END;]
The AUTHID clause determines whether all member methods execute with the current user privileges--which determines invoker's or definer's rights.
In an object type spec, all attributes must be declared before any methods. At least one attribute is required (the maximum is 1000). Methods are optional.
As with a Java variable, you declare an attribute with a name and datatype. The name must be unique within the object type but can be reused in other object types. The datatype can be any SQL type except LONG, LONG RAW, NCHAR, NVARCHAR2, NCLOB, ROWID, or UROWID.
You cannot initialize an attribute in its declaration using the assignment operator or DEFAULT clause. Furthermore, you cannot impose the NOT NULL constraint on an attribute. However, objects can be stored in database tables on which you can impose constraints.
MEMBER methods accept a built-in parameter known as SELF, which is an instance of the object type. Whether declared implicitly or explicitly, it is always the first parameter passed to a MEMBER method. In the method body, SELF denotes the object whose method was invoked. MEMBER methods are invoked on instances, as follows:
instance_expression.method()
However, STATIC methods, which cannot accept or reference SELF, are invoked on the object type, not its instances, as follows:
object_type_name.method()
If you want to call a non-static Java method, you specify the keyword MEMBER in its call spec. Likewise, if you want to call a static Java method, you specify the keyword STATIC in its call spec.
The values of a SQL scalar datatype such as CHAR have a predefined order, which allows them to be compared. However, instances of an object type have no predefined order. To put them in order, SQL calls a user-defined map method.
SQL uses the ordering to evaluate Boolean expressions such as x > y and to make comparisons implied by the DISTINCT, GROUP BY, and ORDER BY clauses. A map method returns the relative position of an object in the ordering of all such objects. An object type can contain only one map method, which must be a parameterless function with one of the following return types: DATE, NUMBER, or VARCHAR2.
Alternatively, you can supply SQL with an order method, which compares two objects. Every order method takes just two parameters: the built-in parameter SELF and another object of the same type. If o1 and o2 are objects, a comparison such as o1 > o2 calls the order method automatically. The method returns a negative number, zero, or a positive number signifying that SELF is respectively less than, equal to, or greater than the other parameter. An object type can contain only one order method, which must be a function that returns a numeric result.
You can declare a map method or an order method but not both. If you declare either method, you can compare objects in SQL and PL/SQL. However, if you declare neither method, you can compare objects only in SQL and solely for equality or inequality. (Two objects of the same type are equal if the values of their corresponding attributes are equal.)
Every object type has a constructor method (constructor for short), which is a system-defined function with the same name as the object type. The constructor initializes and returns an instance of that object type.
Oracle generates a default constructor for every object type. The formal parameters of the constructor match the attributes of the object type. That is, the parameters and attributes are declared in the same order and have the same names and datatypes. SQL never calls a constructor implicitly, so you must call it explicitly. Constructor calls are allowed wherever function calls are allowed.
|
Note: To invoke a Java constructor from SQL, you must wrap calls to it in a |
In this section, each example builds on the previous one. To begin, you create two SQL object types to represent departments and employees. First, you write the spec for object type Department. The body is unnecessary because the spec declares only attributes.
CREATE TYPE Department AS OBJECT ( deptno NUMBER(2), dname VARCHAR2(14), loc VARCHAR2(13) );
Then, you create object type Employee. Its last attribute, deptno, stores a handle, called a ref, to objects of type Department. A ref indicates the location of an object in an object table, which is a database table that stores instances of an object type. The ref does not point to a specific instance copy in memory. To declare a ref, you specify the datatype REF and the object type that the ref targets.
CREATE TYPE Employee AS OBJECT ( empno NUMBER(4), ename VARCHAR2(10), job VARCHAR2(9), mgr NUMBER(4), hiredate DATE, sal NUMBER(7,2), comm NUMBER(7,2), deptno REF Department );
Next, you create SQL object tables to hold objects of type Department and Employee. First, you create object table depts, which will hold objects of type Department. You populate the object table by selecting data from the relational table dept and passing it to a constructor, which is a system-defined function with the same name as the object type. You use the constructor to initialize and return an instance of that object type.
CREATE TABLE depts OF Department AS SELECT Department(deptno, dname, loc) FROM dept;
Finally, you create the object table emps, which will hold objects of type Employee. The last column in object table emps, which corresponds to the last attribute of object type Employee, holds references to objects of type Department. To fetch the references into that column, you use the operator REF, which takes as its argument a table alias associated with a row in an object table.
CREATE TABLE emps OF Employee AS SELECT Employee(e.empno, e.ename, e.job, e.mgr, e.hiredate, e.sal, e.comm, (SELECT REF(d) FROM depts d WHERE d.deptno = e.deptno)) FROM emp e;
Selecting a ref returns a handle to an object; it does not materialize the object itself. To do that, you can use methods in class oracle.sql.REF, which supports Oracle object references. This class, which is a subclass of oracle.sql.Datum, extends the standard JDBC interface oracle.jdbc2.Ref. For more information, see the Oracle9i JDBC Developer's Guide and Reference.
To continue, you write a Java stored procedure. The class Paymaster has one method, which computes an employee's wages. The method getAttributes() defined in class oracle.sql.STRUCT uses the default JDBC mappings for the attribute types. So, for example, NUMBER maps to BigDecimal.
import java.sql.*; import java.io.*; import oracle.sql.*; import oracle.jdbc.*; import oracle.oracore.*; import oracle.jdbc2.*; import java.math.*; public class Paymaster { public static BigDecimal wages(STRUCT e) throws java.sql.SQLException { // Get the attributes of the Employee object. Object[] attribs = e.getAttributes(); // Must use numeric indexes into the array of attributes. BigDecimal sal = (BigDecimal)(attribs[5]); // [5] = sal BigDecimal comm = (BigDecimal)(attribs[6]); // [6] = comm BigDecimal pay = sal; if (comm != null) pay = pay.add(comm); return pay; } }
Because the method wages returns a value, you write a function call spec for it, as follows:
CREATE OR REPLACE FUNCTION wages (e Employee) RETURN NUMBER AS LANGUAGE JAVA NAME 'Paymaster.wages(oracle.sql.STRUCT) return BigDecimal';
This is a top-level call spec because it is not defined inside a package or object type.
To make access to object attributes more natural, you can create a Java class that implements the SQLData interface. To do so, you must provide the methods readSQL() and writeSQL() as defined by the SQLData interface. The JDBC driver calls method readSQL() to read a stream of database values and populate an instance of your Java class. (For details, see the Oracle9i JDBC Developer's Guide and Reference) In the following example, you revise class Paymaster, adding a second method named raiseSal():
import java.sql.*; import java.io.*; import oracle.sql.*; import oracle.jdbc.*; import oracle.oracore.*; import oracle.jdbc2.*; import java.math.*; public class Paymaster implements SQLData { // Implement the attributes and operations for this type. private BigDecimal empno; private String ename; private String job; private BigDecimal mgr; private Date hiredate; private BigDecimal sal; private BigDecimal comm; private Ref dept; public static BigDecimal wages(Paymaster e) { BigDecimal pay = e.sal; if (e.comm != null) pay = pay.add(e.comm); return pay; } public static void raiseSal(Paymaster[] e, BigDecimal amount) { e[0].sal = // IN OUT passes [0] e[0].sal.add(amount); // increase salary by given amount } // Implement SQLData interface. private String sql_type; public String getSQLTypeName() throws SQLException { return sql_type; } public void readSQL(SQLInput stream, String typeName) throws SQLException { sql_type = typeName; empno = stream.readBigDecimal(); ename = stream.readString(); job = stream.readString(); mgr = stream.readBigDecimal(); hiredate = stream.readDate(); sal = stream.readBigDecimal(); comm = stream.readBigDecimal(); dept = stream.readRef(); } public void writeSQL(SQLOutput stream) throws SQLException { stream.writeBigDecimal(empno); stream.writeString(ename); stream.writeString(job); stream.writeBigDecimal(mgr); stream.writeDate(hiredate); stream.writeBigDecimal(sal); stream.writeBigDecimal(comm); stream.writeRef(dept); } }
You must revise the call spec for method wages, as follows, because its parameter has changed from oralce.sql.STRUCT to Paymaster:
CREATE OR REPLACE FUNCTION wages (e Employee) RETURN NUMBER AS LANGUAGE JAVA NAME 'Paymaster.wages(Paymaster) return BigDecimal';
Because the new method raiseSal is void, you write a procedure call spec for it, as follows:
CREATE OR REPLACE PROCEDURE raise_sal (e IN OUT Employee, r NUMBER) AS LANGUAGE JAVA NAME 'Paymaster.raiseSal(Paymaster[], java.math.BigDecimal)';
Again, this is a top-level call spec.
Later, you decide to drop the top-level call specs wages and raise_sal and redeclare them as methods of object type Employee. In an object type spec, all methods must be declared after the attributes. The object type body is unnecessary because the spec declares only attributes and call specs.
CREATE TYPE Employee AS OBJECT ( empno NUMBER(4), ename VARCHAR2(10), job VARCHAR2(9), mgr NUMBER(4), hiredate DATE, sal NUMBER(7,2), comm NUMBER(7,2), deptno REF Department MEMBER FUNCTION wages RETURN NUMBER AS LANGUAGE JAVA NAME 'Paymaster.wages() return java.math.BigDecimal', MEMBER PROCEDURE raise_sal (r NUMBER) AS LANGUAGE JAVA NAME 'Paymaster.raiseSal(java.math.BigDecimal)' );
Then, you revise class Paymaster accordingly. You need not pass an array to method raiseSal because the SQL parameter SELF corresponds directly to the Java parameter this--even when SELF is declared as IN OUT (the default for procedures).
import java.sql.*; import java.io.*; import oracle.sql.*; import oracle.jdbc.*; import oracle.oracore.*; import oracle.jdbc2.*; import java.math.*; public class Paymaster implements SQLData { // Implement the attributes and operations for this type. private BigDecimal empno; private String ename; private String job; private BigDecimal mgr; private Date hiredate; private BigDecimal sal; private BigDecimal comm; private Ref dept; public BigDecimal wages() { BigDecimal pay = sal; if (comm != null) pay = pay.add(comm); return pay; } public void raiseSal(BigDecimal amount) { // For SELF/this, even when IN OUT, no array is needed. sal = sal.add(amount); } // Implement SQLData interface. String sql_type; public String getSQLTypeName() throws SQLException { return sql_type; } public void readSQL(SQLInput stream, String typeName) throws SQLException { sql_type = typeName; empno = stream.readBigDecimal(); ename = stream.readString(); job = stream.readString(); mgr = stream.readBigDecimal(); hiredate = stream.readDate(); sal = stream.readBigDecimal(); comm = stream.readBigDecimal(); dept = stream.readRef(); } public void writeSQL(SQLOutput stream) throws SQLException { stream.writeBigDecimal(empno); stream.writeString(ename); stream.writeString(job); stream.writeBigDecimal(mgr); stream.writeDate(hiredate); stream.writeBigDecimal(sal); stream.writeBigDecimal(comm); stream.writeRef(dept); } }
|
 Copyright © 2000, 2002 Oracle Corporation. All Rights Reserved. |
|