Oracle sqlj
Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Home
 
Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients

 

Free Oracle Tips


 
HTML Text AOL

Free Oracle App Server Tips


 
HTML Text

Oracle support

Oracle training

Oracle tuning

Rednecks!

Remote Oracle

Custom Oracle Training

Donald K. Burleson

Oracle Utilities Tips

The SQLJ Utility


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.

SEE CODE DEPOT


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.

SEE CODE DEPOT

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.
 


To learn more about these techniques, see the book "Advanced Oracle Utilities: The Definitive Reference". 

You can buy it directly from the publisher and get instant access to the code depot of utilities scripts.
 

 

 

 
 
 

Oracle performance tuning book

 

 

Oracle performance tuning software

 
Oracle performance tuning software
 
Oracle performance Tuning 10g reference poster
 
Oracle training in Linux commands
 
Oracle training Excel
 
 
 
 
 
email BC:


Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.