oracle.jdbc
Interface OracleStatement

All Known Subinterfaces:

OracleCallableStatement, OraclePreparedStatement

public abstract interface OracleStatement

extends java.sql.Statement

This interface defines the Oracle extensions to the standard JDBC interface java.sql.Statement and is the superinterface of the OraclePreparedStatement and OracleCallableStatement interfaces. You can use java.sql.Statement in your application where you do not make use of the Oracle extensions. However, when your application uses the Oracle extensions to java.sql.Statement you must cast your statement object to the type oracle.jdbc.OracleStatement. Although the type by which the java compiler will identify the statement object is changed, the object itself is unchanged.

Extended functionality includes support for settings flags and options for Oracle performance extensions on a statement-by-statements basis, as opposed to the OracleConnection interface that sets these on a connection-wide basis.

Since:

8.1.7

See Also:

Connection.createStatement

Field Summary

static int	EXPLICIT
static int	IMPLICIT
static int	NEW

Method Summary

void	clearDefines() Lets you clear previously defined types for the define-columns of this statement.
void	closeWithKey(java.lang.String key) The underlying cursor is not closed and the Statement handle is cached on the Key.
int	creationState() Deprecated.
void	defineColumnType(int column_index, int type) Defines the type you will use to retrieve data from a particular database table column.
void	defineColumnType(int column_index, int type, int max_size) Defines the type you will use to retrieve data from a particular database table column and the maximum size of data you want.
void	defineColumnType(int column_index, int typeCode, java.lang.String typeName) Defines the type you will use to retrieve data from a particular database table column and specifies the column name.
void	defineColumnTypeBytes(int column_index, int type, int max_size) Defines the type you will use to retrieve data from a particular database table column and the maximum size of data you want, specifying the maximum size in bytes, not characters.
void	defineColumnTypeChars(int column_index, int type, int max_size) Defines the type you will use to retrieve data from a particular database table column and the maximum size of data you want, specifying the maximum size in characters, rather than bytes.
int	getRowPrefetch() Retrieves the value or row prefetch for all result sets created from this statement.
boolean	isNCHAR(int index) isNCHAR (int)
void	setResultSetCache(OracleResultSetCache cache) Lets you use your own client-side cache implementation for scrollable result sets.
void	setRowPrefetch(int value) Sets the value of row prefetch for all result sets created from this statement.

Methods inherited from interface java.sql.Statement

addBatch, cancel, clearBatch, clearWarnings, close, execute, executeBatch, executeQuery, executeUpdate, getConnection, getFetchDirection, getFetchSize, getMaxFieldSize, getMaxRows, getMoreResults, getQueryTimeout, getResultSet, getResultSetConcurrency, getResultSetType, getUpdateCount, getWarnings, setCursorName, setEscapeProcessing, setFetchDirection, setFetchSize, setMaxFieldSize, setMaxRows, setQueryTimeout

Field Detail

NEW

public static final int NEW

IMPLICIT

public static final int IMPLICIT

EXPLICIT

public static final int EXPLICIT

Method Detail

clearDefines

public void clearDefines()
                  throws java.sql.SQLException

Lets you clear previously defined types for the define-columns of this statement. This is usefull if you want to reuse this statement for a different query.

After calling clearDefines, you can either perform defines by calling defineColumnType/defineColumnTypeChars or let the driver use the default defines for the table.

Throws:

java.sql.SQLException - if an error occurs

defineColumnType

public void defineColumnType(int column_index,
                             int type)
                      throws java.sql.SQLException

Defines the type you will use to retrieve data from a particular database table column.

Before executing a query you may choose to inform JDBC of the type you will use for fetching data from columns. This will save 2 roundtrips to the RDBMS when executing the query as otherwise the JDBC driver has to ask the RDBMS for the column types.

If you decide to define column types you have to declare the types of exactly all columns in the query. If definition are missing or too many definitions are provided executeQuery will fail with a SQLException.

The following example illustrates the use of this feature:

  
Connection conn = DriverManager.getConnection("jdbc:oracle:oci8:","scott","tiger");
Statement stmt = conn.createStatement();
// Ask for the column as a string:
// Avoid a round trip to get the column type.
// Convert from number to string on the server.

((OracleStatement)stmt).defineColumnType(1, Types.VARCHAR);

ResultSet rset = stmt.executeQuery("select empno from emp");
while (rset.next() )
System.out.println(rset.getString(1));
stmt.close();

Notes:

