oracle.jdbc

## Interface OracleStatement

All Superinterfaces:
  AutoCloseable, Statement, Wrapper

All Known Subinterfaces:
  OracleCallableStatement, OraclePreparedStatement

    public interface OracleStatement
    extends 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

Fields
`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

All Methods Instance Methods Abstract Methods Default Methods Deprecated Methods
`void` `clearDefines()` Lets you clear previously defined types for the define-columns of this statement.
`void` `closeOnCompletion()` Specifies that this Statement will be closed when all its dependent result sets are closed.
`void` `closeWithKey(String key)` The underlying cursor is not closed and the Statement handle is cached on the Key.
`int` `creationState()` Deprecated.
`void` `defineColumnType(int columnIndex, int type)` Defines the type you will use to retrieve data from a particular database table column.
`void` `defineColumnType(int columnIndex, int type, int lobPrefetchSize)` Defines the type you will use to retrieve data from a particular database table column.
`void` `defineColumnType(int columnIndex, int type, int lobPrefetchSize, short formOfUse)` Deprecated.
`void` `defineColumnType(int columnIndex, int typeCode, String typeName)` Defines the type you will use to retrieve data from a particular database table column and specifies the column type name.
`void` `defineColumnTypeBytes(int columnIndex, int type, int lobPrefetchSize)` Deprecated.
`void` `defineColumnTypeChars(int columnIndex, int type, int lobPrefetchSize)` Deprecated.
`default String` `enquoteIdentifier(String identifier, boolean alwaysQuote)` Returns a SQL identifier.
`default String` `enquoteLiteral(String val)` Returns a `String` enclosed in single quotes.
`default String` `enquoteNCharLiteral(String val)` Returns a `String` enclosed in single quotes and prefixed with 'N'.
`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).
`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)
`default boolean` `isSimpleIdentifier(String identifier)` Retrieves whether `identifier` is a simple SQL identifier.
`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` `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, executeLargeBatch, executeLargeUpdate, executeLargeUpdate, executeLargeUpdate, executeLargeUpdate, executeQuery, executeUpdate, executeUpdate, executeUpdate, executeUpdate, getConnection, getFetchDirection, getFetchSize, getGeneratedKeys, getLargeMaxRows, getLargeUpdateCount, getMaxFieldSize, getMaxRows, getMoreResults, getMoreResults, getQueryTimeout, getResultSet, getResultSetConcurrency, getResultSetHoldability, getResultSetType, getUpdateCount, getWarnings, isClosed, isCloseOnCompletion, isPoolable, setCursorName, setEscapeProcessing, setFetchDirection, setFetchSize, setLargeMaxRows, 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 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:
  `SQLException` - if an error occurs

#### defineColumnType

    void defineColumnType(int columnIndex,
                          int type)
                   throws SQLException

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

For the JDBC-OCI driver and the server-side internal driver, if you decide to use defineColumnType you must declare the types of exactly all columns in the query. For the thin driver, it is not required to define all the columns.

See the JDBC Manual section on Data Interface for LOBs for a description of using defineColumnType to get LOB columns as streams. In some cases this is a large performance gain. The lob prefetch feature makes this less important, however. It is effective for basic lobs, but less so for SecureFile lobs.

BFILE, BLOB, CLOB, or NCLOB data can be read using the same streaming mechanism as for LONG RAW and LONG data. Use defineColumnType(nn, Types.LONGVARBINARY) for BLOB or BFILE, defineColumnType(nn,Types.LONGVARCHAR) for CLOB, or defineColumnType(nn,Types.LONGNVARCHAR) for NCLOB. This produces a stream on the data as if it were a LONG RAW or LONG column. Use defineColumnType( nn, Types.VARBINARY) or defineColumnType(nn, Types.VARCHAR) returns the data as if it were a RAW or VARCHAR2 column with the size limits of those types.

The following example illustrates the use of this feature in the PM sample schema:

        // Ask for the column as a character stream:
        ((OracleStatement)stmt).defineColumnType(1, Types.LONGVARCHAR);

        ResultSet rset = stmt.executeQuery("select PRODUCT_TEXT from ONLINE_MEDIA");
        while (rset.next() )
        System.out.println(rset.getString(1));

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.

