A JDBC Reference Information

This appendix contains detailed Java Database Connectivity (JDBC) reference information, including the following topics:

Valid SQL-JDBC Data Type Mappings

Table 11-1 describes the default mappings between Java classes and SQL data types supported by Oracle JDBC drivers. Compare the contents of the JDBC Type Codes, Standard Java Types, and SQL Data Types columns in Table 11-1 with the contents of Table A-1.

Table A-1 lists all the possible Java types to which a given SQL data type can be validly mapped. Oracle JDBC drivers will support these nondefault mappings. For example, to materialize SQL CHAR data in an oracle.sql.CHAR object, use the getCHAR method. To materialize it as a java.math.BigDecimal object, use the getBigDecimal method.

Note:

For classes where oracle.sql.ORAData appears in italic, these can be generated by JPublisher.

Table A-1 Valid SQL Data Type-Java Class Mappings

SQL data type Java types

CHAR, VARCHAR2, LONG

java.lang.String

oracle.sql.CHAR

NUMBER

boolean

char

byte

short

int

long

float

double

java.lang.Byte

java.lang.Short

java.lang.Integer

java.lang.Long

java.lang.Float

java.lang.Double

java.math.BigDecimal

oracle.sql.NUMBER

BINARY_INTEGER

boolean

char

byte

short

int

long

BINARY_FLOAT

oracle.sql.BINARY_FLOAT

BINARY_DOUBLE

oracle.sql.BINARY_DOUBLE

DATE

oracle.sql.DATE

RAW

oracle.sql.RAW

BLOB

oracle.sql.BLOB

CLOB

oracle.sql.CLOB

BFILE

oracle.sql.BFILE

ROWID

oracle.sql.ROWID

TIMESTAMP

oracle.sql.TIMESTAMP

TIMESTAMP WITH TIME ZONE

oracle.sql.TIMESTAMPTZ

TIMESTAMP WITH LOCAL TIME ZONE

oracle.sql.TIMESTAMPLTZ

ref cursor

java.sql.ResultSet

sqlj.runtime.ResultSetIterator

user defined named types, ADTs

oracle.sql.STRUCT

opaque named types

oracle.sql.OPAQUE

nested tables and VARRAY named types

oracle.sql.ARRAY

references to named types

oracle.sql.REF


Note:

  • The type UROWID is not supported.

  • The oracle.sql.Datum class is abstract. The value passed to a parameter of type oracle.sql.Datum must be of the Java type corresponding to the underlying SQL type. Likewise, the value returned by a method with return type oracle.sql.Datum must be of the Java type corresponding to the underlying SQL type.

Supported SQL and PL/SQL Data Types

The tables in this section list SQL and PL/SQL data types, and whether Oracle JDBC drivers support them. Table A-2 describes Oracle JDBC driver support for SQL data types.

Table A-2 Support for SQL Data Types

SQL Data Type Supported by JDBC Drivers?

BFILE

yes

BLOB

yes

CHAR

yes

CLOB

yes

DATE

yes

NCHAR

noFoot 1 

NCHAR VARYING

no

NUMBER

yes

NVARCHAR2

yesFoot 2 

RAW

yes

REF

yes

ROWID

yes

UROWID

no

VARCHAR2

yes


Footnote 1 The NCHAR type is supported indirectly. There is no corresponding java.sql.Types type, but if your application calls the formOfUse(NCHAR) method, then this type can be accessed.

Footnote 2 In JSE 6, the NVARCHAR2 type is supported directly. In J2SE 5.0, the NVARCHAR2 type is supported indirectly. There is no corresponding java.sql.Types type, but if your application calls the formOfUse(NCHAR) method, then this type can be accessed.

Table A-3 describes Oracle JDBC support for the ANSI-supported SQL data types.

Table A-3 Support for ANSI-92 SQL Data Types

ANSI-Supported SQL Data Type Supported by JDBC Drivers?

CHARACTER

yes

DEC

yes

DECIMAL

yes

DOUBLE PRECISION

yes

FLOAT

yes

INT

yes

INTEGER

yes

NATIONAL CHARACTER

no

NATIONAL CHARACTER VARYING

no

NATIONAL CHAR

yes

NATIONAL CHAR VARYING

no

NCHAR

yes

NCHAR VARYING

no

NUMERIC

yes

REAL

yes

SMALLINT

yes

VARCHAR

yes


Table A-4 describes Oracle JDBC driver support for SQL User-Defined types.

Table A-4 Support for SQL User-Defined Types

SQL User-Defined type Supported by JDBC Drivers?

OPAQUE

yes

Reference types

yes

Object types (JAVA_OBJECT)

yes

Nested table types and VARRAY types

yes


Table A-5 describes Oracle JDBC driver support for PL/SQL data types. Note that PL/SQL data types include these categories:

  • Scalar types

  • Scalar character types, which includes BOOLEAN and DATE data types

  • Composite types

  • Reference types

  • Large object (LOB) types

Table A-5 Support for PL/SQL Data Types

PL/SQL Data Type Supported by JDBC Drivers?

Scalar Types:

 

BINARY INTEGER

yes

DEC

yes

DECIMAL

yes

DOUBLE PRECISION

yes

FLOAT

yes

INT

yes

INTEGER

yes

NATURAL

yes

NATURALn

no

NUMBER

yes

NUMERIC

yes

PLS_INTEGER

yes

POSITIVE

yes

POSITIVEn

no

REAL

yes

SIGNTYPE

yes