• All columns can be defined to their "natural" JDBC types; in most cases, they can be defined to the Types.CHAR or Types.VARCHAR typecode. You can also use the OracleTypes typecodes. The type can also be different from the native type of the column. Appropriate conversions will be done. A subsequent call to getObject() for this column will return the supplied type rather than the native type, however.
• When reading the value of an out parameter you don't have to use the getXXX method whose Java type XXX corresponds to the parameter's registered SQL type. It's just faster if you do as the data has already been converted to the right type.

Parameters:

column_index - index of column (first is 1)

type - type to be assigned to column

Throws:

java.sql.SQLException - if an error occurs

See Also:

defineColumnType(int,int,int), clearDefines

defineColumnType

public void defineColumnType(int column_index,
                             int type,
                             int max_size)
                      throws java.sql.SQLException

Defines the type you will use to retrieve data from a particular database table column and the maximum size of data you want. Depending on the value of the dataSizeUnits connection property, max_size will be measured in bytes or characters.

defineColumnTypeBytes

public void defineColumnTypeBytes(int column_index,
                                  int type,
                                  int max_size)
                           throws java.sql.SQLException

Defines the type you will use to retrieve data from a particular database table column and the maximum size of data you want, specifying the maximum size in bytes, not characters. The use of this method is recommended if the column is VARCHAR or equivalent and you know the length limit. To specify the maximum length in characters, use the defineColumnTypeChars method.

Similarly to defineColumnType(int,int), before executing a query you may choose to inform JDBC of the type you will use for fetching data from columns and the maximum length of data you desire. Each type of data has a default maximum length. This method is useful if you do not wish to get the full default length of data.

If you decide to define column types you have to declare the types of exactly all columns in the query. If definitions are missing or too many definitions are provided executeQuery will fail with a SQLException.

Specifically, the size of the data returned will be the minimum of:

• the maximum field size set in this method
• the maximum field size set in setMaxFieldSize()
• the natural maximum size of the datatype

The following example illustrates the use of this feature:

Statement stmt = conn.createStatement ();
// Call DefineColumnType to specify that the column will be
// retrieved as a String to avoid conversion from NUMBER to String
// on the client side.  This also avoids a round-trip to the
// database to get the column type.
//
// Specify the maximum length of the String.  The values obtained for
// this column will not exceed this length.
((OracleStatement)stmt).defineColumnType (1, Types.VARCHAR, 7);
ResultSet rset = stmt.executeQuery ("select empno from emp");
while (rset.next ())
{
System.out.println (rset.getString (1));
}

Notes:

• All columns can be defined to their "natural" JDBC types; in most cases, they can be defined to the Types.CHAR or Types.VARCHAR typecode. You can also use the OracleTypes typecodes. The type can also be different from the native type of the column. Appropriate conversions will be done. A subsequent call to getObject() for this column will return the supplied type rather than the native type, however.
• When reading the value of an out parameter you don't have to use the getXXX method whose Java type XXX corresponds to the parameter's registered SQL type. It's just faster if you do as the data has already been converted to the right type.

Parameters:

column_index - index of column (first is 1)

type - type to be assigned to column

max_size - maximum length of data for this column This value is specified in bytes, not characters.

Throws:

java.sql.SQLException - if an error occurs

See Also:

defineColumnType(int,int), clearDefines

defineColumnTypeChars

public void defineColumnTypeChars(int column_index,
                                  int type,
                                  int max_size)
                           throws java.sql.SQLException

Defines the type you will use to retrieve data from a particular database table column and the maximum size of data you want, specifying the maximum size in characters, rather than bytes. For non-character types, this entrypoint behaves the same as defineColumnType(int,int,int).

Similarly to defineColumnType(int,int), before executing a query you may choose to inform JDBC of the type you will use for fetching data from columns and the maximum length of data you desire. Each type of data has a default maximum length. This method is useful if you do not wish to get the full default length of data.

If you decide to define column types you have to declare the types of exactly all columns in the query. If definitions are missing or too many definitions are provided executeQuery will fail with a SQLException.

Specifically, the size of the data returned will be the minimum of:

• the maximum field size set in this method
• the maximum field size set in setMaxFieldSize()
• the natural maximum size of the datatype

Notes:

• All columns can be defined to their "natural" JDBC types; in most cases, they can be defined to the Types.CHAR or Types.VARCHAR typecode. You can also use the OracleTypes typecodes. The type can also be different from the native type of the column. Appropriate conversions will be done. A subsequent call to getObject() for this column will return the supplied type rather than the native type, however.
• When reading the value of an out parameter you don't have to use the getXXX method whose Java type XXX corresponds to the parameter's registered SQL type. It's just faster if you do as the data has already been converted to the right type.

Parameters:

