3 Calling Java Methods in Oracle Database

This chapter provides an overview and examples of calling Java methods that reside in Oracle Database. It contains the following sections:

Invoking Java Methods

The type of the Java application determines how the client calls a Java method. The following sections discuss each of the Java application programming interfaces (APIs) available for calling a Java method:

Using PL/SQL Wrappers

You can run Java stored procedures in the same way as PL/SQL stored procedures. In Oracle Database, Java is usually invoked through PL/SQL interface.

To call a Java stored procedure, you must publish it through a call specification. The following example shows how to create, resolve, load, and publish a simple Java stored procedure that returns a string:

  1. Define a class, Hello, as follows:

    public class Hello
    {
      public static String world()
      {
        return "Hello world";
      }
    }
    

    Save the file as a Hello.java file.

  2. Compile the class on your client system using the standard Java compiler, as follows:

    javac Hello.java
    

    It is a good idea to specify the CLASSPATH on the command line with the javac command, especially when writing shell scripts or make files. The Java compiler produces a Java binary file, in this case, Hello.class.

    You must determine the location at which this Java code will run. If you run Hello.class on your client system, then it searches the CLASSPATH for all the supporting core classes that Hello.class needs for running. This search should result in locating the dependent classes in one of the following:

    • As individual files in one or more directories, where the directories are specified in the CLASSPATH

    • Within .jar or .zip files, where the directories containing these files are specified in the CLASSPATH

  3. Decide on the resolver for the Hello class.

    In this case, load Hello.class on the server, where it is stored in the database as a Java schema object. When you call the world() method, Oracle JVM locates the necessary supporting classes, such as String, using a resolver. In this case, Oracle JVM uses the default resolver. The default resolver looks for these classes, first in the current schema, and then in PUBLIC. All core class libraries, including the java.lang package, are found in PUBLIC. You may need to specify different resolvers. You can trace problems earlier, rather than at run time, by forcing resolution to occur when you use the loadjava tool.

  4. Load the class on the server using the loadjava tool. You must specify the user name and password. Run the loadjava tool as follows:

    loadjava -user scott Hello.class
    Password: password
    
  5. Publish the stored procedure through a call specification.

    To call a Java static method with a SQL call, you must publish the method with a call specification. A call specification defines the arguments that the method takes and the SQL types that it returns.

    In SQL*Plus, connect to the database and define a top-level call specification for Hello.world() as follows:

    SQL> CONNECT scott
    Enter password: password
    connected
    SQL> CREATE OR REPLACE FUNCTION helloworld RETURN VARCHAR2 AS
      2  LANGUAGE JAVA NAME 'Hello.world () return java.lang.String';
      3  /
    Function created.
    
  6. Call the stored procedure, as follows:

    SQL> VARIABLE myString VARCHAR2(20);
    SQL> CALL helloworld() INTO :myString;
    Call completed.
    SQL> PRINT myString;
    
    MYSTRING
    ---------------------------------------
    Hello world
    
    SQL>
    

    The call helloworld() into :myString statement performs a top-level call in Oracle Database. SQL and PL/SQL see no difference between a stored procedure that is written in Java, PL/SQL, or any other language. The call specification provides a means to tie inter-language calls together in a consistent manner. Call specifications are necessary only for entry points that are called with triggers or SQL and PL/SQL calls. Furthermore, JDeveloper can automate the task of writing call specifications.

JNI Support

The Java Native Interface (JNI) is a standard programming interface for writing Java native methods and embedding the JVM into native applications. The primary goal of JNI is to provide binary compatibility of Java applications that use platform-specific native libraries.

Oracle Database does not support the use of JNI in Java applications. If you use JNI, then your application is not 100 percent pure Java and the native methods require porting between platforms. Native methods can cause server failure, violate security, and corrupt data.

Utilizing SQLJ and JDBC with Java in the Database

You can use SQLJ and Java Database Connectivity (JDBC) APIs from a Java client. Both APIs establish a session with a given user name and password on the database and run SQL queries against the database. The following table lists the APIs and their description:

API Description
JDBC Use this API for more complex or dynamic SQL queries. JDBC requires you to establish the session, construct the query, and so on.
SQLJ Use this API for easy SQL queries, both static and dynamic. SQLJ typically runs against a known table with known column names.

This section covers the following topics:

JDBC

JDBC is an industry-standard API developed by Sun Microsystems that lets you embed SQL statements as Java method arguments. JDBC is based on the X/Open SQL Call Level Interface (CLI) and complies with the Entry Level of SQL-92 standard. Each vendor, such as Oracle, creates its JDBC implementation by implementing the interfaces of the standard java.sql package. Oracle provides the following JDBC drivers that implement these standard interfaces:

  • The JDBC Thin driver, a 100 percent pure Java solution that you can use for either client-side applications or applets and requires no Oracle client installation.

  • The JDBC OCI driver, which you use for client-side applications and requires an Oracle client installation.

  • The server-side JDBC driver embedded in Oracle Database.

Using JDBC is a step-by-step process of performing the following tasks:

  1. Obtaining a connection handle

  2. Creating a statement object of some type for your desired SQL operation

  3. Assigning any local variables that you want to bind to the SQL operation

  4. Carrying out the operation

  5. Optionally retrieving the result sets

This process is sufficient for many applications, but becomes cumbersome for any complicated statements. Dynamic SQL operations, where the operations are not known until run time, require JDBC. However, in typical applications, this represents a minority of the SQL operations.

SQLJ

SQLJ offers an industry-standard way to embed any static SQL operation directly into the Java source code in one simple step, without requiring the multiple steps of JDBC. Oracle SQLJ complies with the X3H2-98-320 American National Standards Institute (ANSI) standard.

SQLJ consists of a translator, which is a precompiler that supports standard SQLJ programming syntax, and a run-time component. After creating your SQLJ source code in a .sqlj file, you process it with the translator. The translator translates the SQLJ source code to standard Java source code, with SQL operations converted to calls to the SQLJ run time. In Oracle Database SQLJ implementation, the translator calls a Java compiler to compile the Java source code. When your SQLJ application runs, the SQLJ run time calls JDBC to communicate with the database.

SQLJ also enables you to catch errors in your SQL statements before run time. JDBC code, being pure Java, is compiled directly. The compiler cannot detect SQL errors. On the other hand, when you translate SQLJ code, the translator analyzes the embedded SQL statements semantically and syntactically, catching SQL errors during development, instead of allowing an end user to catch them when running the application.

Example Comparing JDBC and SQLJ

The following is an example of a JDBC code and a SQLJ code that perform a simple operation:

JDBC:

// Assume you already have a JDBC Connection object conn
// Define Java variables
String name;
int id=37115;
float salary=20000;

// Set up JDBC prepared statement.
PreparedStatement pstmt = conn.prepareStatement
("SELECT ename FROM emp WHERE empno=? AND sal>?");
pstmt.setInt(1, id);
pstmt.setFloat(2, salary);

// Execute query; retrieve name and assign it to Java variable.
ResultSet rs = pstmt.executeQuery();
while (rs.next())
{
  name=rs.getString(1);
  System.out.println("Name is: " + name);
}

// Close result set and statement objects.
rs.close()
pstmt.close();

Assume that you have established a JDBC connection, conn. Next, you must do the following:

  1. Define the Java variables, name, id, and salary.

  2. Create a PreparedStatement instance.

    You can use a prepared statement whenever values in the SQL statement must be dynamically set. You can use the same prepared statement repeatedly with different variable values. The question marks (?) in the prepared statement are placeholders for Java variables. In the preceding example, these variables are assigned values using the pstmt.setInt() and pstmt.setFloat() methods. The first ? refers to the int variable id and is set to a value of 37115. The second ? refers to the float variable salary and is set to a value of 20000.

  3. Run the query and return the data into a ResultSet object.

  4. Retrieve the data of interest from the ResultSet object and display it. In this case, the ename column. A result set usually contains multiple rows of data, although this example has only one row.

SQLJ:

String name;
int id=37115;
float salary=20000;
#sql {SELECT ename INTO :name FROM emp WHERE empno=:id AND sal>:salary};
System.out.println("Name is: " + name);

In addition to allowing SQL statements to be directly embedded in Java code, SQLJ supports Java host expressions, also known as bind expressions, to be used directly in the SQL statements. In the simplest case, a host expression is a simple variable, as in this example. However, more complex expressions are allowed as well. Each host expression is preceded by colon (:). This example uses Java host expressions, name, id, and salary. In SQLJ, because of its host expression support, you do not need a result set or equivalent when you are returning only a single row of data.

Note:

All SQLJ statements, including declarations, start with the #sql token.

Complete SQLJ Example

This section presents a complete example of a simple SQLJ program:

import java.sql.*;
import sqlj.runtime.ref.DefaultContext;
import oracle.sqlj.runtime.Oracle;

#sql ITERATOR MyIter (String ename, int empno, float sal);

public class MyExample
{
  public static void main (String args[]) throws SQLException
  {
    Oracle.connect("jdbc:oracle:thin:@oow11:5521:sol2", "scott", "tiger");
    #sql { INSERT INTO emp (ename, empno, sal) VALUES ('SALMAN', 32, 20000) };
    MyIter iter;
    #sql iter={ SELECT ename, empno, sal FROM emp };
    while (iter.next())
    {
      System.out.println(iter.ename()+" "+iter.empno()+" "+iter.sal());
    }
  }
}

