Oracle® Database JDBC Developer's Guide and Reference 10g Release 2 (10.2) Part Number B14355-02 |
|
|
View PDF |
This appendix contains detailed Java Database Connectivity (JDBC) reference information, including the following topics:
Table 13-1 describes the default mappings between Java classes and SQL data types supported by the Oracle JDBC drivers. Compare the contents of the JDBC Type Codes, Standard Java Types, and SQL Data Types columns in Table 13-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. The 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.
Notes:
For classes whereoracle.sql.ORAData
appears in italic, these can be generated by JPublisher.Table A-1 Valid SQL Data Type-Java Class Mappings
These SQL data types: | Can be materialized as these Java types: |
---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Object types |
|
|
|
|
|
|
|
Reference types |
|
|
|
|
|
Nested table types and |
|
|
|
|
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 tables in this section list SQL and PL/SQL data types, and whether the 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 |
no (see Note) |
NCHAR VARYING |
no |
NUMBER |
yes |
NVARCHAR2 |
no (see Note) |
RAW |
yes |
REF |
yes |
ROWID |
yes |
UROWID |
no |
VARCHAR2 |
yes |
Note:
The typesNCHAR
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.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 ( |
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 |
Notes:
The types NATURAL
, NATURAL
n
, POSITIVE
, POSITIVE
n
, 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" for details.
The Oracle JDBC drivers support some embedded SQL92 syntax, which 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);
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'}");
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:
Returns a comma-delimited list of math functions supported by the driver. For example, ABS
, COS
, SQRT
.
Returns a comma-delimited list of string functions supported by the driver. For example, ASCII
, LOCATE
.
Returns a comma-delimited list of system functions supported by the driver. For example, DATABASE
, USER
.
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.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 '\\'}");
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 a, dept b WHERE a.deptno = b.deptno(+) ORDER BY ename");
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,...) }
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 + " => " + oracle.jdbc.OracleDriver.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_DATE ('1998-10-22 16:22:34', 'YYYY-MM-DD HH24:MI:SS')
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:
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.
Oracle JDBC drivers do not support SQL92 outer join escapes. Use Oracle SQL syntax with + instead.
See Also:
"Embedded SQL92 Syntax"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.
See Also:
"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 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 forBOOLEAN
.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.
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:
"DatabaseMetaData TABLE_REMARKS Reporting" for information on how the Oracle JDBC drivers treat thecatalog
argument.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.
See Also:
"Processing SQL Exceptions"Binding by name is not supported when using the set
XXX
methods. Under certain circumstances, previous versions of the Oracle JDBC drivers have allowed binding statement variables by name when using the set
XXX
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 set
XXX
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. In Oracle Database 10g JDBC drivers, bind by name is supported using the set
XXX
AtName
methods.
See Also:
"Interface oracle.jdbc.OracleCallableStatement" and "Interface oracle.jdbc.OraclePreparedStatement"Retaining Bound Values
Before Oracle9i Database, the Oracle JDBC drivers did not retain bound values from one call of execute
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, a SQLException
would be thrown by the second execute
call because no value was bound to the second argument. In Oracle Database 10g, 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 calls to execute
will send NULL
as the value of the argument.