column_index - index of column (first is 1)

type - type to be assigned to column

max_size - maximum length of data for this column. This value is specified in characters, not bytes.

Throws:

java.sql.SQLException - if an error occurs

See Also:

defineColumnType(int,int), clearDefines

defineColumnType

public void defineColumnType(int column_index,
                             int typeCode,
                             java.lang.String typeName)
                      throws java.sql.SQLException

Defines the type you will use to retrieve data from a particular database table column and specifies the column name. This method should be used for structured object, object reference and array columns (OracleTypes.STRUCT, OracleTypes.REF_TYPE and OracleTypes.ARRAY). The parameter typeName is ignored for other type codes.

Similarly to defineColumnType(int,int), before executing a query you may choose to inform JDBC of the type you will use for fetching data from columns. This will save 2 roundtrips to the RDBMS when executing the query as otherwise the JDBC driver has to ask the RDBMS for the column types.

If you decide to define column types you have to declare the types of exactly all columns in the query. If definition are missing or too many definitions are provided executeQuery will fail with a SQLException.

Parameters:

column_index - index of column (first is 1)

typeCode - type code for this column.

typeName - specifies the fully-qualified name of the column

Throws:

java.sql.SQLException - if an error occurs

See Also:

defineColumnType(int,int), clearDefines

getRowPrefetch

public int getRowPrefetch()

Retrieves the value or row prefetch for all result sets created from this statement.

The row-prefetching feature associates an integer row-prefetch setting with a given statement object. JDBC fetches that number of rows at a time from the database during the query. That is, JDBC will fetch N rows that match the query criteria and bring them all back to the client at once, where N is the prefetch setting. Then, once your next calls have run through those N rows, JDBC will go back to fetch the next N rows that match the criteria.

You can set the number of rows to prefetch for this particular Oracle statement (any type of statement). You can also reset the default number of rows that will be prefetched for all statements in your connection with the OracleConnection.setDefaultRowPrefetch method.

Returns:

the row prefetch value

See Also:

setRowPrefetch, OracleConnection.setDefaultRowPrefetch

setResultSetCache

public void setResultSetCache(OracleResultSetCache cache)
                       throws java.sql.SQLException

Lets you use your own client-side cache implementation for scrollable result sets. Create your own class that implements the OracleResultSetCache interface, then use this method to input an instance of your result set cache class to this statement object that will create the result set.

Parameters:

cache - is a OracleResultSetCache instance

Throws:

java.sql.SQLException - if cache is null or if an error occurs when calling close() on cache

setRowPrefetch

public void setRowPrefetch(int value)
                    throws java.sql.SQLException

Sets the value of row prefetch for all result sets created from this statement. It overrides the prefetch value set from the connection, for this particular statement.

The row-prefetching feature associates an integer row-prefetch setting with a given statement object. JDBC fetches that number of rows at a time from the database during the query. That is, JDBC will fetch N rows that match the query criteria and bring them all back to the client at once, where N is the prefetch setting. Then, once your next calls have run through those N rows, JDBC will go back to fetch the next N rows that match the criteria.

The row_prefetch will be turned back to 1 automatically by the driver if any of the select-column types is streaming (long data or long raw data). This is overrides any value the user might set. Also, this will be done regardless of wether the streaming columns are read or not.

Notes :

• If a column of a result set is of datatype LONG or LONG RAW (that is, the streaming types), JDBC changes the statement's row-prefetch setting to 1, even if you never actually read a value of either of those types.
• Do not mix the JDBC 2.0 fetch size API and the Oracle row-prefetching API in your application. You can use one or the other, but not both.

Parameters:

value - the number of rows to prefetch

Throws:

java.sql.SQLException - if the argument value is <=0

See Also:

getRowPrefetch, OracleConnection.setDefaultRowPrefetch

closeWithKey

public void closeWithKey(java.lang.String key)
                  throws java.sql.SQLException

The underlying cursor is not closed and the Statement handle is cached on the Key. The Statement is cached as it is and the state, data, and meta-data is not cleared. The same statement can be retrieved with this Key later. Key cannot be null.

Parameters:

key - A key to tag to the statement to be retrieved later

Throws:

java.sql.SQLException - if a database access error occurs

creationState

public int creationState()

Deprecated.

Returns:

Creation Status flag

isNCHAR

public boolean isNCHAR(int index)
                throws java.sql.SQLException

isNCHAR (int)

Parameters:

index - the column index

Returns:

true if the column is of type NCHAR/NVARCHAR/NCLOB false if the column is not of type NCHAR/NVARCHAR/NCLOB

Throws:

java.sql.SQLException -