In the preceding example, you do the following:

  1. Declare your iterators.

    SQLJ uses a strongly-typed version of JDBC result sets, known as iterators. An iterator has a specific number of columns of specific data types. You must define your iterator types before using them, as in this example.

    #sql ITERATOR MyIter (String ename, int empno, float sal);
    

    This declaration results in SQLJ creating an iterator class, MyIter. Iterators of type MyIter can store results whose first column maps to a Java String, second column maps to a Java int, and third column maps to a Java float. This definition also names the three columns as ename, empno, and sal, to match the column names of the referenced table in the database. MyIter is a named iterator.

  2. Connect to the database.

    Oracle.connect("jdbc:oracle:thin:@oow11:5521:sol2","scott", "tiger");
    

    SQLJ provides the Oracle class and its connect() method accomplishes the following important tasks:

    1. Registers Oracle JDBC drivers that SQLJ uses to access the database, in this case, the JDBC Thin driver.

    2. Opens a database connection for the specified schema, in this case, user scott with password tiger, at the specified URL. In this case, the URL points to host oow11, port 5521, and SID so12.

    3. Establishes this connection as the default connection for the SQLJ statements. Although each JDBC statement must explicitly specify a connection object, a SQLJ statement can either implicitly use a default connection or optionally specify a different connection.

  3. Process a SQL statement. The following is accomplished:

    1. Insert a row into the emp table:

      #sql {INSERT INTO emp (ename, empno, sal) VALUES ('SALMAN', 32, 20000)};
      
    2. Instantiate and populate the iterator:

      MyIter iter;
      #sql iter={SELECT ename, empno, sal FROM emp};
      
  4. Access the data that was populated within the iterator.

    while (iter.next())
    {
      System.out.println(iter.ename()+" "+iter.empno()+" "+iter.sal());
    }
    

    The next() method is common to all iterators and plays the same role as the next() method of a JDBC result set, returning true and moving to the next row of data, if any rows remain. You can access the data in each row by calling iterator accessor methods whose names match the column names. This is a characteristic of all named iterators. In this example, you access the data using the methods ename(), empno(), and sal().

SQLJ Strong Typing Paradigm

SQLJ uses strong typing, such as iterators, instead of result sets. This enables the SQL instructions to be checked against the database during translation. For example, SQLJ can connect to a database and check your iterators against the database tables that will be queried. The translator will verify that they match, enabling you to catch SQL errors during translation that would otherwise not be caught until a user runs your application. Furthermore, if changes are subsequently made to the schema, then you can determine if these changes affect the application by rerunning the translator.

Translating a SQLJ Program

Integrated development environments (IDEs), such as Oracle JDeveloper, can translate, compile, and customize your SQLJ program as you build it. Oracle JDeveloper is a Microsoft Windows-based visual development environment for Java programming. If you are not using an IDE, then use the front-end SQLJ utility, sqlj. You can run it as follows:

%sqlj MyExample.sqlj

The SQLJ translator checks the syntax and semantics of your SQL operations. You can enable online checking to check your operations against the database. If you choose to do this, then you must specify an example database schema in your translator option settings. It is not necessary for the schema to have data identical to the one that the program will eventually run against. However, the tables must have columns with corresponding names and data types. Use the user option to enable online checking and specify the user name, password, and URL of your schema, as in the following example:

%sqlj -user=scott@jdbc:oracle:thin:@oow11:5521:sol2 MyExample.sqlj
Password: password

Running a SQLJ Program in the Server

Many SQLJ applications run on a client. However, SQLJ offers an advantage in programming stored procedures, which are usually SQL-intensive, to run on the server.

There is almost no difference between writing a client-side SQLJ program and a server-side SQLJ program. The SQLJ run-time packages are automatically available on the server. However, you must consider the following:

  • There are no explicit database connections for code running on the server. There is only a single implicit connection. You do not need the usual connection code. If you are porting an existing client-side application, then you do not have to remove the connection code, because it will be ignored.

  • The JDBC server-side internal driver does not support auto-commit functionality. Use SQLJ syntax for manual commits and rollbacks of your transactions.

  • On the server, the default output device is a trace file, not the user screen. This is, typically, an issue only for development, because you would not write to System.out in a deployed server application.

To run a SQLJ program on the server, presuming you developed the code on a client, you have two options:

  • Translate your SQLJ source code on the client and load the individual components, such as the Java classes and resources, on the server. In this case, it is easy to bundle them into a .jar file first and then load them on the server.

  • Load your SQLJ source code on the server for the embedded translator to translate.

In either case, use the loadjava tool to load the file or files to the server.

Converting a Client Application to Run on the Server

