First Steps in JDBC

This section describes how to get up and running with the Oracle JDBC drivers. When using the Oracle JDBC drivers, you must include certain driver-specific information in your programs. This section describes, in the form of a tutorial, where and how to add the information. The tutorial guides you through creating code to connect to and query a database from the client.

To connect to and query a database from the client, you must provide code for these tasks:

You must supply Oracle driver-specific information for the first three tasks, which allow your program to use the JDBC API to access a database. For the other tasks, you can use standard JDBC Java code as you would for any Java application.

Import Packages

Regardless of which Oracle JDBC driver you use, include the following import statements at the beginning of your program (java.math only if needed):

import java.sql.*;

for standard JDBC packages

import java.math.*;

for BigDecimal and BigInteger classes

Import the following Oracle packages when you want to access the extended functionality provided by the Oracle drivers. However, they are not required for the example presented in this section:

Register the JDBC Drivers

You must provide the code to register your installed driver with your program. You do this with the static registerDriver() method of the JDBC DriverManager class. This class provides a basic service for managing a set of JDBC drivers.

Note:

Alternatively, you can use the forName() method of the java.lang.Class class to load the JDBC drivers directly. For example:

Class.forName ("oracle.jdbc.OracleDriver");

However, this method is valid only for JDK-compliant Java virtual machines. It is not valid for Microsoft Java virtual machines.

Because you are using one of Oracle's JDBC drivers, you declare a specific driver name string to registerDriver(). You register the driver only once in your Java application.

DriverManager.registerDriver (new oracle.jdbc.OracleDriver());

Open a Connection to a Database

Open a connection to the database with the static getConnection() method of the JDBC DriverManager class. This method returns an object of the JDBC Connection class that needs as input a user name, password, connect string that identifies the JDBC driver to use, and the name of the database to which you want to connect.

Connecting to a database is a step where you must enter Oracle JDBC driver-specific information in the getConnection() method. If you are not familiar with this method, continue reading the "Understanding the Forms of getConnection()" section below.

If you are already familiar with the getConnection() method, you can skip ahead to either of these sections, depending on the driver you installed:

For all JDBC drivers, you can also specify the database with a Oracle Net keyword-value pair. The Oracle Net keyword-value pair substitutes for the TNSNAMES entry. The following example uses the same parameters as the preceding example, but in the keyword-value format:

Specifying a Database URL That Includes User Name and Password

The following signature takes the URL, user name, and password all as part of a URL parameter:

getConnection(String URL);

Where the URL is of the form:

jdbc:oracle:<drivertype>:<user>/<password>@<database>

The following example connects user scott with password tiger to a database on host myhost using the OCI driver. In this case, however, the URL includes the userid and password, and is the only input parameter.

If you want to connect with the Thin driver, you must specify the port number and SID. For example, if you want to connect to the database on host myhost that has a TCP/IP listener up on port 1521, and the SID (system identifier) is orcl:

the default number of rows to prefetch from the server (default value is "10")

remarksReporting

remarks

String (containing boolean value)

"true" if getTables() and getColumns() should report TABLE_REMARKS; equivalent to using setRemarksReporting() (default value is "false")

defaultBatchValue

batchvalue

String (containing integer value)

the default batch value that triggers an execution request (default value is "10")

includeSynonyms

synonyms

String (containing boolean value)

"true" to include column information from predefined "synonym" SQL entities when you execute a DataBaseMetaDatagetColumns() call; equivalent to connection setIncludeSynonyms() call (default value is "false")

Opening a Connection for the JDBC OCI Driver

For the JDBC OCI driver, you can specify the database by a TNSNAMES entry. You can find the available TNSNAMES entries listed in the file tnsnames.ora on the client computer from which you are connecting. On Windows NT, this file is located in the [ORACLE_HOME]\NETWORK\ADMIN directory. On UNIX systems, you can find it in the /var/opt/oracle directory.

For example, if you want to connect to the database on host myhost as user scott with password tiger that has a TNSNAMES entry of MyHostString, enter:

For the JDBC OCI and Thin drivers, you can also specify the database with a Oracle Net keyword-value pair. This is less readable than a TNSNAMES entry but does not depend on the accuracy of the TNSNAMES.ORA file. The Oracle Net keyword-value pair also works with other JDBC drivers.

For example, if you want to connect to the database on host myhost that has a TCP/IP listener up on port 1521, and the SID (system identifier) is orcl, use a statement such as:

