Skip Headers

Oracle9i JDBC Developer's Guide and Reference
Release 2 (9.2)

Part Number A96654-01
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback

Go to previous page Go to next page

20
Reference Information

This chapter contains detailed JDBC reference information, including the following topics:

Valid SQL-JDBC Datatype Mappings

Table 3-2 in Chapter 3 describes the default mappings between Java classes and SQL datatypes supported by the Oracle JDBC drivers. Compare the contents of the JDBC Datatypes, Standard Java Types and SQL Datatypes columns in Table 3-2 with the contents of Table 20-1 below.

Table 20-1 lists all the possible Java types to which a given SQL datatype can be validly mapped. The Oracle JDBC drivers will support these "non-default" 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.


Notes:
  • For the following SQL datatypes, oracle.sql.ORAData or oracle.sql.Datum can be materialized as java types.
  • For classes where oracle.sql.ORAData appears in italic, these can be generated by JPublisher.

Table 20-1 Valid SQL Datatype-Java Class Mappings  
These SQL datatypes: Can be materialized as these Java types:

CHAR, VARCHAR2, LONG

oracle.sql.CHAR

java.lang.String

java.sql.Date

java.sql.Time

java.sql.Timestamp

java.lang.Byte

java.lang.Short

java.lang.Integer

java.lang.Long

java.lang.Float

java.lang.Double

java.math.BigDecimal

byte, short, int, long, float, double

DATE

oracle.sql.DATE

java.sql.Date

java.sql.Time

java.sql.Timestamp

java.lang.String

NUMBER

oracle.sql.NUMBER

java.lang.Byte

java.lang.Short

java.lang.Integer

java.lang.Long

java.lang.Float

java.lang.Double

java.math.BigDecimal

byte, short, int, long, float, double

OPAQUE

oracle.sql.OPAQUE

RAW, LONG RAW

oracle.sql.RAW

byte[]

ROWID

oracle.sql.CHAR

oracle.sql.ROWID

java.lang.String

BFILE

oracle.sql.BFILE

BLOB

oracle.sql.BLOB

java.sql.Blob (oracle.jdbc2.Blob under JDK 1.1.x)

CLOB

oracle.sql.CLOB

java.sql.Clob (oracle.jdbc2.Clob under JDK 1.1.x)

Object types and SQLJ types

oracle.sql.STRUCT

TS

oracle.sql.TIMESTAMP

TSTZ

oracle.sql.TIMESTAMPTZ

TSLTZ

oracle.sql.TIMESTAMPLTZ

java.sql.Struct (oracle.jdbc2.Struct under JDK 1.1.x)

java.sql.SqlData

oracle.sql.ORAData

Reference types

oracle.sql.REF

java.sql.Ref (oracle.jdbc2.Ref under JDK 1.1.x)

oracle.sql.ORAData

Nested table types and VARRAY types

oracle.sql.ARRAY

java.sql.Array (oracle.jdbc2.Array under JDK 1.1.x)

oracle.sql.ORAData


Notes:
  • 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.
  • The mappings to oracle.sql classes are optimal if no conversion from SQL format to Java format is necessary.

Supported SQL and PL/SQL Datatypes

The tables in this section list SQL and PL/SQL datatypes, and whether the Oracle JDBC drivers and SQLJ support them. Table 20-2 describes Oracle JDBC driver and SQLJ support for SQL datatypes.

Table 20-2 Support for SQL Datatypes  
SQL Datatype Supported by JDBC Drivers? Supported by SQLJ?

BFILE

yes

yes

BLOB

yes

yes

CHAR

yes

yes

CLOB

yes

yes

DATE

yes

yes

NCHAR

no

no

NCHAR VARYING

no

no

NUMBER

yes

yes

NVARCHAR2

no

no

RAW

yes

yes

REF

yes

yes

ROWID

yes

yes

UROWID

no

no

VARCHAR2

yes

yes

Table 20-3 describes Oracle JDBC driver and SQLJ support for the ANSI-supported SQL datatypes.

Table 20-3 Support for ANSI-92 SQL Datatypes  
ANSI-Supported SQL Datatype Supported by JDBC Drivers? Supported by SQLJ?

CHARACTER

yes

yes

DEC

yes

yes

DECIMAL

yes

yes

DOUBLE PRECISION

yes

yes

FLOAT

yes

yes

INT

yes

yes

INTEGER

yes

yes

NATIONAL CHARACTER

no

no

NATIONAL CHARACTER VARYING

no

no

NATIONAL CHAR

yes

yes

NATIONAL CHAR VARYING

no

no

NCHAR

yes

yes

NCHAR VARYING

no

no

NUMERIC

yes

yes

REAL

yes

yes

SMALLINT

yes

yes

VARCHAR

yes

yes