Parameters:
  `columnIndex` - index of column (first is 1)
  `type` - type to be assigned to column
Throws:
  `SQLException` - if an error occurs
See Also:
  `defineColumnType(int,int,int)`, `clearDefines`

#### defineColumnType

    void defineColumnType(int columnIndex,
                          int type,
                          int lobPrefetchSize)
                   throws SQLException

Defines the type you will use to retrieve data from a particular database table column. See defineColumnType( int, int) for general information.

In previous releases the third parameter was used to control the buffer sizes used with possible truncation of data. In the current release it is used only to control 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 `lobPrefetchSize` 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:
  `columnIndex` - index of column (first is 1)
  `type` - type to be assigned to column
  `lobPrefetchSize` - for lob column, size of prefetch buffer
Throws:
  `SQLException`
See Also:
  `#java.sql.Statement.setMaxFieldSize`

#### defineColumnType

    void defineColumnType(int columnIndex,
                          int type,
                          int lobPrefetchSize,
                          short formOfUse)
                   throws SQLException

Deprecated.

Deprecated method, please use defineColumnType(int, int, int) with the type such as Types.NCHAR, Types.NVARCHAR, Types.NCLOB.

See defineColumnType( int, int) for general information.

The formOfUse parameter 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:
  `columnIndex` - index of column (first is 1)
  `type` - type to be assigned to column
  `lobPrefetchSize` - for lob column, size of prefetch buffer
  `formOfUse` - flag to select character set.
Throws:
  `SQLException`
Since:
  10iR1

#### defineColumnTypeBytes

    void defineColumnTypeBytes(int columnIndex,
                               int type,
                               int lobPrefetchSize)
                        throws SQLException

Deprecated.
Deprecated method, please use defineColumnType(int, int, int) which is used internally by this method.

Parameters:
  `columnIndex` - index of column (first is 1)
  `type` - type to be assigned to column
  `lobPrefetchSize` - ignored except for lob columns
Throws:
  `SQLException` - if an error occurs
See Also:
  `defineColumnType(int,int,int)`, `clearDefines`

#### defineColumnTypeChars

    void defineColumnTypeChars(int columnIndex,
                               int type,
                               int lobPrefetchSize)
                        throws SQLException

Deprecated.
Deprecated method, please use defineColumnType(int, int, int) which is used internally by this method.

Parameters:
  `columnIndex` - index of column (first is 1)
  `type` - type to be assigned to column
  `lobPrefetchSize` - ignored except for lob columns
Throws:
  `SQLException` - if an error occurs
See Also:
  `defineColumnType(int,int,int)`, `clearDefines`

#### defineColumnType

    void defineColumnType(int columnIndex,
                          int typeCode,
                          String typeName)
                   throws SQLException

Defines the type you will use to retrieve data from a particular database table column and specifies the column type name. This method should be used for structured object, object reference and array columns. See defineColumnType( int, int) for general information.

Parameters:
  `columnIndex` - index of column (first is 1)
  `typeCode` - type code for this column.
  `typeName` - specifies the fully-qualified name of the type of the column
Throws:
  `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`

#### setRowPrefetch

    void setRowPrefetch(int value)
                 throws 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:
  `SQLException` - if the argument `value` is <=0
See Also:
  `getRowPrefetch`, `OracleConnection.setDefaultRowPrefetch`

#### getLobPrefetchSize

    int getLobPrefetchSize()
                    throws SQLException

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.

Throws:
  `SQLException`
Since:
  11.2
See Also:
  `setLobPrefetchSize`

#### setLobPrefetchSize

    void setLobPrefetchSize(int value)
                     throws 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:
  `SQLException` - if `value < -1`
Since:
  11.2
See Also:
  `getLobPrefetchSize`, `OracleConnection.DEFAULT_LOB_PREFETCH_SIZE`

#### closeWithKey

    void closeWithKey(String key)
               throws 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:
  `SQLException` - if a database access error occurs

#### creationState

    int creationState()

Deprecated.

Returns:
  Creation Status flag