To convert an existing SQLJ client-side application to run on the server, after the application has already been translated on the client, perform the following steps:

  1. Create a .jar file for your application components.

  2. Use the loadjava tool to load the .jar file on the server.

  3. Create a SQL wrapper in the server for your application. For example, to run the preceding MyExample application on the server, run the following statement:

    CREATE OR REPLACE PROCEDURE sqlj_myexample AS LANGUAGE JAVA NAME `MyExample.main(java.lang.String[])';
    

You can then run sqlj_myexample, similar to any other stored procedure.

Interacting with PL/SQL

All Oracle JDBC drivers communicate seamlessly with Oracle SQL and PL/SQL, and it is important to note that SQLJ interoperates with PL/SQL. You can start using SQLJ without having to rewrite any PL/SQL stored procedures. Oracle SQLJ includes syntax for calling PL/SQL stored procedures and also lets you embed anonymous PL/SQL blocks in SQLJ statements.

Using Command-Line Interface

The command-line interface to Oracle JVM is analogous to using the JDK or JRE shell commands. You can:

  • Use the standard -classpath syntax to indicate where to find the classes to load

  • Set the system properties by using the standard -D syntax

The interface is a PL/SQL function that takes a string (VARCHAR2) argument, parses it as a command-line input and if it is properly formed, runs the indicated Java method in Oracle JVM. To do this, PL/SQL package DBMS_JAVA provides the following functions:

runjava

This function takes the Java command line as its only argument and runs it in Oracle JVM. The return value is null on successful completion, otherwise an error message. The format of the command line is the same as that taken by the JDK shell command, that is:

[option switches] name_of_class_to_execute [arg1 arg2 ... argn]

You can use the option switches -classpath, -D, -Xbootclasspath, and -jar. This function differs from the runjava_in_current_session function in that it clears any Java state remaining from previous use of Java in the session, prior to running the current command. This is necessary, in particular, to guarantee that static variable values derived at class initialization time from -classpath and -D arguments reflect the values of those switches in the current command line.

FUNCTION runjava(cmdline VARCHAR2) RETURN VARCHAR2;

runjava_in_current_session

This function is the same as the runjava function, except that it does not clear Java state remaining from previous use of Java in the session, prior to executing the current command line.

FUNCTION runjava_in_current_session(cmdline VARCHAR2) RETURN VARCHAR2;

Syntax

The syntax of the command line is of the following form:

[-options] classname [arguments...]
[-options] -jar jarfile [arguments...]

Options

-classpath
-D
-Xbootclasspath
-Xbootclasspath/a
-Xbootclasspath/p
-cp

Note:

The effect of the first form is to run the main method of the class identified by classname with the arguments. The effect of the second form is to run the main method of the class identified by the Main-Class attribute in the manifest of the JAR file identified by JAR. This is analogous to how the JDK/JRE interprets this syntax.

Argument Summary

Table 3-1 summarizes the command-line arguments.

Table 3-1 Command Line Argument Summary

Argument Description

classpath

Accepts a colon (:) separated list of directories, JAR archives, and ZIP archives to search for class files. In general, the value of -classpath or similar arguments refer to file system locations as they would in a standard Java runtime. You also have an extension to this syntax to allow for terms that refer to database resident Java objects and sets of bytes. Refer to Appendix B, "Classpath Extensions and User Classloaded Metadata" for more information about -classpath extensions.

D

Establishes values for system properties when there is no existing Java session state. The default behavior of the command-line interface, that is, the runjava function, is to terminate any existing Java session prior to running the new command. On the other hand, the alternative function, runjava_in_current_session leaves any existing session in place. So, values established with the -D option always take effect when runjava function is used, but the values may not take effect when runjava_in_current_session function is used.

 
 

Xbootclasspath

Accepts a colon (:) separated list of directories, JAR archives, and ZIP archives. This option is used to set search path for bootstrap classes and resources.

Xbootclasspath/a

Accepts a colon (:) separated list of directories, JAR archives, and ZIP archives. This is appended to the end of bootstrap class path.

Xbootclasspath/p

Accepts a colon (:) separated list of directories, JAR archives, and ZIP archives. This is added in front of bootstrap class path.

cp

Acts as a synonym of -classpath.


Note:

System classes created by create java system are always used before using any file or folder that are found using the -Xbootclasspath option.

Using the Client-Side Stub

Oracle Database 10g introduced the client-side stub, formerly known as native Java interface, for calls to server-side Java code. It is a simplified application integration. Client-side and middle-tier Java applications can directly call Java in the database without defining a PL/SQL wrapper. The client-side stub uses the server-side Java class reflection capability.

In previous releases, calling Java stored procedures and functions from a database client required Java Database Connectivity (JDBC) calls to the associated PL/SQL wrappers. Each wrapper had to be manually published with a SQL signature and a Java implementation. This had the following disadvantages:

  • The signatures permitted only Java types that had direct SQL equivalents

  • Exceptions issued in Java were not properly returned

The JPublisher -java option provides functionality to overcome these disadvantages. To remedy the deficiencies of JDBC calls to associated PL/SQL wrappers, the -java option uses an API for direct invocation of static Java methods. This functionality is also useful for Web services.

The functionality of the -java option mirrors that of the -sql option, creating a client-side Java stub class to access a server-side Java class, as opposed to creating a client-side Java class to access a server-side SQL object or PL/SQL package. The client-side stub class uses JPublisher code that mirrors the server-side class and includes the following features:

  • Methods corresponding to the public static methods of the server class

  • Two constructors, one that takes a JDBC connection and one that takes the JPublisher default connection context instance

At run time, the stub class is instantiated with a JDBC connection. Calls to the methods of the stub class result in calls to the corresponding methods of the server-side class. Any Java types used in these published methods must be primitive or serializable.

Figure 3-1 demonstrates a client-side stub API for direct invocation of static server-side Java methods. JPublisher transparently takes care of stub generation.

Figure 3-1 Client-Side Stub Interface

Description of Figure 3-1 follows
Description of "Figure 3-1 Client-Side Stub Interface"

You can use the -java option to publish a server-side Java class, as follows:

-java=className

Consider the oracle.sqlj.checker.JdbcVersion server-side Java class, with the following APIs:

public class oracle.sqlj.checker.JdbcVersion
{
 ...
 public java.lang.String toString();
 public static java.lang.String to_string();
 ...
}

As an example, assume that you want to call the following method on the server:

public String oracle.sqlj.checker.JdbcVersion.to_string();

Use the following command to publish JdbcVersion for client-side invocation, using JPublisher:

% jpub -sql=scott -java=oracle.sqlj.checker.JdbcVersion:JdbcVersion Client
Enter scott password: password

This command generates the client-side Java class, JdbcVersionClient, which contains the following APIs:

public class JdbcVersionClient
{
 ...
 public java.lang.String toString(long _handle);
 public java.lang.String to_string();
 ...
}

All static methods are mapped to instance methods in the client-side code. A instance method in the server-side class, toString() for example, is mapped to a method with an extra handle. A handle represents an instance of oracle.sqlj.checker.JdbcVersion in the server. The handle is used to call the instance method on the server-side.

Using the Default Service Feature

Starting from Oracle Database 11g release 1 (11.1), Oracle Database client provides a new default connection feature. If you install Oracle Database client, then you need not specify all the details of the database server in the connection URL. Under certain conditions, Oracle Database connection adapter requires only the host name of the computer where the database is installed.

For example, in the JDBC connection URL syntax, that is:

jdbc:oracle:driver_type:[username/password]@[//]host_name[:port][:ORCL]

,the following have become optional:

  • // is optional.

  • :port is optional.

    You must specify a port only if the default Oracle Net listener port (1521) is not used.

  • :ORCL or the service name is optional.

    The connection adapter for Oracle Database Client connects to the default service on the host. On the host, this is set to ORCL in the listener.ora file.

Testing the Default Service with a Basic Configuration

The following code snippet shows a basic configuration of the listener.ora file, where the default service is defined.

Example 3-1 Basic Configuration of listener.ora with the Default Service Defined

MYLISTENER = (ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=testserver1)(PORT=1521)))
DEFAULT_SERVICE_MYLISTENER=dbjf.app.myserver.com
SID_LIST_MYLISTENER = (SID_LIST=(SID_DESC=(SID_NAME=dbjf)
(GLOBAL_DBNAME=dbjf.app.myserver.com)(ORACLE_HOME=/test/oracle))
)

After defining the listener.ora file, restart the listener with the following command:

lsnrctl start mylistener

Now, any of the following URLs should work with this configuration of the listener.ora file:

  • jdbc:oracle:thin:@//testserver1.myserver.com.com

  • jdbc:oracle:thin:@//testserver1.myserver.com:1521

  • jdbc:oracle:thin:@testserver1.myserver.com

  • jdbc:oracle:thin:@testserver1.myserver.com:1521

  • jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=testserver1.myserver.com)(PORT=1521)))

  • jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=testserver1.myserver.com)))

  • jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=testserver1.myserver.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=)))

Note:

Default service is a feature since Oracle Database 11g Release 1 (11.1). If you use any version prior to Oracle Database 11g Client to connect to the database, then you must specify the SID.

Debugging Server Applications

Oracle Database furnishes a debugging capability that is useful for developers who use the jdb debugger. The interface that is provided is the Java Debug Wire Protocol (JDWP), which is supported by Java Development Kit (JDK) 1.4 and later versions.

Some of the new features that the JDWP protocol supports are:

  • Listening for connections

  • Changing the values of variables while debugging

  • Evaluating arbitrary Java expressions, including method evaluation

Oracle JDeveloper provides a user-friendly integration with these debugging features. Other independent IDE vendors will be able to integrate their own debuggers with Oracle Database.

How To Tell You Are Running on the Server

You may want to write Java code that runs in a certain way on the server and in another way on the client. In general, Oracle does not recommend this. In fact, JDBC and SQLJ enable you to write portable code that avoids this problem, even though the drivers used in the server and client are different.

If you want to determine whether your code is running on the server, then use the System.getProperty() method, as follows:

System.getProperty ("oracle.jserver.version")

The getProperty() method returns the following:

  • A String that represents Oracle Database release, if running on the server

  • null, if running on the client

Redirecting Output on the Server

You can pass Java output to SQL statements to provide more extensive control over the destination of output from Oracle JVM. The PL/SQL package DBMS_JAVA has been enhanced by adding the following new functions, which provide extended functionality to what was previously available only with the DBMS_JAVA.SET_OUTPUT procedure:

set_output_to_sql

set_output_to_sql defines a named output specification that constitutes an instruction for executing a SQL statement, whenever output to the default System.out and System.err streams occurs. The specification is defined either for the duration of the current session, or till the remove_output_to_sql function is called with its ID. The SQL actions prescribed by the specification occur whenever there is Java output. This can be stopped and started by calling the disable_output_to_sql and enable_output_to_sql functions respectively. The return value of this function is null on success, otherwise an error message.

FUNCTION set_output_to_sql (id VARCHAR2,
stmt VARCHAR2,
bindings VARCHAR2,
no_newline_stmt VARCHAR2 default null,
no_newline_bindings VARCHAR2 default null,
newline_only_stmt VARCHAR2 default null,
newline_only_bindings VARCHAR2 default null,
maximum_line_segment_length NUMBER default 0,
allow_replace NUMBER default 1,
from_stdout NUMBER default 1,
from_stderr NUMBER default 1,
include_newlines NUMBER default 0,
eager NUMBER default 0) return VARCHAR2;

Table 3-2 describes the arguments the set_output_to_sql function takes.

Table 3-2 set_output_to_sql Argument Summary

Argument Description

id

The name of the specification. Multiple specifications may exist in the same session, but each must have a distinct ID. The ID is used to identify the specification in the functions remove_output_to_sql, enable_output_to_sql, disable_output_to_sql, and query_output_to_sql.

stmt

The default SQL statement to execute when Java output occurs.

 
 

bindings

A string containing tokens from the set ID, TEXT, LENGTH, LINENO, SEGNO, NL, and ERROUT. This string defines how the SQL statement stmt is bound. The position of a token in the bindings string corresponds to the bind position in the SQL statement. The meanings of the tokens are:

  • ID is the ID of the specification. It is bound as a VARCHAR2.

  • TEXT is the text being output. It is bound as a VARCHAR2.

  • LENGTH is the length of the text. It is bound as a NUMBER.

  • LINENO is the line number since the beginning of session output. It is bound as a NUMBER.

  • SEGNO is the segment number within a line that is being output in more than one piece. It is bound as a NUMBER.

  • NL is a boolean indicating whether the text is to be regarded as newline terminated. It is bound as a NUMBER. The newline may or may not actually be included in the text, depending on the value of the include_newlines argument.

  • ERROUT is a boolean indicating whether the output came from System.out or System.err. It is bound as a NUMBER. The value is 0, if the output came from System.out.

no_newline_stmt

An optional alternate SQL statement to execute, when the output is not newline terminated.

no_newline_bindings

A string with the same syntax as for the bindings argument discussed previously, describing how the no_newline_stmt is bound.

newline_only_stmt

An optional alternate SQL statement to execute when the output is a single newline.

newline_only_bindings

A string with the same syntax as for the bindings argument discussed previously, describing how the newline_only_stmt is bound.

maximum_line_segment_length

The maximum number of characters that is bound in a given execution of the SQL statement. Longer output sequences are broken up into separate calls with distinct SEGNO values. A value of 0 means no maximum.

allow_replace

Controls behavior when a previously defined specification with the same ID exists. A value of 1 means replacing the old specification. 0 means returning an error message without modifying the old specification.

from_stdout

Controls whether output from System.out causes execution of the SQL statement prescribed by the specification. A value of 0 means that if the output came from System.out, then the statement is not executed, even if the specification is otherwise enabled.

from_stderr

Controls whether output from System.err causes execution of the SQL statement prescribed by the specification. A value of 0 means that if the output came from System.err, then the statement is not executed, even if the specification is otherwise enabled.

include_newlines

Controls whether newline characters are left in the output when they are bound to text. A value of 0 means new lines are not included. But the presence of the newline is still indicated by the NL binding and the use of no_newline_stmt.

eager

Controls whether output not terminated by a newline causes execution of the SQL statement every time it is received, or accumulates such output until a newline is received. A value of 0 means that unterminated output is accumulated.


remove_output_to_sql

remove_output_to_sql deletes a specification created by set_output_to_sql. If no such specification exists, an error message is returned.

FUNCTION remove_output_to_sql (id VARCHAR2) return VARCHAR2;

enable_output_to_sql

enable_output_to_sql reenables a specification created by set_output_to_sql and subsequently disabled by disable_output_to_sql. If no such specification exists, an error message is returned. If the specification is not currently disabled, there is no change.

FUNCTION enable_output_to_sql (id VARCHAR2) return VARCHAR2;

disable_output_to_sql

disable_output_to_sql disables a specification created by set_output_to_sql. You can enable the specification by calling enable_output_to_sql. While disabled, the SQL statement prescribed by the specification is not executed. If no such specification exists, an error message is returned. If the specification is already disabled, there is no change.

FUNCTION disable_output_to_sql (id VARCHAR2) return VARCHAR2;

query_output_to_sql

query_output_to_sql returns a message describing a specification created by set_output_to_sql. If no such specification exists, then an error message is returned. Passing null to this function causes all existing specifications to be displayed.

FUNCTION query_output_to_sql (id VARCHAR2) return VARCHAR2;

Another way of achieving control over the destination of output from Oracle JVM is to pass your Java output to an autonomous Java session. This provides a very general mechanism for propagating the output to various kinds of targets, such as disk files, sockets, and URLS. But, you must keep in mind that the Java session that processes the output is logically distinct from the main session, so that there are no other, unwanted interactions between them. To do this, PL/SQL package DBMS_JAVA provides the following functions:

set_output_to_java

set_output_to_java defines a named output specification that gives an instruction for executing a Java method whenever output to the default System.out and System.err streams occurs. The Java method prescribed by the specification is executed in a separate VM context with separate Java session state from the rest of the session.

FUNCTION set_output_to_java (id VARCHAR2,
class_name VARCHAR2,
class_schema VARCHAR2,
method VARCHAR2,
bindings VARCHAR2,
no_newline_method VARCHAR2 default null,
no_newline_bindings VARCHAR2 default null,
newline_only_method VARCHAR2 default null,
newline_only_bindings VARCHAR2 default null,
maximum_line_segment_length NUMBER default 0,
allow_replace NUMBER default 1,
from_stdout NUMBER default 1,
from_stderr NUMBER default 1,
include_newlines NUMBER default 0,
eager NUMBER default 0,
initialization_statement VARCHAR2 default null,
finalization_statement VARCHAR2 default null)return VARCHAR2;

Table 3-3 describes the arguments the set_output_to_java method takes.

Table 3-3 set_output_to_java Argument Summary

Argument Description

class_name

The name of the class defining one or more methods.

class_schema

The schema in which the class is defined. A null value means the class is defined in the current schema, or PUBLIC.

 
 

method

The name of the method.

bindings

A string that defines how the arguments to the method are bound. This is a string of tokens with the same syntax as set_output_to_sql. The position of a token in the string determines the position of the argument it describes. All arguments must be of type INT, except for those corresponding to the tokens ID or TEXT, which must be of type java.lang.String.

no_newline_method

An optional alternate method to execute when the output is not newline terminated.

newline_only_method

An optional alternate method to execute when the output is a single newline.

initialization_statement

An optional SQL statement that is executed once per Java session prior to the first time the methods that receive output are executed. This statement is executed in same Java VM context as the output methods are executed. Typically such a statement is used to run a Java stored procedure that initializes conditions in the separate VM context so that the methods that receive output can function as intended. For example, such a procedure might open a stream that the output methods write to.

finalization_statement

An optional SQL statement that is executed once when the output specification is about to be removed or the session is ending. Like the initialization_statement, this runs in the same JVM context as the methods that receive output. It runs only if the initialization method has run, or if there is no initialization method.


remove_output_to_java

remove_output_to_java deletes a specification created by set_output_to_java. If no such specification exists, an error message is returned

FUNCTION remove_output_to_java (id VARCHAR2) return VARCHAR2;

enable_output_to_java

enable_output_to_java reenables a specification created by set_output_to_java and subsequently disabled by disable_output_to_java. If no such specification exists, an error message is returned. If the specification is not currently disabled, there is no change.

FUNCTION enable_output_to_java (id VARCHAR2) return VARCHAR2;

disable_output_to_java

disable_output_to_java disables a specification created by set_output_to_java. The specification may be reenabled by enable_output_to_java. While disabled, the SQL statement prescribed by the specification is not executed. If no such specification exists, an error message is returned. If the specification is already disabled, there is no change.

FUNCTION disable_output_to_java (id VARCHAR2) return VARCHAR2;

query_output_to_java

query_output_to_java returns a message describing a specification created by set_output_to_java. If no such specification exists, an error message is returned. Passing null to this function causes all existing specifications to be displayed.

FUNCTION query_output_to_java (id VARCHAR2) return VARCHAR2;

set_output_to_file

set_output_to_file defines a named output specification that constitutes an instruction to capture any output sent to the default System.out and System.err streams and append it to a specified file. This is implemented using a special case of set_output_to_java. The argument file_path specifies the path to the file to which to append the output. The arguments allow_replace, from_stdout, and from_stderr are all analogous to the arguments having the same name as in set_output_to_sql.

FUNCTION set_output_to_file (id VARCHAR2,
file_path VARCHAR2,
allow_replace NUMBER default 1,
from_stdout NUMBER default 1,
from_stderr NUMBER default 1) return VARCHAR2;

remove_output_to_file

This function is analogous to remove_output_to_java.

FUNCTION remove_output_to_file (id VARCHAR2) return VARCHAR2;

enable_output_to_file

This function is analogous to enable_output_to_java.

FUNCTION enable_output_to_file (id VARCHAR2) return VARCHAR2;

disable_output_to_file

This function is analogous to disable_output_to_java.

FUNCTION disable_output_to_file (id VARCHAR2) return VARCHAR2;

query_output_to_file

This function is analogous to query_output_to_java.

FUNCTION query_output_to_file (id VARCHAR2) return VARCHAR2;

The following DBMS_JAVA functions control whether Java output appears in the .trc file:

  • PROCEDURE enable_output_to_trc;

  • PROCEDURE disable_output_to_trc;

  • FUNCTION query_output_to_trc return VARCHAR2;

Note:

Prior to 11g release 1 (11.1), the fact that Java output appeared in the .trc file was not modifiable.

Redirecting the output to SQL*Plus Text Buffer

As in previous releases, you can use the DBMS_JAVA package procedure SET_OUTPUT to redirect output to the SQL*Plus text buffer:

SQL> SET SERVEROUTPUT ON
SQL> CALL dbms_java.set_output(2000);

The minimum and default buffer size is 2,000 bytes and the maximum size is 1,000,000 bytes. In the following example, the buffer size is increased to 5,000 bytes:

SQL> SET SERVEROUTPUT ON SIZE 5000
SQL> CALL dbms_java.set_output(5000);

The output is displayed at the end of the call.

Calling Java in the Database Directly

In releases prior to Oracle Database 10g, calling Java stored procedures and functions from a database client required JDBC calls to the associated PL/SQL wrappers. Each wrapper had to be manually published with a SQL signature and a Java implementation. This had the following disadvantages:

  • A separate step was required for publishing the SQL signatures for Java methods.

  • The signatures permitted only Java types with SQL equivalents.

  • Exceptions issued in Java were not properly returned.

  • Only a single method invocation could be performed for each database round trip.

To remedy these deficiencies, a simple API has been implemented since Oracle Database 11g to directly call static Java stored procedures and functions. This new functionality is useful for general applications, but is particularly useful for Web services.

Classes for the simple API are located in the oracle.jpub.reflect package. Import this package into the client-side code.

The following is the Java interface for the API:

public class Client
{
  public static String getSignature(Class[]);
  public static Object invoke(Connection, String, String, String, Object[]);
  public static Object invoke(Connection, String, String, Class[], Object[]);
}

As an example, consider a call to the following method in the server:

public String oracle.sqlj.checker.JdbcVersion.to_string();

You can call the method, as follows:

Connection conn = ...;
String serverSqljVersion = (String)
Client.invoke(conn, "oracle.sqlj.checker.JdbcVersion","to_string", new Class[]{}, new Object[]{});

The Class[] array is for the method parameter types and the Object[] array is for the parameter values. In this case, because to_string has no parameters, the arrays are empty.

Note the following:

  • Any serializable type, such as int[] and String[], can be passed as an argument.

  • As an optimization, parameter values can be represented as String:

    String sig = oracle.jpub.reflect.Client.getSignature(new Class[]{});
    ...
    Client.invoke(conn, "oracle.sqlj.checker.JdbcVersion", "to_string",sig, new Object[]{});
    
  • The semantics of this API are different from the semantics for calling stored procedures or functions through a PL/SQL wrapper, in the following ways:

    • Arguments cannot be OUT or IN OUT. Returned values must all be part of the function result.

    • Exceptions are properly returned.

    • The method invocation uses invoker's rights. There is no tuning to obtain the definer's rights.