An Internal Driver Example

In order for me to show you an internal driver example, you will have to know how to load a program into the Oracle database and publish it so it can be invoked from SQL or PL/SQL. So we'll cover these procedures in this section. By the time you're done reading this chapter you may be wondering whether it's a chapter on internal connections or on writing stored procedures. Let me assure you up front, this is a chapter about using internal connections, but that topic requires that I show you how to load and publish a Java stored procedure. Accordingly, my explanations for doing so are very terse. You can find detailed information on writing and loading Oracle Java stored procedures in the Oracle8i Java Stored Procedures Developer's Guide available on the Oracle Technology Network (OTN) web site.

1. Compile Java source into a Java class file. 2. Load the Java class file into the database. 3. Publish the Java class as a stored procedure.

To get a better understanding of this process, begin by taking a look at Example 5-1, which is a sample stored procedure written to test an internal connection.

Example 5-1. A stored procedure to test an internal connection

import java.sql.*;

class TestInternalConnection {

public static String getGreeting( )

throws ClassNotFoundException, SQLException {

// With 8.1.6 there's no need to load the dr iver anymore, // but it doesn't hurt if you do

Class.forName("oracle.jdbc.driver.OracleDriver"); String greeting = null;

Connection conn = DriverManager.getConnection("jdbc:oracle:kprb:"); Statement stmt = conn.createStatement( );

ResultSet rset = stmt.executeQuery(

"select 'Hello '||initcap(USER)||'!' result from dual"); if (rset.next( )) greeting = rset.getString(1); rset.close( ); stmt.close( ); conn.close( ); return greeting; } }

The first thing you should notice is that there is nothing remarkable about writing a Java stored procedure. It is simply a Java class with one or more static methods. Our stored procedure,

TestInternalConnection, has one static method, getGreeting( ), which returns the username of the user executing the stored procedure. Next, notice that even though as of Oracle8i Version 8.1.6, it is no longer necessary to explicitly load the driver, I do it anyway. Why? Because it's good programming practice to be consistent in how you write Java programs, regardless of whether they are internal or external. By always loading the driver, you can move your programs to either environment without any changes except to the database URL. Lastly, notice that I used the jdbc:oracle:kprb: database URL syntax.

Compile this source into a class file so we can move to the next step, which is to load it into the database.

5.2.1.1 Loading a class file into a database

If you're going to execute a Java program as a stored procedure, then somehow it must get into the database in order to be available from the database. For our examples, we'll use Oracle's loadjava utility to accomplish this task. Accordingly, to load a class file into the database, use the loadjava utility as follows:

loadjava -v -t -user username/password@host:port:sid classfile

The -v switch turns on verbose output, the -t switch tells loadjava to use the Thin driver, -user username/password@host:port:sid identifies the destination database, and the last parameter is the filename of the class to load. For example, to load

TestInternalConnection, you'll need to type a command such as the following at your operating system's command prompt:

loadjava -v -t -user scott/tiger@dssw2k01:1521:orcl TestInternalConnection.class

Go ahead and try this command yourself. Be sure that you replace the username, password, and other connection information with values that are appropriate for your environment.

5.2.1.2 Publishing a class

Now that you have TestInternalConnection loaded, you need to publish its getGreeting( ) method so you can call it as a stored procedure. To publish a Java stored procedure, you create a SQL call specification to expose its methods to the rest of the database. Since a Java class file is loaded into an Oracle database, it resides in what you could call, for lack of a better term, a Java namespace. SQL objects, such as tables, PL/SQL stored procedures, and the like exist in a SQL namespace. That's why, even though your Java program resides in the database, you still need to use JDBC to manipulate SQL objects. And from the other perspective, you need some means to tell the SQL namespace that an internal Java program exists before you can invoke one of the program's methods as a stored procedure.

In Oracle, you can create a stored procedure as a standalone function, as a standalone procedure, or as a function or procedure that is part of a package. Accordingly, to create a wrapper for a Java method, use the SQL CREATE FUNCTION or CREATE PROCEDURE syntax or the keywords function or procedure in a package definition. You can execute the

CREATE command for the SQL call specification by typing the appropriate command in SQL*Plus, but since this is a book about Java, we'll execute the DDL with a Java program instead.

Example 5-2 is a Java application that creates a function call specification named

TIC_getGreeting for TestInternalConnection's getGreeting( ) method. The DDL statement that PublishTestInternalConnection executes is:

create or replace function TIC_getGreeting return varchar2 as language java