Oracle JDBC does not support login timeouts. Calling the static DriverManager.setLoginTimeout() method will have no effect.

Opening a Connection for the JDBC Thin Driver

Because you can use the JDBC Thin driver in applets that do not depend on an Oracle client installation, you cannot use a TNSNAMES entry to identify the database to which you want to connect. You have to either:

Explicitly list the host name, TCP/IP port and Oracle SID of the database to which you want to connect.

or:

Use a keyword-value pair list.

Note:

The JDBC Thin driver supports only the TCP/IP protocol.

For example, use this string if you want to connect to the database on host myhost that has a TCP/IP listener on port 1521 for the database SID (system identifier) orcl. You can logon as user scott, with password tiger:

Oracle JDBC does not support login timeouts. Calling the static DriverManager.setLoginTimeout() method will have no effect.

Create a Statement Object

Once you connect to the database and, in the process, create your Connection object, the next step is to create a Statement object. The createStatement() method of your JDBC Connection object returns an object of the JDBC Statement class. To continue the example from the previous section where the Connection object conn was created, here is an example of how to create the Statement object:

Statement stmt = conn.createStatement();

Note that there is nothing Oracle-specific about this statement; it follows standard JDBC syntax.

Execute a Query and Return a Result Set Object

To query the database, use the executeQuery() method of your Statement object. This method takes a SQL statement as input and returns a JDBC ResultSet object.

To continue the example, once you create the Statement object stmt, the next step is to execute a query that populates a ResultSet object with the contents of the ENAME (employee name) column of a table of employees named EMP:

ResultSet rset = stmt.executeQuery ("SELECT ename FROM emp");

Again, there is nothing Oracle-specific about this statement; it follows standard JDBC syntax.

Process the Result Set

Once you execute your query, use the next() method of your ResultSet object to iterate through the results. This method steps through the result set row by row, detecting the end of the result set when it is reached.

To pull data out of the result set as you iterate through it, use the appropriate getXXX() methods of the ResultSet object, where XXX corresponds to a Java datatype.

For example, the following code will iterate through the ResultSet object rset from the previous section and will retrieve and print each employee name:

while (rset.next())
System.out.println (rset.getString(1));

Once again, this is standard JDBC syntax. The next() method returns false when it reaches the end of the result set. The employee names are materialized as Java strings.

Close the Result Set and Statement Objects

You must explicitly close the ResultSet and Statement objects after you finish using them. This applies to all ResultSet and Statement objects you create when using the Oracle JDBC drivers. The drivers do not have finalizer methods; cleanup routines are performed by the close() method of the ResultSet and Statement classes. If you do not explicitly close your ResultSet and Statement objects, serious memory leaks could occur. You could also run out of cursors in the database. Closing a result set or statement releases the corresponding cursor in the database.

For example, if your ResultSet object is rset and your Statement object is stmt, close the result set and statement with these lines:

rset.close();
stmt.close();

When you close a Statement object that a given Connection object creates, the connection itself remains open.

Note:

Typically, you should put close() statements in a finally clause.

Make Changes to the Database

To write changes to the database, such as for INSERT or UPDATE operations, you will typically create a PreparedStatement object. This allows you to execute a statement with varying sets of input parameters. The prepareStatement() method of your JDBC Connection object allows you to define a statement that takes variable bind parameters, and returns a JDBC PreparedStatement object with your statement definition.

// Prepare to insert new names in the EMP table
PreparedStatement pstmt =
conn.prepareStatement ("insert into EMP (EMPNO, ENAME) values (?, ?)");
// Add LESLIE as employee number 1500
pstmt.setInt (1, 1500); // The first ? is for EMPNO
pstmt.setString (2, "LESLIE"); // The second ? is for ENAME
// Do the insertion
pstmt.execute ();
// Add MARSHA as employee number 507
pstmt.setInt (1, 507); // The first ? is for EMPNO
pstmt.setString (2, "MARSHA"); // The second ? is for ENAME
// Do the insertion
pstmt.execute ();
// Close the statement
pstmt.close();

Commit Changes

By default, DML operations (INSERT, UPDATE, DELETE) are committed automatically as soon as they are executed. This is known as auto-commit mode. You can, however, disable auto-commit mode with the following method call on the Connection object:

