Oracle8i JDBC Developer's Guide and Reference Release 3 (8.1.7) Part Number A83724-01 |
Library | Service | Contents | Index

## Embedded SQL92 Syntax

Oracle's JDBC drivers support some embedded SQL92 syntax. This is the syntax that you specify between curly braces. The current support is basic. This section describes the support offered by the drivers for the following SQL92 constructs:

- Time and Date Literals

- Scalar Functions

- LIKE Escape Characters

- Outer Joins

- Function Call Syntax

Where driver support is limited, these sections also describe possible workarounds.

##### Disabling Escape Processing

Escape processing for SQL92 syntax is enabled by default, which results in the JDBC driver performing escape substitution before sending the SQL code to the database. If you want the driver to use regular Oracle SQL syntax, which is more efficient than SQL92 syntax and escape processing, then use this statement:

    stmt.setEscapeProcessing(false);

Note: Because prepared statements have usually been parsed prior to a call to `setEscapeProcessing()`, disabling escape processing for prepared statements will probably have no affect.

### Time and Date Literals

Databases differ in the syntax they use for date, time, and timestamp literals. JDBC supports dates and times written only in a specific format. This section describes the formats you must use for date, time, and timestamp literals in SQL statements.

#### Date Literals

The JDBC drivers support date literals in SQL statements written in the format:

    {d 'yyyy-mm-dd'}

Where `yyyy-mm-dd` represents the year, month, and day--for example:

    {d '1995-10-22'}

The JDBC drivers will replace this escape clause with the equivalent Oracle representation: "22 OCT 1995".

This code snippet contains an example of using a date literal in a SQL statement.

    // Connect to the database
    // You can put a database name after the @ sign in the connection URL.
    Connection conn = DriverManager.getConnection
                      ("jdbc:oracle:oci8:@", "scott", "tiger");

    // Create a Statement
    Statement stmt = conn.createStatement ();

    // Select the ename column from the emp table where the hiredate is Jan-23-1982
    ResultSet rset = stmt.executeQuery
                     ("SELECT ename FROM emp WHERE hiredate = {d '1982-01-23'}");

    // Iterate through the result and print the employee names
    while (rset.next ())
       System.out.println (rset.getString (1));

#### Time Literals

The JDBC drivers support time literals in SQL statements written in the format:

    {t 'hh:mm:ss'}

where `hh:mm:ss` represents the hours, minutes, and seconds--for example:

    {t '05:10:45'}

The JDBC drivers will replace this escape clause with the equivalent Oracle representation: "05:10:45".

If the time is specified as:

    {t '14:20:50'}

Then the equivalent Oracle representation would be "14:20:50", assuming the server is using a 24-hour clock.

This code snippet contains an example of using a time literal in a SQL statement.

    ResultSet rset = stmt.executeQuery
                     ("SELECT ename FROM emp WHERE hiredate = {t '12:00:00'}");

#### Timestamp Literals

The JDBC drivers support timestamp literals in SQL statements written in the format:

    {ts 'yyyy-mm-dd hh:mm:ss.f...'}

where `yyyy-mm-dd hh:mm:ss.f...` represents the year, month, day, hours, minutes, and seconds. The fractional seconds portion (".f...") is optional and can be omitted. For example: `{ts '1997-11-01 13:22:45'}` represents, in Oracle format, NOV 01 1997 13:22:45.

This code snippet contains an example of using a timestamp literal in a SQL statement.

    ResultSet rset = stmt.executeQuery
        ("SELECT ename FROM emp WHERE hiredate = {ts '1982-01-23 12:00:00'}");

### Scalar Functions

The Oracle JDBC drivers do not support all scalar functions. To find out which functions the drivers support, use the following methods supported by the Oracle-specific `oracle.jdbc.driver.OracleDatabaseMetaData` and the standard Java `java.sql.DatabaseMetadata` interfaces:

- `getNumericFunctions()`: Returns a comma-separated list of math functions supported by the driver. For example, `ABS`(`number`), `COS`(`float`), `SQRT`(`float`).

- `getStringFunctions()`: Returns a comma-separated list of string functions supported by the driver. For example, `ASCII`(`string`), `LOCATE`(`string1`, `string2`, `start`).

- `getSystemFunctions()`: Returns a comma-separated list of system functions supported by the driver. For example, `DATABASE`(), `IFNULL`(`expression`, `value`), `USER`().

