Note:

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

Note:

• The typeUROWID is not supported.

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

Table A-2 Support for SQL Data Types

Note:

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

Table A-4 Support for SQL User-Defined Types

Table A-5 Support for PL/SQL Data Types

Note:

• The typesNATURAL,NATURALn,POSITIVE,POSITIVEn, andSIGNTYPE are subtypes ofBINARY INTEGER.

• The typesDEC,DECIMAL,DOUBLE PRECISION,FLOAT,INT,INTEGER,NUMERIC,REAL, andSMALLINT are subtypes ofNUMBER.

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

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("scott");ods.setPassword("tiger");Connection conn = ods.getConnection();// Create a StatementStatement stmt = conn.createStatement ();// Select the ename column from the emp table where the hiredate is Jan-23-1982ResultSet rset = stmt.executeQuery                  ("SELECT ename FROM emp WHERE hiredate = {d '1982-01-23'}");// Iterate through the result and print the employee nameswhile (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 ename FROM emp WHERE hiredate = {t '12:00:00'}");

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

ResultSet rset = stmt.executeQuery     ("SELECT ename FROM emp WHERE hiredate = {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-specificoracle.jdbc.OracleDatabaseMetaData class and the standard Javajava.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 supportfn, the function keyword.

LIKE Escape Characters

The characters% and_ have special meaning in SQLLIKE 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 numberswhile (rset.next ())   System.out.println (rset.getString (1));

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:

{ callprocedure_name (argument1,argument2,...) }

Function calls:

{ ? = callprocedure_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, theoracle.jdbc.OracleSql classparse() 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 theget getCursorName andsetCursorName methods, because there is no convenient way to map them to Oracle constructs. Oracle recommends usingROWID instead.

SQL92 Outer Join Escapes

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

PL/SQL TABLE, BOOLEAN, and RECORD Types

It is not feasible forOracle JDBC drivers to support calling arguments or return values of the PL/SQLRECORD,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/SQLRECORD,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 asBOOLEAN or, for an output parameter, accepts aBOOLEAN argument from the original procedure and passes it as aCHAR orNUMBER 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 asCHAR andNUMBER, 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.

IEEE 754 Floating Point Compliance

The arithmetic for the OracleNUMBER 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

CertainDatabaseMetaData methods define acatalog 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

Thejava.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 supportSQLWarning. As an exception to this, scrollable result set operations do generate SQL warnings, but theSQLWarning instance is created on the client, not in the database.

Binding Named Parameters

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

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

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

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

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

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

Retaining Bound Values

Before Oracle9i Database, Oracle JDBC drivers did not retain bound values from one call ofexecute to the next as specified in JDBC 1.0. All releases after Oracle9i Database have retained bound values. For example:

PreparedStatement p = conn.prepareStatement    ("SELECT name FROM emp WHERE id = ? AND dept = ?");p.setInt(1, 314159); p.setString(2, "SALES"); ResultSet r1 = p.execute(); p.setInt(1, 425260); ResultSet r2 = p.execute();

Previously, aSQLException would be thrown by the secondexecute call because no value was bound to the second argument. Starting from Oracle Database 10g, the secondexecute will return the correct value, retaining the binding of the second argument to the stringSALES.

If the retained bound value is a stream, then Oracle JDBC drivers will not reset the stream. Unless the application code resets, repositions, or otherwise modifies the stream, the subsequent calls toexecute will sendNULL as the value of the argument.