If you disable auto-commit mode, then you must manually commit or roll back changes with the appropriate method call on the Connection object:

conn.commit();

or:

conn.rollback();

A COMMIT or ROLLBACK operation affects all DML statements executed since the last COMMIT or ROLLBACK.

Important:

If auto-commit mode is disabled and you close the connection without explicitly committing or rolling back your last changes, then an implicit COMMIT operation is executed.

Any DDL operation, such as CREATE or ALTER, always includes an implicit COMMIT. If auto-commit mode is disabled, this implicit COMMIT will not only commit the DDL statement, but also any pending DML operations that had not yet been explicitly committed or rolled back.

Close the Connection

You must close your connection to the database once you finish your work. Use the close() method of the Connection object to do this:

conn.close();

Note:

Typically, you should put close() statements in a finally clause.

Sample: Connecting, Querying, and Processing the Results

The steps in the preceding sections are illustrated in the following example, which registers an Oracle JDBC Thin driver, connects to the database, creates a Statement object, executes a query, and processes the result set.

Note that the code for creating the Statement object, executing the query, returning and processing the ResultSet object, and closing the statement and connection all follow standard JDBC syntax.

Table of Mappings

The SQL Datatypes column lists the SQL types that exist in the database.

The JDBC Typecodes column lists data typecodes supported by the JDBC standard and defined in the java.sql.Types class, or by Oracle in the oracle.jdbc.OracleTypes class. For standard typecodes, the codes are identical in these two classes.

The Oracle Extension Java Types column lists the oracle.sql.* Java types that correspond to each SQL datatype in the database. These are Oracle extensions that let you retrieve all SQL data in the form of a oracle.sql.* Java type. Mapping SQL datatypes into the oracle.sql datatypes lets you store and retrieve data without losing information. Refer to "Package oracle.sql" for more information on the oracle.sql.* package.

Table 3-2 Default Mappings Between SQL Types and Java Types

SQL Datatypes

JDBC Typecodes

Standard Java Types

Oracle Extension Java Types

STANDARD JDBC 1.0 TYPES:

CHAR

java.sql.Types.CHAR

java.lang.String

oracle.sql.CHAR

VARCHAR2

java.sql.Types.VARCHAR

java.lang.String

oracle.sql.CHAR

LONG

java.sql.Types.LONGVARCHAR

java.lang.String

oracle.sql.CHAR

NUMBER

java.sql.Types.NUMERIC

java.math.BigDecimal

oracle.sql.NUMBER

NUMBER

java.sql.Types.DECIMAL

java.math.BigDecimal

oracle.sql.NUMBER

NUMBER

java.sql.Types.BIT

boolean

oracle.sql.NUMBER

NUMBER

java.sql.Types.TINYINT

byte

oracle.sql.NUMBER

NUMBER

java.sql.Types.SMALLINT

short

oracle.sql.NUMBER

NUMBER

java.sql.Types.INTEGER

int

oracle.sql.NUMBER

NUMBER

java.sql.Types.BIGINT

long

oracle.sql.NUMBER

NUMBER

java.sql.Types.REAL

float

oracle.sql.NUMBER

NUMBER

java.sql.Types.FLOAT

double

oracle.sql.NUMBER

NUMBER

java.sql.Types.DOUBLE

double

oracle.sql.NUMBER

RAW

java.sql.Types.BINARY

byte[]

oracle.sql.RAW

RAW

java.sql.Types.VARBINARY

byte[]

oracle.sql.RAW

LONGRAW

java.sql.Types.LONGVARBINARY

byte[]

oracle.sql.RAW

DATE

java.sql.Types.DATE

java.sql.Date

oracle.sql.DATE

DATE

java.sql.Types.TIME

java.sql.Time

oracle.sql.DATE

DATE

java.sql.Types.TIMESTAMP

javal.sql.Timestamp

oracle.sql.DATE

STANDARD JDBC 2.0 TYPES:

BLOB

java.sql.Types.BLOB

java.sql.Blob

oracle.sql.BLOB

CLOB

java.sql.Types.CLOB

java.sql.Clob

oracle.sql.CLOB

user-defined object

java.sql.Types.STRUCT

java.sql.Struct

oracle.sql.STRUCT

user-defined reference

java.sql.Types.REF

java.sql.Ref

oracle.sql.REF

user-defined collection

java.sql.Types.ARRAY

java.sql.Array