name 'TestInternalConnection.getGreeting( ) return java.lang.String';

All that PublishTestInternalConnection does is connect to the database and execute the DDL.

Example 5-2. An application to create a stored function call specification

import java.sql.*;

class PublishTestInternalConnection {

public static void main(String[] argv) throws SQLException {

DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver( ));

String sql = "create or replace function TIC_getGreeting " + "return varchar2 " +

"as language java " +

"name 'TestInternalConnection.getGreeting( ) " + "return java.lang.String';";

Connection conn = DriverManager.getConnection(

"jdbc:oracle:thin:dssw2k01:1521:orcl", "scott", "tiger"); Statement stmt = conn.createStatement( );

if (rslt==0) System.out.println("OK"); stmt.close( ); conn.close( ); } }

Modify the database URL in Example 5-2 to a value appropriate for your installation. Then compile the program. Next, execute the program from the command line. It will log into the database and execute the SQL statement, creating the function TIC_getGreeting in the login user's schema.

5.2.1.3 Executing a Java stored procedure

Now that you have your stored procedure ready, you can test it using the application shown in Example 5-3. Once again, before running the example, modify the database URL to an appropriate value for your environment. Next, compile the program and execute it. If all works well, you should see output such as the following:

Hello Scott!

Impressive, isn't it? When CallTestInternalConnection is executed, it creates a

CallableStatement object that executes the SQL function TIC_getGreeting.

TIC_getGreeting in turn calls the Java stored procedure

TestInternalConnection.getGreeting( ). The getGreeting( ) method retrieves the user's username and returns the greeting to TIC_getGreeting, which returns it to

CallTestInternalConnection.

Example 5-3. A test application to call getGreeting( )

import java.sql.*;

class CallTestInternalConnection {

public static void main(String[] argv) throws SQLException {

DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver( ));

Connection conn = DriverManager.getConnection(

"jdbc:oracle:thin:dssw2k01:1521:orcl", "scott", "tiger"); CallableStatement cstmt = conn.prepareCall( "{?= call TIC_getGreeting}"); cstmt.registerOutParameter(1, Types.CHAR); long rslt = cstmt.executeUpdate( ); if (rslt>0) System.out.println(cstmt.getString(1)); cstmt.close( ); conn.close( ); } }

5.2.2 Internal-Connection Considerations

Now that you understand how to establish an internal connection, there are four important

considerations to note. I describe these in the following sections. If you keep these considerations in mind when writing both internal and external programs, you'll have no trouble moving those programs into and out of the database.

Any time you make a call to getConnection( ) , or to defaultConnection( ), you are actually getting the same default connection used by every other internal object, but you are returned a new Connection object. Why is this distinction important? It becomes important only if you will be using object-relational database objects and wish to use custom type maps. By using multiple Connection objects, you can use custom type maps in each connection that will in turn allow you to look at the same database object in different ways. I'll cover type maps in Chapter 16. Just keep the fact that you can use different type maps on the same object by opening an internal connection multiple times tucked away in the back of your mind in case you need it someday.

5.2.2.2 Closing one of your connections closes all of your connections

Since every Connection object really represents the same connection, if you close any one of your connections, you inadvertently close them all! Oracle recommends that you not close your connections to avoid this problem. That bothers me. It sounds like an invitation to a bad habit, so I close my connections at the end of my stored-procedure call. Do whatever you feel is

appropriate. I won't be there to say tsk-tsk, but consider the fact that if you make it a habit not to close your connections in stored procedures, they will lose their portability, and you may end up not closing your connections in applications, applets, and servlets simply out of habit.

5.2.2.3 Auto-commit is not supported

Auto-commit mode is disabled in the server. If you wish to do any transaction management in a Java stored procedure, you will have to do it manually.

5.2.2.4 Additional methods are available for use in exception handlers

For code that runs in the JServer, there are two additional Oracle methods available with the

OracleSQLException object: getNumParameters( ) and getParameters( ). These two methods make the parameters that are normally passed when calling stored procedures available inside the catch clause of an SQLException. These methods provide the following information:

int getNumParameters( )

Returns the numbers of parameters available

Object[] getParameters( )

Returns the parameter values

You will need to cast an SQLException object to an OracleSQLException object to use these methods. For example:

. . .

catch (SQLException e) {

Int numParms = (OracleSQLException)e.getNumParameters( ); . . .

}

Now that we've covered the internal driver, let's take a look at using the server-side Thin driver to connect to an external database.