oracle.jdbc
Interface OracleStatement

All Superinterfaces:

java.sql.Statement

All Known Subinterfaces:

OracleCallableStatement, OraclePreparedStatement

public 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

Fields inherited from interface java.sql.Statement

CLOSE_ALL_RESULTS, CLOSE_CURRENT_RESULT, EXECUTE_FAILED, KEEP_CURRENT_RESULT, NO_GENERATED_KEYS, RETURN_GENERATED_KEYS, SUCCESS_NO_INFO

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 type, int max_size, short form_of_use) 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	getLobPrefetchSize() Returns the LOB prefetch size.
long	getRegisteredQueryId() Returns the id of the query that has been added in the registration (only for query change notification).
java.lang.String[]	getRegisteredTableNames() Returns the name of the tables that have been added to the registration if any.
int	getRowPrefetch() Retrieves the value or row prefetch for all result sets created from this statement.
boolean	isNCHAR(int index) isNCHAR (int)
void	setDatabaseChangeRegistration(DatabaseChangeRegistration registration) Associate a Database Change Registration object with this statement.
void	setLobPrefetchSize(int value) Overrides the LOB prefetch size for this statement.
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, execute, execute, execute, executeBatch, executeQuery, executeUpdate, executeUpdate, executeUpdate, executeUpdate, getConnection, getFetchDirection, getFetchSize, getGeneratedKeys, getMaxFieldSize, getMaxRows, getMoreResults, getMoreResults, getQueryTimeout, getResultSet, getResultSetConcurrency, getResultSetHoldability, getResultSetType, getUpdateCount, getWarnings, setCursorName, setEscapeProcessing, setFetchDirection, setFetchSize, setMaxFieldSize, setMaxRows, setQueryTimeout

Field Detail

NEW

static final int NEW

See Also:

Constant Field Values

IMPLICIT

static final int IMPLICIT

See Also:

Constant Field Values

EXPLICIT

static final int EXPLICIT

See Also:

Constant Field Values

Method Detail

clearDefines

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

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

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.
Note for the thin driver : You should only specify the size (max_size) for char-type columns and then the unit of max_size is always in java chars.

This method is also extended to set the LOB prefetch size at the column level This setting overrides the default LOB prefetch size that is defined at the connection or statement level. The max_size argument represents in this case the number of bytes to prefetch for a BLOB and chars for a CLOB and the value must be >= 0 and the type must be set to OracleTypes.CLOB for a CLOB column and OracleTypes.BLOB for a BLOB column.

Parameters:

column_index -

type -

max_size -

Throws:

java.sql.SQLException

defineColumnType

void defineColumnType(int column_index,
                      int type,
                      int max_size,
                      short form_of_use)
                      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.
Note for the thin driver : You should only specify the size (max_size) for char-type columns and then the unit of max_size is always in java chars.
Additionally set the "form of use" parameter which may take the value oracle.jdbc.OraclePreparedStatement.FORM_CHAR to specify that the data be in the database character set or oracle.jdbc.OraclePreparedStatement.FORM_NCHAR to specify that the data be in the national character set.

Parameters:

column_index -

type -

max_size -

form_of_use -

Throws:

java.sql.SQLException

Since:

10iR1

defineColumnTypeBytes

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

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

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

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

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

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

getLobPrefetchSize

int getLobPrefetchSize()

Returns the LOB prefetch size. This value can be set at the connection level with the oracle.jdbc.defaultLobPrefetchSize connection property or at the statement level through the setLobPrefetchSize(int) method.

Since:

11.2

See Also:

setLobPrefetchSize

setLobPrefetchSize

void setLobPrefetchSize(int value)
                        throws java.sql.SQLException

Overrides the LOB prefetch size for this statement. With LOB prefetch, meta-data such as the lob length and the chunk size as well as the beginning of the LOB data are sent along with the locator during the regular fetch operation. This has a significant performance impact especially for small LOBs which can potentially be entirely prefetched. The data is then available to the user without having to go through the LOB protocol. Note that this is available only with the Oracle database starting in 11.1.

LOB prefetch is enabled by default (see the oracle.jdbc.defaultLobPrefetchSize connection property which default value is 4k bytes for BLOBs and 4k chars for CLOBs). The LOB prefetch size can be set at the connection level through the property or at the statement level through this method. The statement level setting overrides the setting at the connection level. This setting can also be overriden at the column level through the defineColumnType method where the size represents the number of bytes (or chars for CLOB) to prefetch.

Parameters:

value - must be >= -1. -1 disables the feature. 0 enables LOB prefetch of meta data only (lob length and chunk size). Any value >=0 represents the number of bytes to be prefetched for BLOB and the number of chars for CLOB.

Throws:

java.sql.SQLException - if value < -1

Since:

11.2

See Also:

getLobPrefetchSize, OracleConnection.DEFAULT_LOB_PREFETCH_SIZE

closeWithKey

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

int creationState()

Deprecated.

Returns:

Creation Status flag

isNCHAR

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

setDatabaseChangeRegistration

void setDatabaseChangeRegistration(DatabaseChangeRegistration registration)
                                   throws java.sql.SQLException

Associate a Database Change Registration object with this statement.

Any subsequent queries executed with this statement will be part of the given registration.

If you want this statement to no longer add queries to the registration, call this method again with a 'null' argument. Subsequent queries won't be part of the registration.

Parameters:

registration - can be either a valid registration or 'null'.

Throws:

java.sql.SQLException

Since:

11.1

See Also:

getRegisteredQueryId()

getRegisteredTableNames

java.lang.String[] getRegisteredTableNames()
                                           throws java.sql.SQLException

Returns the name of the tables that have been added to the registration if any.

Throws:

java.sql.SQLException

Since:

11.1

getRegisteredQueryId

long getRegisteredQueryId()
                          throws java.sql.SQLException

Returns the id of the query that has been added in the registration (only for query change notification).

Throws:

java.sql.SQLException

Since:

11.1

See Also:

setDatabaseChangeRegistration(oracle.jdbc.dcn.DatabaseChangeRegistration)