SMALLINT

yes

Scalar Character Types:

 

CHAR

yes

CHARACTER

yes

LONG

yes

LONG RAW

yes

NCHAR

no (see Note)

NVARCHAR2

no (see Note)

RAW

yes

ROWID

yes

STRING

yes

UROWID

no

VARCHAR

yes

VARCHAR2

yes

BOOLEAN

yes

DATE

yes

Composite Types:

 

RECORD

no

TABLE

no

VARRAY

yes

Reference Types:

 

REF CURSOR types

yes

object reference types

yes

LOB Types:

 

BFILE

yes

BLOB

yes

CLOB

yes

NCLOB

yes


Note:

  • The types NATURAL, NATURALn, POSITIVE, POSITIVEn, and SIGNTYPE are subtypes of BINARY INTEGER.

  • The types DEC, DECIMAL, DOUBLE PRECISION, FLOAT, INT, INTEGER, NUMERIC, REAL, and SMALLINT are subtypes of NUMBER.

  • The types NCHAR and NVARCHAR2 are supported indirectly. There is no corresponding java.sql.Types type, but if your application calls formOfUse(NCHAR), then these types can be accessed. Refer to "NCHAR, NVARCHAR2, NCLOB and the defaultNChar Property in JDK 1.5" for details.

Embedded JDBC Escape Syntax

Oracle JDBC drivers support some embedded JDBC escape syntax, which is the syntax that you specify between curly braces. The current support is basic.

Note:

JDBC escape syntax was previously known as SQL92 Syntax or SQL92 escape syntax.

This section describes the support offered by the drivers for the following constructs:

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

Disabling Escape Processing

The processing for JDBC escape 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 JDBC escape syntax processing, then use this statement:

stmt.setEscapeProcessing(false);

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".

The following 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.
OracleDataSource ods = new OracleDataSource();
ods.setURL("jdbc:oracle:oci:@");
ods.setUser("scott");
ods.setPassword("tiger");
Connection conn = ods.getConnection();

// 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'}");

Mapping SQL DATE Data type to Java

Oracle Database 8i and earlier versions did not support TIMESTAMP data, but Oracle DATE data used to have a time component as an extension to the SQL standard. So, Oracle Database 8i and earlier versions of JDBC drivers mapped oracle.sql.DATE to java.sql.Timestamp to preserve the time component. Starting with Oracle Database 9.0.1, TIMESTAMP support was included and 9i JDBC drivers started mapping oracle.sql.DATE to java.sql.Date. This mapping was incorrect as it truncated the time component of Oracle DATE data. To overcome this problem, Oracle Database 11.1 introduced a new flag mapDateToTimestamp. The default value of this flag is true, which means that by default the drivers will correctly map oracle.sql.DATE to java.sql.Timestamp, retaining the time information. If you still want the incorrect but 10g compatible oracle.sql.DATE to java.sql.Date mapping, then you can get it by setting the value of mapDateToTimestamp flag to false.

Note:

  • In Oracle Database 11g, if you have an index on a DATE column to be used by a SQL query, then to obtain faster and accurate results, you must use the setObject method in the following way:

    Date d = parseIsoDate(val);
    Timestamp t = new Timestamp(d.getTime());
    stmt.setObject(pos, new oracle.sql.DATE(t, (Calendar)UTC_CAL.clone()));
    

    This is because if you use the setDate method, then the time component of the Oracle DATE data will be lost and if you use the setTimestamp method, then the index on the DATE column will not be used.

  • To overcome the problem of oracle.sql.DATE to java.sql.Date mapping, Oracle Database 9.2 had introduced a flag, V8Compatible. The default value of this flag was false, which allowed the mapping of Oracle DATE data to java.sql.Date data. But, users could retain the time component of the Oracle DATE data by setting the value of this flag to true. This flag is desupported in 11g because it controlled Oracle Database 8i compatibility, which is no longer supported.

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

Note:

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

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

Oracle JDBC Notes and Limitations

The following limitations exist in the Oracle JDBC implementation, but all of them are either insignificant or have easy workarounds. This section covers the following topics:

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.

See Also:

"Oracle ROWID Type" for more information about how to use and manipulate ROWIDs.

JDBC Outer Join Escapes

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

PL/SQL TABLE, BOOLEAN, and RECORD Types

It is not feasible for Oracle JDBC drivers to support calling arguments or return values of the PL/SQL RECORD, BOOLEAN, or table with non-scalar element types. However, Oracle JDBC drivers support PL/SQL index-by table of scalar element types.

As a workaround to PL/SQL RECORD, BOOLEAN, or non-scalar table types, create container procedures that handle the data as types supported by JDBC. For example, to wrap a stored procedure that uses PL/SQL boolean, create a stored procedure that takes a character or number from JDBC and passes it to the original procedure as BOOLEAN or, for an output parameter, accepts a BOOLEAN argument from the original procedure and passes it as a CHAR or NUMBER to JDBC. Similarly, to wrap a stored procedure that uses PL/SQL records, create a stored procedure that handles a record in its individual components, such as CHAR and NUMBER, or in a structured object type. To wrap a stored procedure that uses PL/SQL tables, break the data into components or perhaps use Oracle collection types.

See Also:

"Boolean Parameters in PL/SQL Stored Procedures" for an example of a workaround for BOOLEAN.

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) * 10126 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.

See Also:

"Reporting DatabaseMetaData TABLE_REMARKS" for information about how Oracle JDBC drivers treat the catalog argument.

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.