Skip Headers

Oracle® Database JDBC Developer's Guide and Reference
10g Release 1 (10.1)

Part Number B10979-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page
Previous
Go to next page
Next
View PDF

11 Accessing and Manipulating Oracle Data

This chapter describes data access in oracle.sql.* formats, as opposed to standard Java formats. As described in the previous chapter, the oracle.sql.* formats are a key factor of the Oracle JDBC extensions, offering significant advantages in efficiency and precision in manipulating SQL data.

Using oracle.sql.* formats involves casting your result sets and statements to OracleResultSet, OracleStatement, OraclePreparedStatement, and OracleCallableStatement objects, as appropriate, and using the getOracleObject(), setOracleObject(), getXXX(), and setXXX() methods of these classes (where XXX corresponds to the types in the oracle.sql package).

This chapter covers the following topics:

Data Conversion Considerations

When JDBC programs retrieve SQL data into Java, you can use standard Java types, or you can use types of the oracle.sql package. The classes in this package simply wrap the raw SQL data.

Standard Types Versus Oracle Types

In processing speed and effort, the oracle.sql.* classes usually provide the most efficient way of representing SQL data. These classes store the usual representations of SQL data as byte arrays. They do not reformat the data.

In Oracle 10g, the implementation of the JDBC drivers has been changed in order to improve overall performance. As a result, all character data is converted to Java chars, which are in the UCS2 character set. A result of this is that oracle.sql.CHAR is no longer the most efficient way to access character data. In order to construct a CHAR, JDBC must convert the Java chars to bytes encoded in the appropriate character set. This additional conversion causes a reduction in performance. At this release, unlike earlier versions, the most efficient way to access character data in JDBC is through the Java type 3. It is worth noting that the NUMBER, DATE and other conversions are much faster in 10g Release 1 (10.1) and the performance advantage of using the oracle.sql types is correspondingly less.

Converting SQL NULL Data

Java represents a SQL NULL datum by the Java value null. Java datatypes fall into two categories: primitive types (such as byte, int, float) and object types (class instances). The primitive types cannot represent null. Instead, they store the null as the value zero (as defined by the JDBC specification). This can lead to ambiguity when you try to interpret your results.

In contrast, Java object types can represent null. The Java language defines an object wrapper type corresponding to every primitive type (for example, Integer for int, Float for float) that can represent null. The object wrapper types must be used as the targets for SQL data to detect SQL NULL without ambiguity.

Testing for NULLs

You cannot use a relational operator to compare NULL values with each other or with other values. For example, the following SELECT statement fails if the COMM column contains one or more NULLs. This SELECT does not return any rows.

PreparedStatement pstmt = conn.prepareStatement(
  "SELECT * FROM EMP WHERE COMM = ?"); 
pstmt.setNull(1, java.sql.Types.VARCHAR); 

The next example shows how to compare values for equality when some return values might be NULL. The following code returns all the ENAMES from the EMP table that are NULL, if there is no value of 100 for COMM.