#### isNCHAR

    boolean isNCHAR(int index)
             throws 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:
  `SQLException`

#### setDatabaseChangeRegistration

    void setDatabaseChangeRegistration(DatabaseChangeRegistration registration)
                                throws 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:
  `SQLException`
Since:
  11.1
See Also:
  `getRegisteredQueryId()`

#### getRegisteredTableNames

    String[] getRegisteredTableNames()
                              throws SQLException

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

Throws:
  `SQLException`
Since:
  11.1

#### getRegisteredQueryId

    long getRegisteredQueryId()
                       throws SQLException

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

Throws:
  `SQLException`
Since:
  11.1
See Also:
  `setDatabaseChangeRegistration(oracle.jdbc.dcn.DatabaseChangeRegistration)`

#### closeOnCompletion

    void closeOnCompletion()
                    throws SQLException

Specifies that this Statement will be closed when all its dependent result sets are closed. If execution of the Statement does not produce any result sets, this method has no effect.

Specified by:
  `closeOnCompletion` in interface `Statement`
Throws:
  `SQLException` - if this method is called on a closed Statement
Since:
  1.7

#### enquoteLiteral

    default String enquoteLiteral(String val)
                           throws SQLException

Returns a `String` enclosed in single quotes. Any occurrence of a single quote within the string will be replaced by two single quotes.

> Examples of the conversion:
> Value | Result
> --- | ---
> Hello | 'Hello'
> G'Day | 'G''Day'
> 'G''Day' | '''G''''Day'''
> I'''M | 'I''''''M'

Parameters:
  `val` - a character string
Returns:
  A string enclosed by single quotes with every single quote converted to two single quotes
Throws:
  `NullPointerException` - if val is null
  `SQLException` - if val cannot be transformed into a SQL literal

#### enquoteNCharLiteral

    default String enquoteNCharLiteral(String val)
                                throws SQLException

Returns a `String` enclosed in single quotes and prefixed with 'N'. Any occurrence of a single quote within the string will be replaced by two single quotes.

> Examples of the conversion:
> Value | Result
> --- | ---
> Hello | N'Hello'
> G'Day | N'G''Day'
> N'G''Day' | N'''G''''Day'''
> I'''M | N'I''''''M'

Parameters:
  `val` - a character string
Returns:
  A string enclosed by single quotes with every single quote converted to two single quotes
Throws:
  `NullPointerException` - if val is null
  `SQLException` - if val cannot be transformed into a SQL literal

#### isSimpleIdentifier

    default boolean isSimpleIdentifier(String identifier)
                                throws SQLException

Retrieves whether `identifier` is a simple SQL identifier.

Parameters:
  `identifier` - a SQL identifier
Returns:
  A simple SQL identifier or a delimited identifier
Throws:
  `NullPointerException` - if identifier is null
  `SQLException` - if the driver cannot verify that identifier is a valid simple identifier or not

#### enquoteIdentifier

    default String enquoteIdentifier(String identifier,
                                     boolean alwaysQuote)
                              throws SQLException

Returns a SQL identifier. If `identifier` is a simple SQL identifier:

- Return the original value if `alwaysQuote` is `false`
- Return a delimited identifier if `alwaysQuote` is `true`

If `identifier` is not a simple SQL identifier, `identifier` will be enclosed in double quotes if not already present. If the datasource does not support double quotes for delimited identifiers, the identifier should be enclosed by the string returned from `DatabaseMetaData#getIdentifierQuoteString`. If the datasource does not support delimited identifiers, a `SQLFeatureNotSupportedException` should be thrown.

A `SQLException` will be thrown if `identifier` contains any characters invalid in a delimited identifier or the identifier length is invalid for the datasource.

Parameters:
  `identifier` - a SQL identifier
  `alwaysQuote` - indicates if a simple SQL identifier should be returned as a quoted identifier
Returns:
  A simple SQL identifier or a delimited identifier
Throws:
  `SQLException` - if identifier is not a valid identifier
  `SQLFeatureNotSupportedException` - if the datasource does not support delimited identifiers
  `NullPointerException` - if identifier is null

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