4 Basic Features

4.1.2.1 Specifying a Database URL, User Name, and Password

The following code sets the URL, user name, and password for a datasource:

OracleDataSource ods = new OracleDataSource();
ods.setURL(URL);
ods.setUser(user);
ods.setPassword(password);

(For URL format, see Chapter 3, "Datasources and URLs".)

The following example connects user scott with password tiger to a database with service orcl through port 1521 of host myhost, using the Thin driver.

OracleDataSource ods = new OracleDataSource();
String URL = "jdbc:oracle:thin:@//myhost:1521/orcl",
ods.setURL(URL);
ods.setUser("scott");
ods.setPassword("tiger");
Connection conn = ods.getConnection();

4.1.2.2 Specifying a Database URL That Includes User Name and Password

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

String URL = "jdbc:oracle:oci:scott/tiger@myTNSEntry");
ods.setURL(URL);
Connection conn = ods.getConnection();

If you want to connect using the Thin driver you must specify the port number. 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 service identifier is orcl:

String URL = "jdbc:oracle:thin:scott/tiger@//myhost:1521/orcl"); 
ods.setURL(URL);
Connection conn = ods.getConnection();

4.1.2.3 Supported Connection Properties

Table 4-2 lists the connection properties that Oracle JDBC drivers support.

See Table 23-2, "OCI Driver Client Parameters for Encryption and Integrity" and Table 23-2, "OCI Driver Client Parameters for Encryption and Integrity" for descriptions of encryption and integrity drivers.

4.1.2.4 Using Roles for Sys Logon

To specify the role (mode) for sys logon, use the internal_logon connection property. (See Table 4-2, "Connection Properties Recognized by Oracle JDBC Drivers", for a complete description of this connection property.) To logon as sys, set the internal_logon connection property to sysdba or sysoper.

For a bequeath connection, we can get a connection as "sys" by setting the internal_logon property. For a remote connection, we need additional password file setting procedures.

4.1.2.5 Bequeath Connection and Sys Logon

The following example illustrates how to use the internal_logon and sysdba arguments to specify sys logon. This example works regardless of the database's national-language settings.

/** Example of bequeath connection **/
import java.sql.*;
import oracle.jdbc.*;
import oracle.jdbc.pool.*;
 
// create an OracleDataSource instance
OracleDataSource ods = new OracleDataSource();
 
// set neccessary properties
java.util.Properties prop = new java.util.Properties();
prop.put("user", "sys");
prop.put("password", "sys");
prop.put("internal_logon", "sysdba");
ods.setConnectionProperties(prop);
 
// the url for bequeath connection
String url = "jdbc:oracle:oci8:@";
ods.setURL(url);
 
// retrieve the connection
Connection conn = ods.getConnection();
... 

4.1.2.6 Remote Connection

Password file pre-procedures are needed for getting connected to a remote database as user SYS, because the Oracle database security system requires a password file for remote connections as an administrator.

1. Set a password file on the server side, or on the remote database, using the password utility orapwd. You can add a password file for user sys as follows:

   (UNIX) orapwd file=$ORACLE_HOME/dbs/orapw password=sys entries=200
   (WINDOWS) orapwd file=$ORACLE_HOME\database\PWDsid_name.ora 
      password=sys entries=200
   
   

   Please refer to the Oracle Database Administrator's Guide for its details. file must be the name of the password file. password is the password for the user sys. It can be altered using "alter user ..." in SQLPlus. You should set entries higher than the number of entries you expect.

   The syntax for the password file name is different on Windows than on Unix.

2. Enable remote login as sysdba. This step grants SYSDBA and SYSOPER system privileges to individual users and lets them connect as themselves.

   Stop the database. Then add the following line to (UNIX) initservice_name.ora (Windows) init.ora:

   remote_login_passwordfile=exclusive
   
   

   The initservice_name.ora file is located at ORACLE_HOME/dbs/and also at ORACLE_HOME/admin/db_name/pfile/. Keep the two files synchronized.

   The init.ora file is located at %ORACLE_BASE%\ADMIN\db_name\pfile\.

3. (Optional) Change the password for the sys user

   SQL> alter user sys identified by sys;
   

4. Verify whether sys has the sysdba privilege. The following message should come up:

   SQL> select * from v$pwfile_users;
   USERNAME                  SYSDB   SYSOP
   ----------------------           ---------    ---------
   SYS                               TRUE    TRUE
   

5. Restart the remote database.

This example works regardless of database's language settings
 /** case of remote connection using sys **/
import java.sql.*;
import oracle.jdbc.*;
import oracle.jdbc.pool.*;
// create an OracleDataSource
OracleDataSource ods = new OracleDataSource();
// set connection properties
java.util.Properties prop = new java.util.Properties();
prop.put("user", "sys");
prop.put("password", "sys");
prop.put("internal_logon", "sysoper");
ods.setConnectionProperties(prop);
// set the url
// the url can use oci driver as well as:
// url = "jdbc:oracle:oci8:@inst1"; the inst1 is a remote database
String url = "jdbc:oracle:thin:@//myHost:1521/service_name";
ods.setURL(url);
// get the connection
Connection conn = ods.getConnection();

4.1.2.7 Properties for Oracle Performance Extensions

Some of these properties are for use with Oracle performance extensions. Setting these properties is equivalent to using corresponding methods on the OracleConnection object, as follows:

• Setting the defaultRowPrefetch property is equivalent to calling setDefaultRowPrefetch().

  See "Oracle Row Prefetching".