oracle.sql.ARRAY

ORACLE EXTENSIONS:

BFILE

oracle.jdbc.OracleTypes.BFILE

n/a

oracle.sql.BFILE

ROWID

oracle.jdbc.OracleTypes.ROWID

n/a

oracle.sql.ROWID

REF CURSOR type

oracle.jdbc.OracleTypes.CURSOR

java.sql.ResultSet

oracle.jdbc.OracleResultSet

Note:

Under JDK 1.1.x, the Oracle package oracle.jdbc2 is required to support JDBC 2.0 types. (Under JDK 1.2.x they are supported by the standard java.sql package.)

type extensions for the Oracle BFILE and ROWID datatypes and user-defined types of the REFCURSOR category

Notes Regarding Mappings

This section goes into further detail regarding mappings for NUMBER and user-defined types.

Regarding User-Defined Types

User-defined types such as objects, object references, and collections map by default to weak Java types (such as java.sql.Struct), but alternatively can map to strongly typed custom Java classes. Custom Java classes can implement one of two interfaces:

The standard java.sql.SQLData (for user-defined objects only)

The Oracle-specific oracle.sql.ORAData (primarily for user-defined objects, object references, and collections, but able to map from any SQL type where you want customized processing of any kind)

Regarding NUMBER Types

For the different typecodes that an Oracle NUMBER value can correspond to, call the getter routine that is appropriate for the size of the data for mapping to work properly. For example, call getByte() to get a Java tinyint value, for an item x where -128 < x < 128.

This section describes how the Oracle JDBC drivers handle Java streams for several datatypes. Data streams allow you to read LONG column data of up to 2 gigabytes. Methods associated with streams let you read the data incrementally.

Oracle JDBC drivers support the manipulation of data streams in either direction between server and client. The drivers support all stream conversions: binary, ASCII, and Unicode. Following is a brief description of each type of stream:

binary stream--Used for RAW bytes of data. This corresponds to the getBinaryStream() method.

ASCII stream--Used for ASCII bytes in ISO-Latin-1 encoding. This corresponds to the getAsciiStream() method.

Unicode stream--Used for Unicode bytes with the UCS-2 encoding. This corresponds to the getUnicodeStream() method.

The methods getBinaryStream(), getAsciiStream(), and getUnicodeStream() return the bytes of data in an InputStream object. These methods are described in greater detail in Chapter 7, "Working with LOBs and BFILEs".

Streaming LONG or LONG RAW Columns

When a query selects one or moreLONG orLONGRAW columns, the JDBC driver transfers these columns to the client in streaming mode. After a call to executeQuery() or next(), the data of the LONG column is waiting to be read.

To access the data in a LONG column, you can get the column as a Java InputStream and use the read() method of the InputStream object. As an alternative, you can get the data as a string or byte array, in which case the driver will do the streaming for you.

You can getLONG and LONGRAW data with any of the three stream types. The driver performs NLS conversions for you, depending on the character set of your database and the driver. For more information about NLS, see "JDBC and Globalization Support".

LONG RAW Data Conversions

A call to getBinaryStream() returns RAW data "as-is". A call to getAsciiStream() converts the RAW data to hexadecimal and returns the ASCII representation. A call to getUnicodeStream() converts the RAW data to hexadecimal and returns the Unicode bytes.

For example, if yourLONGRAW column contains the bytes 20 21 22, you receive the following bytes:

LONG RAW

BinaryStream

ASCIIStream

UnicodeStream

20 21 22

20 21 22

49 52 49 53 49 54

which is also

'1' '4' '1' '5' '1' '6'

0049 0052 0049 0053 0049 0054

which is also:

'1' '4' '1' '5' '1' '6'

For example, the LONGRAW value 20 is represented in hexadecimal as 14 or "1" "4". In ASCII, 1 is represented by "49" and "4" is represented by "52". In Unicode, a padding of zeros is used to separate individual values. So, the hexadecimal value 14 is represented as 0 "1" 0 "4". The Unicode representation is 0 "49" 0 "52".

LONG Data Conversions

When you get LONG data with getAsciiStream(), the drivers assume that the underlying data in the database uses an US7ASCII or WE8ISO8859P1 character set. If the assumption is true, the drivers return bytes corresponding to ASCII characters. If the database is not using an US7ASCII or WE8ISO8859P1 character set, a call to getAsciiStream() returns meaningless information.