PreparedStatement pstmt = conn.prepareStatement("SELECT ENAME FROM EMP 
  WHERE COMM =? OR  ((COMM IS NULL) AND (? IS NULL))"); 
pstmt.setBigDecimal(1, new BigDecimal(100)); 
pstmt.setNull(2, java.sql.Types.VARCHAR); 

Result Set and Statement Extensions

The JDBC Statement object returns an OracleResultSet object, typed as a java.sql.ResultSet. If you want to apply only standard JDBC methods to the object, keep it as a ResultSet type. However, if you want to use the Oracle extensions on the object, you must cast it to an OracleResultSet type. Although the type by which the Java compiler will identify the object is changed, the object itself is unchanged. All of the OracleResultSet and Statement extensions are in the package oracle.jdbc.OracleResultSet.

For example, assuming you have a standard Statement object stmt, do the following if you want to use only standard JDBC ResultSet methods:

ResultSet rs = stmt.executeQuery("SELECT * FROM emp");

If you need the extended functionality provided by the Oracle extensions to JDBC, you can select the results into a standard ResultSet object, as above, and then cast that object into an OracleResultSet object later.

Similarly, when you want to execute a stored procedure using a callable statement, the JDBC drivers will return an OracleCallableStatement object typed as a java.sql.CallableStatement. If you want to apply only standard JDBC methods to the object, then keep it as a CallableStatement type. However, if you want to use the Oracle extensions on the object, you must cast it to an OracleCallableStatement type. Although the type by which the Java compiler will identify the object is changed, the object itself is unchanged.

You use the standard JDBC java.sql.Connection.prepareStatement() method to create a PreparedStatement object. If you want to apply only standard JDBC methods to the object, keep it as a PreparedStatement type. However, if you want to use the Oracle extensions on the object, you must cast it to an OraclePreparedStatement type. While the type by which the Java compiler will identify the object is changed, the object itself is unchanged.

Key extensions to the result set and statement classes include getOracleObject() and setOracleObject() methods that you can use to access and manipulate data in oracle.sql.* formats, instead of standard Java formats. For more information, see the next section: "Comparison of Oracle get and set Methods to Standard JDBC ".

Comparison of Oracle get and set Methods to Standard JDBC

This section describes get and set methods, particularly the JDBC standard getObject() and setObject() methods and the Oracle-specific getOracleObject() and setOracleObject() methods, and how to access data in oracle.sql.* format compared with Java format.

Although there are specific getXXX() methods for all the Oracle SQL types (as described in "Other getXXX() Methods"), you can use the general get methods for convenience or simplicity, or if you are not certain in advance what type of data you will receive.

Standard getObject() Method

The standard JDBC getObject() method of a result set or callable statement returns data into a java.lang.Object object. The format of the data returned is based on its original type, as follows:

  • For SQL datatypes that are not Oracle-specific, getObject() returns the default Java type corresponding to the column's SQL type, following the mapping specified in the JDBC specification.

  • For Oracle-specific datatypes (such as ROWID, discussed in "Oracle ROWID Type"), getObject() returns an object of the appropriate oracle.sql.* class (such as oracle.sql.ROWID).

  • For Oracle objects, getObject() returns an object of the Java class specified in your type map. (Type maps specify the correlation between Java classes and database SQL types and are discussed in "Understanding Type Maps for SQLData Implementations".) The getObject(parameter_index) method uses the connection's default type map. The getObject(parameter_index, map) enables you to pass in a type map. If the type map does not provide a mapping for a particular Oracle object, then getObject() returns an oracle.sql.STRUCT object.

For more information on getObject() return types, see Table 11-1, "Summary of getObject() and getOracleObject() Return Types ".

Oracle getOracleObject() Method

If you want to retrieve data from a result set or callable statement into an oracle.sql.* object, then cast your result set to an OracleResultSet type or your callable statement to an OracleCallableStatement type, and use the getOracleObject() method.

When you use getOracleObject(), the data will be of the appropriate oracle.sql.* type and is returned into an oracle.sql.Datum object (the oracle.sql type classes extend Datum). The signature for the method is:

public oracle.sql.Datum getOracleObject(int parameter_index)

When you have retrieved data into a Datum object, you can use the standard Java instanceof operator to determine which oracle.sql.* type it really is.

For more information on getOracleObject() return types, see Table 11-1, "Summary of getObject() and getOracleObject() Return Types ".

Example: Using getOracleObject() with a ResultSet

The following example creates a table that contains a column of character data (in this case, a row number) and a column containing a BFILE locator. A SELECT statement retrieves the contents of the table into a result set. The getOracleObject() then retrieves the CHAR data into the char_datum variable and the BFILE locator into the bfile_datum variable. Note that because getOracleObject() returns a Datum object, the results must be cast to CHAR and BFILE, respectively.

stmt.execute ("CREATE TABLE bfile_table (x varchar2 (30), b bfile)");
stmt.execute 
    ("INSERT INTO bfile_table VALUES ('one', bfilename ('TEST_DIR', 'file1'))");

ResultSet rset = stmt.executeQuery ("SELECT * FROM bfile_table");
while (rset.next ())
{
   CHAR char_datum = (CHAR) ((OracleResultSet)rset).getOracleObject (1);
   BFILE bfile_datum = (BFILE) ((OracleResultSet)rset).getOracleObject (2);
   ...
} 

Example: Using getOracleObject() in a Callable Statement

The following example prepares a call to the procedure myGetDate(), which associates a character string (in this case a name) with a date. The program passes the string SCOTT to the prepared call and registers the DATE type as an output parameter. After the call is executed, getOracleObject() retrieves the date associated with the name SCOTT. Note that because getOracleObject() returns a Datum object, the results are cast to a DATE object.

OracleCallableStatement cstmt = (OracleCallableStatement)conn.prepareCall
                                   ("begin myGetDate (?, ?); end;");

cstmt.setString (1, "SCOTT");
cstmt.registerOutParameter (2, Types.DATE);
cstmt.execute ();

DATE date = (DATE) ((OracleCallableStatement)cstmt).getOracleObject (2);
...

Summary of getObject() and getOracleObject() Return Types

Table 11-1 summarizes the information in the preceding sections, "Standard getObject() Method" and "Oracle getOracleObject() Method".

This table lists the underlying return types for each method for each Oracle SQL type, but keep in mind the signatures of the methods when you write your code:

  • getObject(): Always returns data into a java.lang.Object instance.

  • getOracleObject(): Always returns data into an oracle.sql.Datum instance.

You must cast the returned object to use any special functionality (see "Casting Your get Method Return Values").

Table 11-1 Summary of getObject() and getOracleObject() Return Types

Oracle SQL Type getObject() Underlying Return Type getOracleObject() Underlying Return Type
CHAR String oracle.sql.CHAR
VARCHAR2 String oracle.sql.CHAR
LONG String oracle.sql.CHAR
NUMBER java.math.BigDecimal or java.lang.Double if j2eeCompliant flag is set to true oracle.sql.NUMBER
RAW byte[] oracle.sql.RAW
LONGRAW byte[] oracle.sql.RAW
DATE java.sql.Date oracle.sql.DATE
TIMESTAMP java.sql.Timestamp oracle.sql.TIMESTAMP
ROWID oracle.sql.ROWID oracle.sql.ROWID
REF CURSOR java.sql.ResultSet (not supported)
BLOB oracle.sql.BLOB oracle.sql.BLOB
CLOB oracle.sql.CLOB oracle.sql.CLOB
BFILE oracle.sql.BFILE oracle.sql.BFILE
Oracle object class specified in type map

or oracle.sql.STRUCT (if no type map entry)

oracle.sql.STRUCT
Oracle object reference oracle.sql.REF oracle.sql.REF
collection (varray or nested table) oracle.sql.ARRAY oracle.sql.ARRAY

For information on type compatibility between all SQL and Java types, see Table 24-1, "Valid SQL Datatype-Java Class Mappings ".

Other getXXX() Methods

Standard JDBC provides a getXXX() for each standard Java type, such as getByte(), getInt(), getFloat(), and so on. Each of these returns exactly what the method name implies (a byte, an int, a float, and so on).

In addition, the OracleResultSet and OracleCallableStatement classes provide a full complement of getXXX() methods corresponding to all the oracle.sql.* types. Each getXXX() method returns an oracle.sql.XXX object. For example, getROWID() returns an oracle.sql.ROWID object.

Some of these extensions are taken from the JDBC 2.0 specification. They return objects of type java.sql.* instead of oracle.sql.*. For example, compare the following method names and return types:

java.sql.Blob getBlob(int parameter_index) 

oracle.sql.BLOB getBLOB(int parameter_index)  

Although there is no particular performance advantage in using the specific getXXX() methods, they can save you the trouble of casting, because they return specific object types.

Return Types and Input Parameter Types of getXXX() Methods

Table 11-2 summarizes the underlying return types and the input parameter types for each getXXX() method, and notes which are Oracle extensions under JDK 1.2.x. You must cast to an OracleResultSet or OracleCallableStatement to use methods that are Oracle extensions.

Table 11-2 Summary of getXXX() Return Types

Method Underlying Return Type Signature Type Oracle Ext for JDK 1.2.x?
getArray() oracle.sql.ARRAY java.sql.Array No
getARRAY() oracle.sql.ARRAY oracle.sql.ARRAY Yes
getAsciiStream() java.io.InputStream java.io.InputStream No
getBfile() oracle.sql.BFILE oracle.sql.BFILE Yes
getBFILE() oracle.sql.BFILE oracle.sql.BFILE Yes
getBigDecimal() (see Notes section below) java.math.BigDecimal java.math.BigDecimal No
getBinaryStream() java.io.InputStream java.io.InputStream No
getBlob() oracle.sql.BLOB java.sql.Blob No
getBLOB oracle.sql.BLOB oracle.sql.BLOB Yes
getBoolean() (see Notes section below) boolean boolean No
getByte() byte byte No
getBytes() byte[] byte[] No
getCHAR() oracle.sql.CHAR oracle.sql.CHAR Yes
getCharacterStream() java.io.Reader java.io.Reader No
getClob() oracle.sql.CLOB java.sql.Clob No
getCLOB() oracle.sql.CLOB oracle.sql.CLOB Yes
getDate() java.sql.Date java.sql.Date No
getDATE() oracle.sql.DATE oracle.sql.DATE Yes
getDouble() double double No
getFloat() float float No
getInt() int int No
getLong() long long No
getNUMBER() oracle.sql.NUMBER oracle.sql.NUMBER Yes
getOracleObject() subclasses of oracle.sql.Datum oracle.sql.Datum Yes
getRAW() oracle.sql.RAW oracle.sql.RAW Yes
getRef() oracle.sql.REF java.sql.Ref No
getREF() oracle.sql.REF oracle.sql.REF Yes
getROWID() oracle.sql.ROWID oracle.sql.ROWID Yes
getShort() short short No
getString() String String No
getSTRUCT() oracle.sql.STRUCT. oracle.sql.STRUCT Yes
getTime() java.sql.Time java.sql.Time No
getTimestamp() java.sql.Timestamp java.sql.Timestamp No
getUnicodeStream() java.io.InputStream java.io.InputStream No

Special Notes about getXXX() Methods

This section provides additional details about some getXXX() methods.

getBigDecimal() Note

JDBC 2.0 supports simplified method signatures for the getBigDecimal() method. The previous input signatures were:

(int columnIndex, int scale) or (String columnName, int scale)

The new input signature is simply:

(int columnIndex) or (String columnName)

The scale parameter, used to specify the number of digits to the right of the decimal, is no longer necessary. The Oracle JDBC drivers retrieve numeric values with full precision.

getBoolean() Note

Because there is no BOOLEAN database type, when you use getBoolean() a datatype conversion always occurs. The getBoolean() method is supported only for numeric columns (BIT, TINYINT, SMALLINT, INTEGER, BIGINT, REAL, FLOAT, DOUBLE, DECIMAL, NUMERIC, CHAR, VARCHAR, or LONGVARCHAR). When applied to these columns, getBoolean() interprets any zero (0) value as false, and any other value as true. When applied to any other sort of column, getBoolean() raises the exception java.lang.NumberFormatException. Check with Longxing!

Casting Your get Method Return Values

As described in "Standard getObject() Method", the return type of the Oracle implementation of getObject() is java.lang.Object. The returned value is an instance of a subclass of java.lang.Object. Similarly, the return type of getOracleObject() is oracle.sql.Datum. You normally cast the returned object to the appropriate class so that you could use particular methods and functionality of that class.

In addition, you have the option of using a specific getXXX() method instead of the generic getObject() or getOracleObject() methods. The getXXX() methods enable you to avoid casting, because the return type of getXXX() corresponds to the type of object returned. For example, getCLOB() returns an oracle.sql.CLOB instance, as opposed to a java.lang.Object instance.

Example: Casting Return Values

This example assumes that you have fetched data of type NUMBER into a result set (where it is in column 1). Because you want to manipulate the NUMBER data without losing precision, cast your result set to an OracleResultSet, and use getOracleObject() to return the NUMBER data in oracle.sql.* format. If you do not cast your result set, you have to use getObject(), which returns your character data into a Java Float and loses some of the precision of your SQL data.

The getOracleObject() method returns an oracle.sql.NUMBER object into an oracle.sql.Datum return variable unless you cast the output. Cast the getOracleObject() output to oracle.sql.NUMBER if you want to use a NUMBER return variable and any of the special functionality of that class.

NUMBER x = (NUMBER)ors.getOracleObject(1);

Alternatively, you can return the object into a generic oracle.sql.Datum return variable and cast it later when you must use the NUMBER getCharacterSet() method.

Datum rawdatum = ors.getOracleObject(1);
...
CharacterSet cs = ((NUMBER)rawdatum).getCharacterSet();

This uses the getCharacterSet() method of oracle.sql.NUMBER. The getCharacterSet() method is not defined on oracle.sql.Datum and would not be reachable without the cast.

Standard setObject() and Oracle setOracleObject() Methods

Just as there is a standard getObject() and Oracle-specific getOracleObject() in result sets and callable statements for retrieving data, there is also a standard setObject() and an Oracle-specific setOracleObject() in Oracle prepared statements and callable statements for updating data. The setOracleObject() methods take oracle.sql.* input parameters.

To bind standard Java types to a prepared statement or callable statement, use the setObject() method, which takes a java.lang.Object as input. The setObject() method does support a few of the oracle.sql.* types—it has been implemented so that you can also input instances of the oracle.sql.* classes that correspond to JDBC 2.0-compliant Oracle extensions: BLOB, CLOB, BFILE, STRUCT, REF, and ARRAY.

To bind oracle.sql.* types to a prepared statement or callable statement, use the setOracleObject() method, which takes an oracle.sql.Datum (or any subclass) as input. To use setOracleObject(), you must cast your prepared statement or callable statement to an OraclePreparedStatement or OracleCallableStatement object.

Example: Using setObject() and setOracleObject() in a Prepared Statement

This example assumes that you have fetched character data into a standard result set (where it is in column 1), and you want to cast the results to an OracleResultSet so that you can use Oracle-specific formats and methods. Because you want to use the data as oracle.sql.CHAR format, cast the results of the getOracleObject() (which returns type oracle.sql.Datum) to CHAR. Similarly, because you want to manipulate the data in column 2 as strings, cast the data to a Java String type (because getObject() returns data of type Object). In this example, rs represents the result set, charVal represents the data from column 1 in oracle.sql.CHAR format, and strVal represents the data from column 2 in Java String format.

CHAR charVal=(CHAR)((OracleResultSet)rs).getOracleObject(1);
String strVal=(String)rs.getObject(2);
...

For a prepared statement object ps, the setOracleObject() method binds the oracle.sql.CHAR data represented by the charVal variable to the prepared statement. To bind the oracle.sql.* data, the prepared statement must be cast to an OraclePreparedStatement. Similarly, the setObject() method binds the Java String data represented by the variable strVal.

PreparedStatement ps= conn.prepareStatement("text_of_prepared_statement");
((OraclePreparedStatement)ps).setOracleObject(1,charVal);
ps.setObject(2,strVal);

Other setXXX() Methods

As with getXXX() methods, there are several specific setXXX() methods. Standard setXXX() methods are provided for binding standard Java types, and Oracle-specific setXXX() methods are provided for binding Oracle-specific types.

Similarly, there are two forms of the setNull() method:

  • void setNull(int parameterIndex, int sqlType)

    This is specified in the standard java.sql.PreparedStatement interface. This signature takes a parameter index and a SQL typecode defined by the java.sql.Types or oracle.jdbc.OracleTypes class. Use this signature to set an object other than a REF, ARRAY, or STRUCT to NULL.

  • void setNull(int parameterIndex, int sqlType, String sql_type_name)

    With JDBC 2.0, this signature is also specified in the standard java.sql.PreparedStatement interface. This method takes a SQL type name in addition to a parameter index and a SQL type code. Use this method when the SQL typecode is java.sql.Types.REF, ARRAY, or STRUCT. (If the typecode is other than REF, ARRAY, or STRUCT, then the given SQL type name is ignored.)

Similarly, the registerOutParameter() method has a signature for use with REF, ARRAY, or STRUCT data:

void registerOutParameter
            (int parameterIndex, int sqlType, String sql_type_name)

For binding Oracle-specific types, using the appropriate specific setXXX() methods instead of methods for binding standard Java types may offer some performance advantage.

Input Parameter Types of setXXX() Methods

Table 11-3 summarizes the input types for all the setXXX() methods and notes which are Oracle extensions under JDK 1.2.x. To use methods that are Oracle extensions, you must cast your statement to an OraclePreparedStatement or OracleCallableStatement.

For information on all supported type mappings between SQL and Java, see Table 24-1, "Valid SQL Datatype-Java Class Mappings ".

Table 11-3 Summary of setXXX() Input Parameter Types

Method Input Parameter Type Oracle Ext for JDK 1.2.x?
setArray() java.sql.Array No
setARRAY() oracle.sql.ARRAY Yes
setAsciiStream() (see Notes section) java.io.InputStream No
setBfile() oracle.sql.BFILE Yes
setBFILE() oracle.sql.BFILE Yes
setBigDecimal() BigDecimal No
setBinaryStream() (see Notes section) java.io.InputStream No
setBlob() java.sql.Blob No
setBLOB() oracle.sql.BLOB Yes
setBoolean() boolean No
setByte() byte No
setBytes() byte[] No
setCHAR() (also see setFixedCHAR() method) oracle.sql.CHAR Yes
setCharacterStream() (see Notes section ) java.io.Reader No
setClob() java.sql.Clob No
setCLOB() oracle.sql.CLOB Yes
setDate() (see Notes section ) java.sql.Date No
setDATE() oracle.sql.DATE Yes
setDouble() double No
setFixedCHAR() (see setFixedCHAR() section) java.lang.String Yes
setFloat() float No
setInt() int No
setLong() long No
setNUMBER() oracle.sql.NUMBER Yes
setRAW() oracle.sql.RAW Yes
setRef() java.sql.Ref No
setREF() oracle.sql.REF Yes
setROWID() oracle.sql.ROWID Yes
setShort() short No
setString() String No
setSTRUCT() oracle.sql.STRUCT Yes
setTime() (see note below) java.sql.Time No
setTimestamp() (see note below) java.sql.Timestamp No
setUnicodeStream() (see note below) java.io.InputStream No

Setter Method Size Limitations

Table 11-4 lists size limitations for the setBytes() and setString() methods for SQL binds. (These limitations do not apply to PL/SQL binds.) For information about how to work around these limits using the stream API, see "Using Streams to Avoid Limits on setBytes() and setString()".

Table 11-4 Size Limitations for setBytes() and setString() Methods

Method Name Size Limit
setBytes() 2000 bytes
setString() 4000 bytes

Setter Methods That Take Additional Input

The following setXXX() methods take an additional input parameter other than the parameter index and the data item itself:

  • setAsciiStream(int paramIndex, InputStream istream, int length)

    Takes the length of the stream, in bytes.

  • setBinaryStream(int paramIndex, InputStream istream, int length)

    Takes the length of the stream, in bytes.

  • setCharacterStream(int paramIndex, Reader reader, int length)

    Takes the length of the stream, in characters.

  • setUnicodeStream(int paramIndex, InputStream istream, int length)

    Takes the length of the stream, in bytes.

The particular usefulness of the setCharacterStream() method is that when a very large Unicode value is input to a LONGVARCHAR parameter, it can be more practical to send it through a java.io.Reader object. JDBC will read the data from the stream as needed, until it reaches the end-of-file mark. The JDBC driver will do any necessary conversion from Unicode to the database character format.


Important:

The preceding stream methods can also be used for LOBs. See "Reading and Writing BLOB and CLOB Data" for more information.

  • setDate(int paramIndex, Date x, Calendar cal)

  • setTime(int paramIndex, Time x, Calendar cal)

  • setTimestamp(int paramIndex, Timestamp x, Calendar cal)

Method setFixedCHAR() for Binding CHAR Data into WHERE Clauses

CHAR data in the database is padded to the column width. This leads to a limitation in using the setCHAR() method to bind character data into the WHERE clause of a SELECT statement—the character data in the WHERE clause must also be padded to the column width to produce a match in the SELECT statement. This is especially troublesome if you do not know the column width.

To remedy this, Oracle has added the setFixedCHAR() method to the OraclePreparedStatement class. This method executes a non-padded comparison.


Note:

  • Remember to cast your prepared statement object to OraclePreparedStatement to use the setFixedCHAR() method.

  • There is no need to use setFixedCHAR() for an INSERT statement. The database always automatically pads the data to the column width as it inserts it.


Example

The following example demonstrates the difference between the setCHAR() and setFixedCHAR() methods.

/* Schema is :
 create table my_table (col1 char(10));
 insert into my_table values ('JDBC');
*/
 PreparedStatement pstmt = conn.prepareStatement 
                    ("select count(*) from my_table where col1 = ?");

 pstmt.setString (1, "JDBC");  // Set the Bind Value
 runQuery (pstmt);             // This will print " No of rows are 0"

 CHAR ch = new CHAR("JDBC      ", null);
 ((OraclePreparedStatement)pstmt).setCHAR(1, ch); // Pad it to 10 bytes
 runQuery (pstmt);             // This will print "No of rows are 1"

 ((OraclePreparedStatement)pstmt).setFixedCHAR(1, "JDBC");
  runQuery (pstmt);            // This will print "No of rows are 1"
 
 void runQuery (PreparedStatement ps)
 {    
   // Run the Query
   ResultSet rs = pstmt.executeQuery ();

   while (rs.next())
     System.out.println("No of rows are " + rs.getInt(1));
   
   rs.close();
   rs = null;
 }

Using Result Set Meta Data Extensions

The oracle.jdbc.OracleResultSetMetaData interface is JDBC 2.0-compliant but does not implement the getSchemaName() and getTableName() methods because underlying protocol does not make this feasible. Oracle does implement many methods to retrieve information about an Oracle result set, however.

Key methods include the following:

The following example uses several of the methods in the OracleResultSetMetadata interface to retrieve the number of columns from the EMP table, and each column's numerical type and SQL type name.

DatabaseMetaData dbmd = conn.getMetaData();
ResultSet rset = dbmd.getTables("", "SCOTT", "EMP", null);

 while (rset.next())
 {
   OracleResultSetMetaData orsmd = ((OracleResultSet)rset).getMetaData();
   int numColumns = orsmd.getColumnCount();
   System.out.println("Num of columns = " + numColumns);

   for (int i=0; i<numColumns; i++)
   {
     System.out.print ("Column Name=" + orsmd.getColumnName (i+1));
     System.out.print (" Type=" + orsmd.getColumnType (i + 1) );
     System.out.println (" Type Name=" + orsmd.getColumnTypeName (i + 1));
  }
}

The program returns the following output:

Num of columns = 5
Column Name=TABLE_CAT Type=12 Type Name=VARCHAR2
Column Name=TABLE_SCHEM Type=12 Type Name=VARCHAR2
Column Name=TABLE_NAME Type=12 Type Name=VARCHAR2
Column Name=TABLE_TYPE Type=12 Type Name=VARCHAR2
Column Name=TABLE_REMARKS Type=12 Type Name=VARCHAR2