Oracle in a Nutshell

Using SQL with SQLJ

Once you have established a connection to an Oracle database with the Oracle.connect( ) function (shown later), you can specify SQL statements with this syntax:

#sql{ [conn_context] [[,]exec_context] SQL_statement };

The variable SQL_statement can be virtually any SQL statement acceptable to the Oracle database. The optional variable conn_context specifies a particular connection context, and the optional variable exec_context specifies an execution context for that connection; see the descriptions of the getConnection( ) and getExecutionContext( ) functions in the later “SQL Methods” section for more information.

You can set the transaction isolation level of a SQL transaction with the following syntax:

#sql [conn_context] {SET TRANSACTION (READ ONLY | READ WRITE) 
        ISOLATION LEVEL (SERIALIZATION | READ COMMITTED) }

If the SQL statement returns a value, you can receive the value with the following syntax:

#sql{ :host_variable = SQL_statement };

The host_variable can include a mode specifier of IN, OUT, or IN OUT. If the host_variable is part of an INTO list or an assignment with a SET statement (both discussed later in this section), the default mode is OUT; otherwise, the default mode is IN.

You can also use the following command:

#sql { SET :host_expression = expression };

to receive data into a host variable. If the expression is a PL/SQL function, you can use the VALUES clause described in the later section “PL/SQL in SQLJ.”

With Oracle9i, you can use bind variables ...