Stored procedures are Java methods published to SQL and stored
in an Oracle database for general use. A stored procedure can accept arguments,
reference Java classes, and return Java result values when called from a client
application. Any output produced by the Java class during the execution is not
displayed in the user screen. This is because the default output device on the
database server is not the user screen but a trace file and hence System.out
and System.err print to the current trace files.
This how to illustrates where to look for the trace files as well as how to
redirect the output to the user screen when the Java Stored Procedure is executed
within a PL/SQL block.
Pre-requisites for running the example
You will need the following to run this example -
Oracle9i Database, which
can be downloaded from here .
Description
Often we use print statements in most of the java programs
for debugging purposes. These statements print the messages to the standard
output devise like the console. In the server the default output device is not
the user screen instead a trace file. As a result all the System.out and System.err
statements used in the Java Stored Procedure, print to the current trace file.
rset = stmt.executeQuery("SELECT ENAME FROM EMP"); System.out.println("Employee names in the EMP table ...");
while ( rset.next() ) { System.out.println(rset.getString("ENAME"));
}
You can find the output statements in the latest trace file <TRACEFILE_NAME>.trc,
located at <ORACLE_HOME>/admin/<SID>/udump
Where,
<ORA_HOME> : The folder where oracle database is installed
<SID>
: The SID used to connect to the database
<TRACEFILE_NAME> : The most recent trace file used by the database for
logging
Now if you want to To redirect output to the SQL*Plus text
buffer, call the procedure set_output() in package DBMS_JAVA, as follows:
SQL> SET SERVEROUTPUT ON
SQL> CALL dbms_java.set_output(2000);
The minimum (and default) buffer size is 2,000 bytes; the
maximum size is 1,000,000 bytes.
Output is printed when the stored procedure exits.
Running the Sample
The complete source code for this sample can be viewed online.
This section discusses the instruction to run the sample application
Step 1
Copy the Tracing.java to your working directory. Open this file in your
editor, change the following line to your database connection parametersin the getEmployee()
method , Connection conn =
DriverManager.getConnection("jdbc:oracle:thin:@<hostname>:<port>:<SID>",
"scott","tiger");
Step 2
Set CLASSPATH
environment variable to include Oracle9i JDBC Driver file: classes12.zip
or classes12.jar or ojdbc14.jar. Also ensure
that PATH system variable includes the bin
directory of JDK.
Step 3
Loading
Java classes to the database
Run the loadjava command at the command prompt
loadjava -u scott/tiger@<DBHOST>:<PORT>:<SID>
-verbose -resolve -thin <FILE_NAME>.java where,
<DBHOST> : Name of the machine
where the database is running
<PORT> :
The database port
<SID>
: The SID used to connect to the databse
<FILE_NAME> : The java stored procedure source file.
Bookmark
