SQLJ in the Server

Creating SQLJ Code for Use in the Server

With few exceptions, writing SQLJ code for use in the Oracle8i server is identical to writing SQLJ code for client-side use. The few differences are due to Oracle JDBC characteristics or general Java characteristics in the server, rather than being specific to SQLJ.

When writing SQLJ code for use in the server, be aware of the following:

The SQLJ runtime packages are automatically available in the server.
There are no explicit database connections for an application that executes in the server. Instead there is an implicit connection to the server itself.
There are coding issues relating to the Oracle JDBC server-side driver, such as lack of auto-commit functionality.
In the server, the default output device is the current trace file.

You should also be aware of class naming and class resolution considerations. These are discussed in "About Class Loading, Compiling, and Name Resolution".

Database Connections in the Server

The concept of connecting to the server is different when your SQLJ code is running in the server itself. Note the following issues and characteristics when writing code for use in the server:

There is no explicit database connection in the server as there is from a client. By default there is an implicit channel to the database for the current user, which is employed for any Java program running in the server. You do not have to initialize this "connection"; it is automatically initialized for SQLJ programs. You do not have to register or specify a driver, create a connection object, specify a default connection context, or specify any connection objects for any of your #sql statements.

Any SQLJ connection context instance or JDBC connection instance in your program communicates to the database through the database session that is executing your Java code.
Any attempt to close a SQLJ connection context instance or JDBC connection in a Java program running in the server is ignored. No exception is thrown and there is no effect on communication to the database.
You cannot connect to a remote database from a Java program executing in the server.

Note:
In the server, setting the default connection context to null, as follows, will reinstall the default connection context (the implicit connection to the server):
DefaultContext.setDefaultContext(null);

Coding Issues Relating to the JDBC Server-Side Driver

Note the following characteristics of the Oracle JDBC server-side driver when writing code for use in the server:

Result sets issued by the server-side driver persist across calls, and their finalizers do not release their database cursors. Because of this, it is especially important to close all iterators to avoid running out of available cursors, unless you have a particular reason for keeping an iterator open (such as when it is actually used across calls).
The server-side driver does not support auto-commit functionality--the auto-commit setting is ignored in the server. Use explicit COMMIT or ROLLBACK statements to implement or cancel your database updates:
```
#sql { COMMIT };
...
#sql { ROLLBACK };
```

Note:
If you are using any kind of XA transactions, such as Java Transaction Service (JTS) transactions, you cannot use SQLJ or JDBC commit/rollback statements or methods. This applies particularly to Enterprise JavaBeans or CORBA objects, which typically use such transactions. See "Additional Vehicles for SQLJ in the Server".

For more information about server-side JDBC and the server-side driver, see the Oracle8i JDBC Developer's Guide and Reference.

Server-Side Default Output Device

The default standard output device in the Oracle8i Java VM is the current trace file.

If you want to reroute all standard output from a program executing in the server (output from any System.out.println() calls, for example) to a user screen, then you can execute the SET_OUTPUT() procedure of the DBMS_JAVA package, as follows (inputting the buffer size in bytes):

sqlplus> execute dbms_java.set_output(10000);

If you want your code executing in the server to expressly output to the user screen, you can also use the PL/SQL DBMS_OUTPUT.PUT_LINE() procedure instead of the Java System.out.println() method.

The PUT_LINE() procedure is overloaded, accepting either VARCHAR2, NUMBER, or DATE as input to specify what is printed.

For more information about the DBMS_OUTPUT package, see the Oracle8i Supplied Packages Reference.