When you getLONG data with getUnicodeStream(), you get a stream of Unicode characters in the UCS-2 encoding. This applies to all underlying database character sets that Oracle supports.

When you get LONG data with getBinaryStream(), there are two possible cases:

If the driver is JDBC OCI and the client character set is not US7ASCII or WE8ISO8859P1, then a call to getBinaryStream() returns UTF-8. If the client character set is US7ASCII or WE8ISO8859P1, then the call returns a US7ASCII stream of bytes.

If the driver is JDBC Thin and the database character set is not US7ASCII or WE8ISO8859P1, then a call to getBinaryStream() returns UTF-8. If the server-side character set is US7ASCII or WE8ISO8859P1, then the call returns a US7ASCII stream of bytes.

Receiving LONG or LONGRAW columns as a stream (the default case) requires you to pay special attention to the order in which you receive data from the database. For more information, see "Data Streaming and Multiple Columns".

Table 3-3 summarizes LONG and LONGRAW data conversions for each stream type.

Table 3-3 LONG and LONG RAW Data Conversions

Datatype

BinaryStream

AsciiStream

UnicodeStream

LONG

bytes representing characters in Unicode UTF-8. The bytes can represent characters in US7ASCII or WE8ISO8859P1 if:

the value of NLS_LANG on the client is US7ASCII or WE8ISO8859P1.

or:

the database character set is US7ASCII or WE8ISO8859P1.

bytes representing characters in ISO-Latin-1 (WE8ISO8859P1) encoding

bytes representing characters in Unicode UCS-2 encoding

LONG RAW

as-is

ASCII representation of hexadecimal bytes

Unicode representation of hexadecimal bytes

Streaming Example for LONG RAW Data

One of the features of a getXXXStream() method is that it allows you to fetch data incrementally. In contrast, getBytes() fetches all the data in one call. This section contains two examples of getting a stream of binary data. The first version uses the getBinaryStream() method to obtain LONGRAW data; the second version uses the getBytes() method.

Getting a LONG RAW Data Column with getBinaryStream()

This Java example writes the contents of aLONGRAW column to a file on the local file system. In this case, the driver fetches the data incrementally.

The following code creates the table that stores a column of LONGRAW data associated with the name LESLIE:

In this example the contents of the GIFDATA column are transferred incrementally in chunk-sized pieces between the database and the client. The InputStream object returned by the call to getBinaryStream() reads the data directly from the database connection.

Getting a LONG RAW Data Column with getBytes()

This version of the example gets the content of the GIFDATA column with getBytes() instead of getBinaryStream(). In this case, the driver fetches all the data in one call and stores it in a byte array. The previous code snippet can be rewritten as:

Because a LONGRAW column can contain up to 2 gigabytes of data, the getBytes() example will probably use much more memory than the getBinaryStream() example. Use streams if you do not know the maximum size of the data in your LONG or LONGRAW columns.

Avoiding Streaming for LONG or LONG RAW

The JDBC driver automatically streams any LONG and LONGRAW columns. However, there may be situations where you want to avoid data streaming. For example, if you have a very small LONG column, you might want to avoid returning the data incrementally and instead, return the data in one call.

To avoid streaming, use the defineColumnType() method to redefine the type of the LONG column. For example, if you redefine the LONG or LONG RAW column as type VARCHAR or VARBINARY, then the driver will not automatically stream the data.

If you redefine column types with defineColumnType(), you must declare the types of all columns in the query. If you do not, executeQuery() will fail. In addition, you must cast the Statement object to an oracle.jdbc.OracleStatement object.

As an added benefit, using defineColumnType() saves the driver two round trips to the database when executing the query. Without defineColumnType(), the JDBC driver has to request the datatypes of the column types.

Using the example from the previous section, the Statement object stmt is cast to the OracleStatement and the column containing LONGRAW data is redefined to be of the type VARBINARAY. The data is not streamed--instead, it is returned in a byte array.

//cast the statement stmt to an OracleStatement
oracle.jdbc.OracleStatement ostmt =
(oracle.jdbc.OracleStatement)stmt;
//redefine the LONG column at index position 1 to VARBINARY
ostmt.defineColumnType(1, Types.VARBINARY);
// Do a query to get the images named 'LESLIE'
ResultSet rset = ostmt.executeQuery
("select GIFDATA from streamexample where NAME='LESLIE'");
// The data is not streamed here
rset.next();
byte [] bytes = rset.getBytes(1);

