Oracle8i SQLJ Developer's Guide and Reference
Release 8.1.5

A64684-01

Library

Product

Contents

Index

Prev  Chap Top Next

Overview of SQLJ Executable Statements

A SQLJ executable statement consists of the #sql token followed by a SQLJ clause, which uses syntax that follows a specified standard for embedding executable SQL statements in Java code. The embedded SQL operation of a SQLJ executable statement can be any SQL operation that is supported by your JDBC driver (such as DML, DDL, and transaction control).

Rules for SQLJ Executable Statements

A SQLJ executable statement must follow these rules:


Notes:

  • Everything inside the curly braces of a SQLJ executable statement is treated as SQL syntax and must follow SQL rules, with the exception of Java host expressions (which are described in "Java Host Expressions, Context Expressions, and Result Expressions").

  • During examination of SQL operations, only DML operations (such as SELECT, UPDATE, INSERT, and DELETE) can be parsed and checked for syntax and semantics by the SQLJ translator using a database connection. DDL operations (such as CREATE..., or ALTER...), transaction-control operations (such as COMMIT and ROLLBACK), or any other kinds of SQL operations cannot.

 

SQLJ Clauses

A SQLJ clause is the executable part of a statement (everything to the right of the #sql token). This consists of embedded SQL inside curly braces, preceded by a Java result expression if appropriate (such as result below):

#sql { SQL operation };   // For a statement with no output, like INSERT
...
#sql result = { SQL operation };   // For a statement with output, like SELECT

A clause without a result expression, such as in the first example, is known as a statement clause. A clause that does have a result expression, such as in the second example, is known as an assignment clause.

A result expression can be anything from a simple variable that takes a stored-function return value, to an iterator that takes several columns of data from a multi-row SELECT.

A SQL operation in a SQLJ statement can use standard SQL syntax only, or can use a clause with syntax that is specific to SQLJ (see Table 3-1 and Table 3-2 below).

For reference, Table 3-1 lists supported SQLJ statement clauses and Table 3-2 lists supported SQLJ assignment clauses. Details of how to use the various kinds of clauses are discussed elsewhere, as indicated. The last entry in Table 3-1 is a general category for statement clauses that use standard SQL syntax, as opposed to SQLJ-specific syntax.

Table 3-1 SQLJ Statement Clauses
Category  Functionality  More Information 

SELECT INTO clause  

select data into Java host expressions  

"Single-Row Query Results--SELECT INTO Statements"  

FETCH clause  

fetch data from a positional iterator  

"Using Positional Iterators"  

COMMIT clause  

commit changes to the database  

"Using Manual COMMIT and ROLLBACK"  

ROLLBACK clause  

cancel changes to the database  

"Using Manual COMMIT and ROLLBACK"  

set transaction clause  

use advanced transaction control--access mode and isolation level  

"Advanced Transaction Control"  

procedure clause  

call a stored procedure  

"Calling Stored Procedures"  

assignment clause  

assign values to Java host expressions  

"Assignment Statements (SET)"  

SQL clause  

use standard SQL syntax and functionality: SELECT, UPDATE, INSERT, DELETE  

Oracle8i SQL Reference  

Table 3-2 SQLJ Assignment Clauses
Category  Functionality  More Information 

query clause  

select data into a SQLJ iterator  

"Multi-Row Query Results--SQLJ Iterators"  

function clause  

call a stored function  

"Calling Stored Functions"  

iterator conversion clause  

convert a JDBC result set to a SQLJ iterator  

"Converting from Result Sets to Named or Positional Iterators"  


Note:

A SQLJ statement is referred to by the same name as the clause that makes up the body of that statement. For example, an executable statement consisting of #sql followed by a SELECT INTO clause is referred to as a SELECT INTO statement.  


Specifying Connection Context Instances and Execution Context Instances

If you have defined multiple database connections and want to specify a particular connection context instance for an executable statement, use the following syntax:

#sql [conn_context_instance] { SQL operation };

"Connection Considerations" discusses connection context instances.

If you have defined one or more execution context instances and want to specify one of them for use with an executable statement, use the following syntax (similar to that for connection context instances):

#sql [exec_context_instance] { SQL operation };

You can use an execution context instance to provide status or control of the SQL operation of a SQLJ executable statement. This is an advanced topic; for example, you can use execution context instances in multithreading situations where multiple operations are occurring on the same connection. See "Execution Contexts" for information.

You can also specify both a connection context instance and an execution context instance:

#sql [conn_context_instance, exec_context_instance] { SQL operation };


Note:

  • Include the square brackets around connection context instances and execution context instances; they are part of the syntax.

  • If you specify both a connection context instance and an execution context instance, the connection context instance must come first.

 

Executable Statement Examples

Examples of elementary SQLJ executable statements appear below. More complicated statements are discussed later in this chapter.

Elementary INSERT

The following example demonstrates a basic INSERT. The statement clause does not require any syntax that is specific to SQLJ.

Assume this table has been created:

CREATE TABLE EMP (
   EMPNAME CHAR(30),
   SALARY NUMBER );

Use the following SQLJ executable statement (simply using standard SQL syntax) to insert Joe as a new employee into the EMP table, specifying his name and salary.

#sql { INSERT INTO emp (empname, salary) VALUES ('Joe', 43000) };

Elementary INSERT with Connection Context or Execution Context Instances

The following examples use ctx as a connection context instance (an instance of either the default sqlj.runtime.ref.DefaultContext or a class that you have previously declared in a connection context declaration) and execctx as an execution context instance:

#sql [ctx] { INSERT INTO emp (empname, salary) VALUES ('Joe', 43000) };

#sql [execctx] { INSERT INTO emp (empname, salary) VALUES ('Joe', 43000) };

#sql [ctx, execctx] { INSERT INTO emp (empname, salary) VALUES ('Joe', 43000) };

A Simple SQLJ Method

This example demonstrates a simple method using SQLJ code, demonstrating how SQLJ statements interrelate with and are interspersed with Java statements. The SQLJ statement uses standard INSERT INTO table VALUES syntax supported by Oracle SQL. The statement also uses Java host expressions, marked by colons (:), to define the values. (Host expressions are used to pass data between your Java code and SQL instructions. They are discussed in "Java Host Expressions, Context Expressions, and Result Expressions".)

public static void writeSalesData (int[] itemNums, String[] itemNames)
       throws SQLException
{
  for (int i =0; i < itemNums.length; i++)
    #sql { INSERT INTO sales VALUES(:(itemNums[i]), :(itemNames[i]), SYSDATE) };
}


Notes:

  • The throws SQLException is required. For information about exception-handling, see "Exception-Handling Basics".

  • SQLJ function calls also use a VALUES token, but these situations are not related semantically.

 

PL/SQL Blocks in Executable Statements

PL/SQL blocks can be used within the curly braces of a SQLJ executable statement just as SQL operations can, as in the following example:

#sql {
   DECLARE
      n NUMBER;
   BEGIN
      n := 1;
      WHILE n <= 100 LOOP
         INSERT INTO emp (empno) VALUES(2000 + n);
         n := n + 1;
      END LOOP;
   END;
};

This goes through a loop that inserts new employees in the emp table, creating employee numbers 2001-2100 (this example presumes that data other than the employee number will be filled in later).

Simple PL/SQL blocks can also be coded in a single line:

#sql { <DECLARE ...> BEGIN ... END; };


Note:

Remember that using PL/SQL in your SQLJ code would prevent portability to other platforms, because PL/SQL is Oracle-specific.  





Prev

Top

Next
Oracle
Copyright © 1999 Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index