Database Reference
In-Depth Information
Also, we will almost always be expecting one or more rows to be returned in a
ResultSet
, and calling
if( rs.next() )
would only ever test whether a single row is returned. When we expect to be getting
several rows, we will use a
while
block to process the
ResultSet
, for example:
while( rs.next() ) {
…
}
Method Syntax in Java Stored Procedures
I described the function of the modifier
static
in the
main()
method in Chapter 3. In Java stored
procedures, we will see that Oracle database
only calls
static
methods from Java stored procedures.
This is for a similar reason to what we saw for the
main()
method: Oracle is calling these methods
without instantiating an object of that class beforehand. We do not create an instance of
MyApp4
(listing
4-1) before we call the
getOracleTime()
method. However, just as the
main()
method can instantiate
itself or any other object, these static methods (like
getOracleTime()
) called as Java stored procedures
can instantiate themselves or other objects as needed (not everything used in a static method is static).
Calling Java from Oracle Database
The second part of our example code for a Java stored procedure (function) in Listing 4-1 is an Oracle
statement to create a function,
f_get_oracle_time
. The syntax (repeated in the following) should be
familiar—most of the Oracle keywords are identical to what we saw in Chapter 2 when we created
procedures. With Java stored procedures (and functions), we modify the procedure definition with an
indication that the code performing the processing is in a different language—in our case, Java. We
indicate what Java code is going to be called by providing the full Java method specification:
CREATE OR REPLACE FUNCTION f_get_oracle_time
RETURN VARCHAR2
AS LANGUAGE JAVA
NAME 'pkg4.MyApp4.getOracleTime() return java.lang.String';
/
Notice that our specification of the Java method includes the package name (
pkg4
), the class name
(
MyApp4
) and the method name
getOracleTime()
. In this example, we also see that the return type being
sent back from Java is specified as
java.lang.String
. If you look at the definition of the return type from
the Oracle function, it says:
RETURN VARCHAR2
The Oracle
VARCHAR2
type corresponds to the Java
String
type. In case you hadn't already guessed,
java.lang
is the package where Sun (Oracle) keeps the
String
class. Specifying a fully qualified package
as part of the parameter and return value types is required in Java stored procedure definitions.
We do need to coordinate the consistency of Java return types with the return types available in
Oracle database. Oracle includes Java classes that encapsulate their return types in the
oracle.sql
package, and for many Oracle types, there are corresponding generic Java classes in the
java.sql
package.
We will see some of these as we proceed. For more information on the Oracle database types and
associated Java types, refer to the Oracle Documentation and to Appendix A of the document called
Oracle® Database JDBC Developer's Guide
.
Although this technology is called Java stored procedures, you'll see that for the most part we will be
calling Java methods from Oracle functions. Java methods can only return one value. While that value