Streaming CHAR, VARCHAR, or RAW Columns

If you use the defineColumnType() Oracle extension to redefine a CHAR, VARCHAR, or RAW column as a LONGVARCHAR or LONGVARBINARY, then you can get the column as a stream. The program will behave as if the column were actually of type LONG orLONG RAW. Note that there is not much point to this, because these columns are usually short.

If you try to get a CHAR, VARCHAR, or RAW column as a data stream without redefining the column type, the JDBC driver will return a Java InputStream, but no real streaming occurs. In the case of these datatypes, the JDBC driver fully fetches the data into an in-memory buffer during a call to the executeQuery() method or next() method. The getXXXStream() entry points return a stream that reads data from this buffer.

Data Streaming and Multiple Columns

If your query selects multiple columns and one of the columns contains a data stream, then the contents of the columns following the stream column are not available until the stream has been read, and the stream column is no longer available once any following column is read. Any attempt to read a column beyond a streaming column closes the streaming column. See "Streaming Data Precautions" for more information.

As you process each row of the iterator, you must complete any processing of the stream column before reading the number column.

An exception to this behavior is LOB data, which is also transferred between server and client as a Java stream. For more information on how the driver treats LOB data, see "Streaming LOBs and External Files".

Bypassing Streaming Data Columns

There might be situations where you want to avoid reading a column that contains streaming data. If you do not want to read the data for the streaming column, then call the close() method of the stream object. This method discards the stream data and allows the driver to continue reading data for all the non-streaming columns that follow the stream. Even though you are intentionally discarding the stream, it is good programming practice to call the columns in SELECT-list order.

In the following example, the stream data in the LONG column is discarded and the data from only the DATE and NUMBER column is recovered:

Streaming LOBs and External Files

The term large object (LOB) refers to a data item that is too large to be stored directly in a database table. Instead, a locator is stored in the database table and points to the location of the actual data. External files (binary files, or BFILEs) are managed similarly. The JDBC drivers can support these types through the use of streams:

BLOBs (unstructured binary data)

CLOBs (character data)

BFILEs (external files)

LOBs and BFILEs behave differently from the other types of streaming data described in this chapter. The driver transfers data between server and client as a Java stream. However, unlike most Java streams, a locator representing the data is stored in the table. Thus, you can access the data at any time during the life of the connection.

Streaming BLOBs and CLOBs

When a query selects one or more CLOB or BLOB columns, the JDBC driver transfers to the client the data pointed to by the locator. The driver performs the transfer as a Java stream. To manipulate CLOB or BLOB data from JDBC, use methods in the Oracle extension classes oracle.sql.BLOB and oracle.sql.CLOB. These classes provide functionality such as reading from the CLOB or BLOB into an input stream, writing from an output stream into a CLOB or BLOB, determining the length of a CLOB or BLOB, and closing a CLOB or BLOB.

The JDBC 2.0 specification states that PreparedStatement methods setBinaryStream() and setObject() can be used to input a stream value as a BLOB, and that the PreparedStatement methods setAsciiStream(), setUnicodeStream(), setCharacterStream(), and setObject() can be used to input a stream value as a CLOB. This bypasses the LOB locator, going directly to the LOB data itself.

In the implementation of the Oracle JDBC drivers, this functionality is supported only for a configuration using an 8.1.6 database and 8.1.6 JDBC OCI driver. Do not use this functionality for any other configuration, as data corruption can result.

Streaming BFILEs

An external file, or BFILE, is used to store a locator to a file outside the database, stored somewhere on the filesystem of the data server. The locator points to the actual location of the file.

When a query selects one or more BFILE columns, the JDBC driver transfers to the client the file pointed to by the locator. The transfer is performed in a Java stream. To manipulate BFILE data from JDBC, use methods in the Oracle extension class oracle.sql.BFILE. This class provides functionality such as reading from the BFILE into an input stream, writing from an output stream into a BFILE, determining the length of a BFILE, and closing a BFILE.

Closing a Stream

You can discard the data from a stream at any time by calling the stream's close() method. You can also close and discard the stream by closing its result set or connection object. You can find more information about the close() method for data streams in "Bypassing Streaming Data Columns". For information on how to avoid closing a stream and discarding its data by accident, see "Streaming Data Precautions".

