CRITICAL SKILL 7.8 Java-Stored Procedures

Team Fly

Page 262

translator (precompiler) is used to convert a .sqlj file containing embedded SQL statements into a .java file containing JDBC statements. The translated .java file is then compiled. SQLJ statements can contain queries, DML, transaction control, and DDL statements. The SQLJ translator in the database will automatically convert .sqlj files into compiled Java code.

Sample SQLJ Code

SQLJ makes it easier for traditional Oracle developers to write Java database code. Oracle developers can embed the SQL statements without having to work with all of the JDBC interfaces. SQLJ performs compile time checking of SQL statements while JDBC performs runtime checking of SQL statements. The following code snippets are some examples of using SQLJ.

String name;
int  empid = 7788;
...
  #sql {SELECT ename INTO :name FROM emp WHERE empno=:empid};
  #sql {INSERT INTO emp (empno, ename, sal, deptno)
      VALUES (8888, 'TRUJILLO', 2400, 10);
  #sql {CREATE TABLE mytemp (id NUMBER, name VARCHAR2(20)) };
  #sql { UPDATE emp SET salary = salary * 1.10 WHERE id = :empid };
  #sql { DELETE FROM customer WHERE id = :custid };
...

SQLJ Directions

SQLJ offers a number of advantages over JDBC. However, there are a number of performance benefits of writing the lower-level JDBC statements directly. SQLJ has been deprecated in the Oracle Database 10g server. SQLJ applications running in the database server will continue to work in Oracle Database 10g. In a future release, Oracle will provide a migration tool and options with existing Oracle tools to ease the migration to pure JDBC.

CRITICAL SKILL 7.8
Java-Stored Procedures

With the inclusion of Java within the database, Oracle wanted to have Java-similar support features that current Oracle users were used to, and one such area is stored procedures. As discussed in Chapter 6, storing objects with PL/SQL or Java has numerous advantages. The ability to call shared programs whether written in PL/SQL or Java is a strength of Oracle Database 10g.

Team Fly