Question: We are
looking at Oracle SQLJ, and we
needed to examine SQLJ. Can you please
explain the functionality of Oracle SQLJ
Answer:
The SQLJ Utility (sqlj.exe) is for Java developers seeking alternatives to
JDBC for Oracle database access. This utility is roughly equivalent to the
Pro*C and Pro*COBOL precompilers. Given that entire books are written on
SQLJ, this book will detail the use of the SQLJ executable. This example
requires a working version of the Java SDK, either version 1.3 or 1.4 on
the client machine and the SQLJ Oracle installation.
SQLJ is an industry standard that provides an easier way for Java
programmers to access databases via SQL. Without SQLJ, they are limited to
JDBC result set processing, an error prone and time-consuming process.
SQLJ enables developers to insert SQL statements directly into the Java
code indicated by the #sql syntax:
#sql users = { select username from v$session };
The sqlj.exe file is used to convert SQLJ syntax to a Java source file and
optionally, a class file that can be executed. And, after viewing the
messy generated Java source file, it is easy to see the value of SQLJ.
This is not a Java source that someone would want to maintain.
The following .sqlj file (Util_Sessions.sqlj) will be used as an example.
This SQLJ source simply queries the database for the names of the users
connected and displays them.
At the operating system level, the Java environment must be configured.
The following DOS commands ensure that JAVA_HOME is set and also appends
the three zip files below to the CLASSPATH. These zip files are needed for
the SQLJ executable to perform.
C:\oracle9i\bin>echo %JAVA_HOME%
c:\j2sdk1.4.0_01
C:\oracle9i\bin>SET CLASSPATH=%CLASSPATH%;%ORACLE_HOME%\sqlj\lib\translator.zip
C:\oracle9i\bin>SET CLASSPATH=%CLASSPATH%;%ORACLE_HOME%\sqlj\lib\runtime12.zip
C:\oracle9i\bin>SET CLASSPATH=%CLASSPATH%;%ORACLE_HOME%\jdbc\lib\classes12.zip
C:\oracle9i\bin>sqlj -compile=true -user=scott/tiger -status -ser2class
-warn CurrentSessions.sqlj
[Translating]
[Reading file CurrentSessions]
[Translating file CurrentSessions]
[Compiling]
Here is the resulting Java file (CurrentSessions.java) as generated by the
sqlj.exe. The SQLJ executable created a corresponding Java file that will
be compiled and executed as part of our program. The SQLJ file is used
only for the purpose of generating the Java file.
Notice that the SQLJ process inserted some special syntax into the
generated Java file. Developers should only maintain the sqlj file,
however, and not be concerned with editing the sqlj generated Java file.
Once CurrentSessions.java is compiled into a class file, it can be
executed. Notice from the output below that three users are connected to
the database. This Java program was executed by simply invoking the entire
class name at the operating system command line.
C:\oracle9i\bin>java
com.rampant.sqljexample.CurrentSessions
SYSTEM
SYS
SCOTT
That is a very simple example, but in a few lines of code, a Java program
was created that accessed the database and returned a list of connected
users.
SQLJ provides the Java developer with an extensive list of command line
options. These enable the Java developer to fully control the SQLJ
development environment. These options can be viewed by typing sqlj at the
command line with no additional parameters.
The advantages of SQLJ include:
• The automatic validation of SQL code. With JDBC, SQL and DML statements
are only validated at runtime and not compile time, since the SQL is only
known as a String object to JDBC. SQLJ will not allow invalid SQL to
compile and will inform the developer at development time instead of
runtime.
• The readability of the Java code. SQLJ can accomplish the same tasks as
JDBC with much fewer lines. In addition, it is much easier to read SQL and
DML statements inline in Java.
The disadvantages to SQLJ include:
• The extra step needed to create compiled code to access the database. An
extra step is one more opportunity for problems to occur.
• The readability of the generated Java code.
Whether the developer chooses to use JDBC or SQLJ when writing server-side
code, the code still needs to be loaded into the database. The loadjava
utility is important because it provides this capability directly from the
operating system command line.
Loading and Dropping Java Objects
The loadjava utility (Oracle 8.1.5 and up) loads Java source and class
files into the database. When class files are created in a conventional
manner, outside the database, loadjava is used to get them into the
database.
loadjava requires two database privileges to load java objects into your
own schema: CREATE PROCEDURE and CREATE TABLE. To load Java objects into a
schema other than the currently connected user, CREATE ANY PROCEDURE and
CREATE ANY TABLE privileges are required.
This example will use a simple Java program that will be compiled outside
of Oracle and then loaded into the database.
public class SimpleJava {
public void main(String[] args) {
System.out.println("Here we are");
}
From DOS or UNIX :
C:\oracle9i\bin>javac SimpleJava.java
C:\oracle9i\bin>loadjava -user scott/tiger SimpleJava.class
The class file is now loaded into the database and visible from the
dba_objects view with an object type of JAVA CLASS.
From SQL*Plus, create the PL/SQL wrapper to invoke the newly loaded Java
class:
SQL> create or replace procedure call_simplejava
2 as language java
3 name 'SimpleJava.showMessage()';
4 /
Execute the code from SQL*Plus:
SQL> set serveroutput on;
SQL> call dbms_java.set_output(50);
Call completed.
SQL> execute call_simplejava;
Here we are
PL/SQL procedure successfully completed.
In this example, the Java class file was loaded into the database. The
Java source file can also be loaded. But, both the source and class files
cannot be loaded at the same time.
C:\oracle9i\bin>loadjava -user scott/tiger SimpleJava.java
If loading many Java class files at one time, it is advisable to put them
in a JAR file and load them into the database at one time, since the
loadjava program will also load JAR files. A JAR file is a group of Java
class files lumped into one file, a format similar to TAR (on UNIX ) and
WinZip (on Windows). The contents of a JAR file can be viewed using these
popular utilities. Java developers prefer to distribute a few JAR files
rather than many individual Java class files.
loadjava provides many command line options for the Java developer. The
complete list can be viewed by typing loadjava at the command line.
Just as loadjava loads the database with our Java files or classes, the
dropjava utility deletes them. In the example below, the class file that
was loaded with loadjava is removed. And just like loadjava, it will drop
Java source, Java classes, or JAR files.
C:\oracle9i\bin>dropjava -u scott/tiger SimpleJava.class
Alternatively, instead of using the command line utility, you can call a
package that will do the same thing:
SQL> call dbms_java.dropjava('... options...');
Dropjava can be used to delete Java objects from the database. These Java
objects may have been loaded into the database through the loadjava
utility. The next utility also loads code into the database; only instead
of Java, it loads a PL/SQL Server Page (PSP).
|
|
|
|
|
Oracle Training from Don Burleson
The best on site
"Oracle
training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!
|
|
|
|
| |
|
Burleson is the American Team
Note:
This Oracle
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.
Feel free to ask questions on our
Oracle forum.
Verify
experience!
Anyone
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
publish
their Oracle
qualifications.
Errata?
Oracle technology is changing and we
strive to update our BC Oracle support information. If you find an error
or have a suggestion for improving our content, we would appreciate your
feedback. Just
e-mail:
 and include the URL for the page.
Copyright © 1996 - 2020
All rights reserved by
Burleson
Oracle ®
is the registered trademark of Oracle Corporation.
|
|