• Setting the remarksReporting property is equivalent to calling setRemarksReporting().

  See "DatabaseMetaData TABLE_REMARKS Reporting".

• Setting the defaultBatchValue property is equivalent to calling setDefaultExecuteBatch().

  See "Oracle Update Batching".

//import packages and register the driver
import java.sql.*;
import java.math.*;
import oracle.jdbc.*;
import oracle.jdbc.pool.OracleDataSource;

//specify the properties object
java.util.Properties info = new java.util.Properties();
info.put ("user", "scott");
info.put ("password", "tiger");
info.put ("defaultRowPrefetch","20");
info.put ("defaultBatchValue", "5");

//specify the datasource object 
OracleDataSource ods = new OracleDataSource();
ods.setURL("jdbc:oracle:thin:@//myhost:1521/orcl");
ods.setUser("scott");
ods.setPassword("tiger");
...

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

For information about custom Java classes and the SQLData and ORAData interfaces, see "Mapping Oracle Objects" and "Creating and Using Custom Object Classes for Oracle Objects". (Although these sections focus on custom Java classes for user-defined objects, there is some general information about other kinds of custom Java classes as well.)

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

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

4.4.1.2 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 get LONG data with getUnicodeStream(), you get a stream of Unicode characters in the UTF-16 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.

For more information on how the drivers return data based on character set, see Chapter 12, "Globalization Support".

Table 4-4 summarizes LONG and LONG RAW data conversions for each stream type.

4.4.1.3 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 LONG RAW data; the second version uses the getBytes() method.

-- SQL code:
create table streamexample (NAME varchar2 (256), GIFDATA long raw);
insert into streamexample values ('LESLIE', '00010203040506070809');

ResultSet rset = stmt.executeQuery 
                 ("select GIFDATA from streamexample where NAME='LESLIE'");

// get first row
if (rset.next())
{
    // Get the GIF data as a stream from Oracle to the client
    InputStream gif_data = rset.getBinaryStream (1);
   try
   {
      FileOutputStream file = null;
      file = new FileOutputStream ("leslie.gif");
      int chunk;
      while ((chunk = gif_data.read()) != -1)
         file.write(chunk);
   }
   catch (Exception e)
   {
      String err = e.toString();
      System.out.println(err);
   }
   finally
   {
      if file != null()
         file.close();
   }
} 

ResultSet rset2 = stmt.executeQuery 
                  ("select GIFDATA from streamexample where NAME='LESLIE'"); 

// get first row
if (rset2.next())
{
   // Get the GIF data as a stream from Oracle to the client
   byte[] bytes = rset2.getBytes(1);
   try
   {
      FileOutputStream file = null;
      file = new FileOutputStream ("leslie2.gif");
      file.write(bytes);
   }
   catch (Exception e)
   {
      String err = e.toString();
      System.out.println(err);
   }
   finally
   {
      if file != null()
         file.close();
   }
}

4.4.1.4 Avoiding Streaming for LONG or LONG RAW

The JDBC driver automatically streams any LONG and LONG RAW 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 LONG RAW 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);

4.4.3.1 Streaming Example with Multiple Columns

Consider the following query:

ResultSet rset = stmt.executeQuery
        ("select DATECOL, LONGCOL, NUMBERCOL from TABLE");
while rset.next()
{
   //get the date data
   java.sql.Date date = rset.getDate(1);

   // get the streaming data
   InputStream is = rset.getAsciiStream(2); 

   // Open a file to store the gif data
   FileOutputStream file = new FileOutputStream ("ascii.dat");

   // Loop, reading from the ascii stream and 
   // write to the file
   int chunk;
   while ((chunk = is.read ()) != -1)
      file.write(chunk);
   // Close the file
   file.close();

   //get the number column data
   int n = rset.getInt(3);  
}

The incoming data for each row has the following shape:

<a date><the characters of the long column><a number>

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

4.4.3.2 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:

ResultSet rset = stmt.executeQuery
        ("select DATECOL, LONGCOL, NUMBERCOL from TABLE");

while rset.next()
{
   //get the date
   java.sql.Date date = rset.getDate(1);
   
   // access the stream data and discard it with close()
   InputStream is = rset.getAsciiStream(2);
   is.close();   
   
   // get the number column data
   int n = rset.getInt(3); 
}

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

For a complete discussion of how to use streaming CLOB and BLOB data, see "Reading and Writing BLOB and CLOB Data". CLOB and BLOB data may also be streamed with the same mechanism as for LONG and LONG RAW. See "Shortcuts For Inserting and Retrieving CLOB Data".

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

For a complete discussion of how to use streaming BFILE data, see "Reading BFILE Data".

4.4.6.1 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 the NUMBER 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.

The second point is illustrated in the following example:

ResultSet rset = stmt.executeQuery
        ("select DATECOL, LONGCOL, NUMBERCOL from TABLE");
while rset.next()
{
   int n = rset.getInt(3);  // This discards the streaming data
   InputStream is = rset.getAsciiStream(2);
                            // Raises an error: stream closed.
}

If you get the stream but do not use it before you get the NUMBER column, the stream still closes automatically:

ResultSet rset = stmt.executeQuery
                 ("select DATECOL, LONGCOL, NUMBERCOL from TABLE");
while rset.next()
{
   InputStream is = rset.getAsciiStream(2); // Get the stream
   int n = rset.getInt(3);
   // Discards streaming data and closes the stream
}
int c = is.read(); // c is -1: no more characters to read-stream closed

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

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