Notes and Precautions on Streams

This section discusses several noteworthy and cautionary issues regarding the use of streams:

Streaming Data Precautions

This section describes some of the precautions you must take to ensure that you do not accidentally discard or lose your stream data. The drivers automatically discard stream data if you perform any JDBC operation that communicates with the database, other than reading the current stream. Two common precautions are described:

Use the stream data after you access it.

To recover the data from a column containing a data stream, it is not enough to get the column; you must immediately process its contents. Otherwise, the contents will be discarded when you get the next column.

Call the stream column in SELECT-list order.

If your query selects multiple columns, the database sends each row as a set of bytes representing the columns in the SELECT order. If one of the columns contains stream data, the database sends the entire data stream before proceeding to the next column.

If you do not use the SELECT-list order to access data, then you can lose the stream data. That is, if you bypass the stream data column and access data in a column that follows it, the stream data will be lost. For example, if you try to access the data for theNUMBER column before reading the data from the stream data column, the JDBC driver first reads then discards the streaming data automatically. This can be very inefficient if the LONG column contains a large amount of data.

If you try to access the LONG column later in the program, the data will not be available and the driver will return a "Stream Closed" error.

Using Streams to Avoid Limits on setBytes() and setString()

There is a limit on the maximum size of the array which can be bound using the PreparedStatement class setBytes() method, and on the size of the string which can be bound using the setString() method.

Above the limits, which depend on the version of the server you use, you should use setBinaryStream() or setCharacterStream() instead.

When connecting to an Oracle8 database, the limit for setBytes() is 2000 bytes (the maximum size of a RAW in Oracle8) and the limit for setString() is 4000 bytes (the maximum size of a VARCHAR2 in Oracle8).

When connecting to an Oracle7 database, the limit for setBytes() is 255 bytes (the maximum size of a RAW in Oracle7) and the limit for setString() is 2000 bytes (the maximum size of a VARCHAR2 in Oracle7).

The 8.1.6 Oracle JDBC drivers may not raise an error if you exceed the limit when using setBytes() or setString(), but you may receive the following error:

ORA-17070: Data size bigger than max size for this type

Future versions of the Oracle drivers will raise an error if the length exceeds these limits.

Note:

This discussion applies to binds in SQL, not PL/SQL.

Streaming and Row Prefetching

If the JDBC driver encounters a column containing a data stream, row prefetching is set back to 1.

Row prefetching is an Oracle performance enhancement that allows multiple rows of data to be retrieved with each trip to the database. See "Oracle Row Prefetching".

Stored Procedure Calls in JDBC Programs

This section describes how the Oracle JDBC drivers support the following kinds of stored procedures:

PL/SQL Stored Procedures

Oracle JDBC drivers support execution of PL/SQL stored procedures and anonymous blocks. They support both SQL92 escape syntax and Oracle PL/SQL block syntax. The following PL/SQL calls would work with any Oracle JDBC driver:

Java Stored Procedures

You can use JDBC to invoke Java stored procedures through the SQL and PL/SQL engines. The syntax for calling Java stored procedures is the same as the syntax for calling PL/SQL stored procedures, presuming they have been properly "published" (that is, have had call specifications written to publish them to the Oracle data dictionary). See the Oracle9i Java Stored Procedures Developer's Guide for more information on writing, publishing, and using Java stored procedures.

Processing SQL Exceptions

To handle error conditions, the Oracle JDBC drivers throws SQL exceptions, producing instances of class java.sql.SQLException or a subclass. Errors can originate either in the JDBC driver or in the database (RDBMS) itself. Resulting messages describe the error and identify the method that threw the error. Additional run-time information can also be appended.

Basic exception-handling can include retrieving the error message, retrieving the error code, retrieving the SQL state, and printing the stack trace. The SQLException class includes functionality to retrieve all of this information, where available.

Retrieving Error Information

You can retrieve basic error information with these SQLException methods:

getMessage()

For errors originating in the JDBC driver, this method returns the error message with no prefix. For errors originating in the RDBMS, it returns the error message prefixed with the corresponding ORA number.

getErrorCode()

For errors originating in either the JDBC driver or the RDBMS, this method returns the five-digit ORA number.

getSQLState()

For errors originating in the JDBC driver, this returns no useful information. For errors originating in the RDBMS, this method returns a five-digit code indicating the SQL state. Your code should be prepared to handle null data.