Skip navigation links

Oracle® Database JDBC Java API Reference
11g Release 2

E13995-02


oracle.jdbc
Interface OracleStatement

All Superinterfaces:
java.sql.Statement, java.sql.Wrapper
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, isClosed, isPoolable, setCursorName, setEscapeProcessing, setFetchDirection, setFetchSize, setMaxFieldSize, setMaxRows, setPoolable, setQueryTimeout

 

Methods inherited from interface java.sql.Wrapper
isWrapperFor, unwrap

 

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:

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

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:

Notes:

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 :

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

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

Skip navigation links

Oracle® Database JDBC Java API Reference
11g Release 2

E13995-02


Copyright © 2009, Oracle and/or its affiliates. All rights reserved.