- `getTimeDateFunctions()`: Returns a comma-separated list of time and date functions supported by the driver. For example, `CURDATE`(), `DAYOFYEAR`(`date`), `HOUR`(`time)`.

Oracle's JDBC drivers do not support the function keyword, '`fn`'. If you try to use this keyword, for example:

    {fn concat ("Oracle", "8i") }

Then you will get the error "`Non supported SQL92 token at position xx: fn`" when you run your Java application. The workaround is to use Oracle SQL syntax.

For example, instead of using the `fn` keyword in embedded SQL92 syntax:

    Statement stmt = conn.createStatement ();
    stmt.executeUpdate("UPDATE emp SET ename = {fn CONCAT('My', 'Name')}");

Use Oracle SQL syntax:

    stmt.executeUpdate("UPDATE emp SET ename = CONCAT('My', 'Name')");

### LIKE Escape Characters

The characters "`%`" and "`_`" have special meaning in SQL `LIKE` clauses (you use "`%`" to match zero or more characters, "`_`" to match exactly one character). If you want to interpret these characters literally in strings, you precede them with a special escape character. For example, if you want to use the ampersand "`&`" as the escape character, you identify it in the SQL statement as {escape '`&`'}:

    Statement stmt = conn.createStatement ();

    // Select the empno column from the emp table where the ename starts with '_'
    ResultSet rset = stmt.executeQuery
              ("SELECT empno FROM emp WHERE ename LIKE '&_%' {ESCAPE '&'}");

    // Iterate through the result and print the employee numbers
    while (rset.next ())
       System.out.println (rset.getString (1));

Note: If you want to use the backslash character (\) as an escape character, you must enter it twice (that is, \\). For example:
    ResultSet rset = stmt.executeQuery("SELECT empno FROM emp
                    WHERE ename LIKE '\\_%' {escape '\\'}");

### Outer Joins

Oracle's JDBC drivers do not support outer join syntax: {oj outer-join}. The workaround is to use Oracle outer join syntax:

Instead of:

    Statement stmt = conn.createStatement ();
    ResultSet rset = stmt.executeQuery
         ("SELECT ename, dname
           FROM {OJ dept LEFT OUTER JOIN emp ON dept.deptno = emp.deptno}
           ORDER BY ename");

Use Oracle SQL syntax:

    Statement stmt = conn.createStatement ();
    ResultSet rset = stmt.executeQuery
         ("SELECT ename, dname
           FROM emp a, dept b WHERE a.deptno = b.deptno(+)
           ORDER BY ename");

### Function Call Syntax

Oracle's JDBC drivers support the following procedure and function call syntax:

Procedure calls (without a return value):

    { call procedure_name (argument1, argument2,...) }

Function calls (with a return value):

    { ? = call procedure_name (argument1, argument2,...) }

### SQL92 to SQL Syntax Example

You can write a simple program to translate SQL92 syntax to standard SQL syntax. The following program prints the comparable SQL syntax for SQL92 statements for function calls, date literals, time literals, and timestamp literals. In the program, the `oracle.jdbc.driver.OracleSql` class `parse()` method performs the conversions.

    import oracle.jdbc.driver.OracleSql;

    public class Foo
    {
       public static void main (String args[]) throws Exception
       {
          show ("{call foo(?, ?)}");
          show ("{? = call bar (?, ?)}");
          show ("{d '1998-10-22'}");
          show ("{t '16:22:34'}");
          show ("{ts '1998-10-22 16:22:34'}");
       }

       public static void show (String s) throws Exception
       {
          System.out.println (s + " => " + new OracleSql().parse (s));
       }
    }

The following code is the output that prints the comparable SQL syntax.

    {call foo(?, ?)} => BEGIN foo(:1, :2); END;
    {? = call bar (?, ?)} => BEGIN :1 := bar (:2, :3); END;
    {d '1998-10-22'} => TO_DATE ('1998-10-22', 'YYYY-MM-DD')
    {t '16:22:34'} => TO_DATE ('16:22:34', 'HH24:MI:SS')
    {ts '1998-10-22 16:22:34'} => TO_DATE ('1998-10-22 16:22:34', 'YYYY-MM-DD
    HH24:MI:SS')

| Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
Library | Service | Contents | Index