A JDBC Reference Information

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.

{d 'yyyy-mm-dd'}

{d '1995-10-22'}

// Connect to the database
// You can put a database name after the @ sign in the connection URL.
OracleDataSource ods = new OracleDataSource();
ods.setURL("jdbc:oracle:oci:@");
ods.setUser("HR");
ods.setPassword("hr");
Connection conn = ods.getConnection();

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

// Select the first name column from the employees table where the hire date is Jan-23-1982
ResultSet rset = stmt.executeQuery 
                 ("SELECT first_name FROM employees WHERE hire_date = {d '1982-01-23'}");

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

{t 'hh:mm:ss'}

{t '05:10:45'}

{t '14:20:50'}

ResultSet rset = stmt.executeQuery 
                 ("SELECT first_name FROM employees WHERE hire_date = {t '12:00:00'}");

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

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

Scalar Functions

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.OracleDatabaseMetaData class and the standard Java java.sql.DatabaseMetadata interface:

• getNumericFunctions()

  Returns a comma-delimited list of math functions supported by the driver. For example, ABS, COS, SQRT.

• getStringFunctions()

  Returns a comma-delimited list of string functions supported by the driver. For example, ASCII, LOCATE.

• getSystemFunctions()

  Returns a comma-delimited list of system functions supported by the driver. For example, DATABASE, USER.

• getTimeDateFunctions()

  Returns a comma-delimited list of time and date functions supported by the driver. For example, CURDATE, DAYOFYEAR, HOUR.

  Note:

  Oracle JDBC drivers support fn, the function keyword.

LIKE Escape Characters

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

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));

ResultSet rset = stmt.executeQuery("SELECT empno FROM emp
                WHERE ename LIKE '\\_%' {escape '\\'}");

MATCH_RECOGNIZE Clause

The ? character is used as a token in MATCH_RECOGNIZE clause in Oracle Database 11g and later versions. As the JDBC standard defines the ? character as a parameter marker, the JDBC Driver and the Server SQL Engine cannot distinguish between different uses of the same token.

In earlier versions of JDBC Driver, if you want to interpret the ? character as a MATCH_RECOGNIZE token and not as a parameter marker, then you must use a Statement instead of a PreparedStatement and disable escape processing. However, starting from Oracle Database 12c Release 1 (12.1.0.2), you can use the '{\ ... \}' syntax while using the ? character, so that the JDBC driver does not process it as a parameter marker and allows the SQL engine to process it. The following code snippet shows how to use the '{\ ... \}' syntax:

  String sql =
    "select T.firstW, T.lastZ, ? " +  // use of parameter marker
    "from tkpattern_S11 " +
    "MATCH_RECOGNIZE ( " +
    "    MEASURES A.c1 as firstW, last(Z.c1) as lastZ " +
    "    ALL MATCHES " +
    "    PATTERN(A{\?\} X{\*?\} Y{\+?\} Z{\??\}) " +  // use of escape sequence
    "    DEFINE " +
    "        X as X.c2 > prev(X.c2), " +
    "        Y as Y.c2 < prev(Y.c2), " +
    "        Z as Z.c2 > prev(Z.c2)" +
    ") as T";
  PreparedStatement ps = conn.prepareStatatement(sql);
  ps.setString(1, "test");
  ResultSet rs = ps.executeQuery();

Outer Joins

Oracle JDBC drivers do not support the outer join syntax. 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 b, dept a WHERE a.deptno = b.deptno(+)
       ORDER BY ename");

Function Call Syntax

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

Procedure calls:

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

Function calls:

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

JDBC Escape Syntax to Oracle SQL Syntax Example

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

public class Foo 
{ 
   static oracle.jdbc.OracleDriver driver = new oracle.jdbc.OracleDriver();
   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 + " => " + 
         driver.processSqlEscapes(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_TIMESTAMP ('1998-10-22 16:22:34', 'YYYY-MM-DD 
HH24:MI:SS.FF')

CursorName

Oracle JDBC drivers do not support the get getCursorName and setCursorName methods, because there is no convenient way to map them to Oracle constructs. Oracle recommends using ROWID instead.

JDBC Outer Join Escapes

Oracle JDBC drivers do not support JDBC outer join escapes. Use Oracle SQL syntax with + instead.

IEEE 754 Floating Point Compliance

The arithmetic for the Oracle NUMBER type does not comply with the IEEE 754 standard for floating-point arithmetic. Therefore, there can be small disagreements between the results of computations performed by Oracle and the same computations performed by Java.

Oracle stores numbers in a format compatible with decimal arithmetic and guarantees 38 decimal digits of precision. It represents zero, minus infinity, and plus infinity exactly. For each positive number it represents, it represents a negative number of the same absolute value.

It represents every positive number between 10^-30 and (1 – 10^-38) * 10¹²⁶ to full 38-digit precision.

Catalog Arguments to DatabaseMetaData Calls

Certain DatabaseMetaData methods define a catalog parameter. This parameter is one of the selection criteria for the method. Oracle does not have multiple catalogs, but it does have packages.

SQLWarning Class

The java.sql.SQLWarning class provides information about a database access warning. Warnings typically contain a description of the warning and a code that identifies the warning. Warnings are silently chained to the object whose method caused it to be reported. Oracle JDBC drivers generally do not support SQLWarning. As an exception to this, scrollable result set operations do generate SQL warnings, but the SQLWarning instance is created on the client, not in the database.

Executing DDL Statements

You must execute Data Definition Language (DDL) statements with Statement objects. If you use PreparedStatements objects or CallableStatements objects, then the DDL statement takes effect only on the first execution. This can cause unexpected behavior if the SQL statements are in a statement cache.

Binding Named Parameters

Binding by name is not supported when using the setXXX methods. Under certain circumstances, previous versions of Oracle JDBC drivers have allowed binding statement variables by name when using the setXXX methods. In the following statement, the named variable EmpId would be bound to the integer 314159.

PreparedStatement p = conn.prepareStatement
  ("SELECT name FROM emp WHERE id = :EmpId");
  p.setInt(1, 314159);

This capability to bind by name using the setXXX methods is not part of the JDBC specification, and Oracle does not support it. The JDBC drivers can throw a SQLException or produce unexpected results. Starting from Oracle Database 10g JDBC drivers, bind by name is supported using the setXXXAtName methods.

The bound values are not copied by the drivers until you call the execute method. So, changing the bound value before calling the execute method could change the bound value. For example, consider the following code snippet:

PreparedStatement p;
.......
Date d = new Date(1181676033917L);
p.setDate(1, d);
d.setTime(0);
p.executeUpdate();

This code snippet inserts Date(0) in the database instead of Date(1181676033917L) because the bound values are not copied by JDBC driver implementation for performance reasons.