Table 20-4 describes Oracle JDBC driver and SQLJ support for SQL User-Defined types.

Table 20-4 Support for SQL User-Defined Types 
SQL User-Defined type Supported by JDBC Drivers? Supported by SQLJ?

OPAQUE

yes

no

Reference types

yes

yes

SQLJ types (JAVA_STRUCT)

yes

no

Object types (JAVA_OBJECT)

yes

yes

Nested table types and VARRAY types

yes

yes


Note:

SQLJ types are described in the Information Technology - SQLJ - Part 2: SQL Types using the JAVATM Programming Language document (ANSI NCITS 331.2-2000).


Table 20-5 describes Oracle JDBC driver and SQLJ support for PL/SQL datatypes. Note that PL/SQL datatypes include these categories:

Table 20-5 Support for PL/SQL Datatypes  
PL/SQL Datatype Supported by JDBC Drivers? Supported by SQLJ?

Scalar Types:

BINARY INTEGER

yes

yes

DEC

yes

yes

DECIMAL

yes

yes

DOUBLE PRECISION

yes

yes

FLOAT

yes

yes

INT

yes

yes

INTEGER

yes

yes

NATURAL

yes

yes

NATURALn

no

no

NUMBER

yes

yes

NUMERIC

yes

yes

PLS_INTEGER

yes

yes

POSITIVE

yes

yes

POSITIVEn

no

no

REAL

yes

yes

SIGNTYPE

yes

yes

SMALLINT

yes

yes

Scalar Character Types:

CHAR

yes

yes

CHARACTER

yes

yes

LONG

yes

yes

LONG RAW

yes

yes

NCHAR

no

no

NVARCHAR2

no

no

RAW

yes

yes

ROWID

yes

yes

STRING

yes

yes

UROWID

no

no

VARCHAR

yes

yes

VARCHAR2

yes

yes

BOOLEAN

yes

yes

DATE

yes

yes

Composite Types:

RECORD

no

no

TABLE

no

no

VARRAY

yes

yes

Reference Types:

REF CURSOR types

yes

yes

object reference types

yes

yes

LOB Types:

BFILE

yes

yes

BLOB

yes

yes

CLOB

yes

yes

NCLOB

yes

yes


Notes:
  • 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.

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:

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

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.OracleSql class parse() method performs the conversions.

import oracle.jdbc.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') 

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.

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. For more information on how to use and manipulate ROWIDs, see "Oracle ROWID Type".

SQL92 Outer Join Escapes

Oracle JDBC drivers do not support SQL92 outer join escapes. Use Oracle SQL syntax with "(+)" instead. For more information on SQL92 syntax, see "Embedded SQL92 Syntax".

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. For a complete description of this, see "Accessing PL/SQL Index-by Tables".

As a workaround to PL/SQL RECORD, BOOLEAN, or non-scalar table types, create wrapper procedures that handle the data as types supported by JDBC. For example, to wrap a stored procedure that uses PL/SQL booleans, 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.

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

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. For more information on how the Oracle JDBC drivers treat the catalog argument, see "DatabaseMetaData TABLE_REMARKS Reporting".

SQLWarning Class

The java.sql.SQLWarning class provides information on 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. The 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.)

For information on how the Oracle JDBC drivers handle errors, see "Processing SQL Exceptions".

Bind by Name

Binding by name is not supported. Under certain circumstances, previous versions of the Oracle JDBC drivers have allowed binding statement variables by name. 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 is not part of the JDBC specification, either 1.0 or 2.0, and Oracle does not support it. The JDBC drivers can throw a SQLException or produce unexpected results.

Prior releases of the Oracle JDBC drivers did not retain bound values from one call of execute to the next as specified in JDBC 1.0. Bound values are now retained. 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, a SQLException would be thrown by the second execute() call because no value was bound to the second argument. In this release, the second execute will return the correct value, retaining the binding of the second argument to the string "SALES".

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

Related Information

This section lists Web sites that contain useful information for JDBC programmers. Many of the sites are referenced in other sections of this manual. In this list you can find references to the Oracle JDBC drivers, Oracle SQLJ, Java technology, the Java Developer's Kit APIs (for versions 1.2.x and 1.1.x), the Java Security API, and resources to help you write signed applets.

Oracle JDBC Drivers and SQLJ

Oracle JDBC Driver Home Page (Oracle Corporation)

http://www.oracle.com/java/jdbc

Oracle SQLJ Home Page (Oracle Corporation)

http://www.oracle.com/java/sqlj

Java Technology

Java Technology Home Page (Sun Microsystems, Inc.):

http://www.javasoft.com

Java Development Kit (JDK1.2.x and 1.1.x) (Sun Microsystems, Inc.):

http://java.sun.com/products/jdk


Go to previous page Go to next page
Oracle
Copyright © 1999, 2002 Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback