oracle.jdbc
Interface OracleCallableStatement

public abstract interface OracleCallableStatement
extends java.sql.CallableStatement, OraclePreparedStatement

This interface extends the OraclePreparedStatement (which extends the OracleStatement interface) and incorporates standard JDBC callable statement functionality. It is used to execute SQL stored procedure.

Oracle JDBC drivers support execution of PL/SQL stored procedures and anonymous blocks. They support both SQL92 escape syntax and Oracle PL/SQL block syntax. The following PL/SQL calls would work with any Oracle JDBC driver: 

// SQL92 syntax
CallableStatement cs1 = conn.prepareCall
( "{call proc (?,?)}" ) ; // stored proc
CallableStatement cs2 = conn.prepareCall
( "{? = call func (?,?)}" ) ; // stored func
// Oracle PL/SQL block syntax
CallableStatement cs3 = conn.prepareCall
( "begin proc (?,?); end;" ) ; // stored proc
CallableStatement cs4 = conn.prepareCall
( "begin ? := func(?,?); end;" ) ; // stored func

As an example of using Oracle syntax, here is a PL/SQL code snippet that creates a stored function. The PL/SQL function gets a character sequence and concatenates a suffix to it: 

create or replace function foo (val1 char)
return char as
begin
return val1 || 'suffix';
end;

Your invocation call in your JDBC program should look like: 

Connection conn = DriverManager.getConnection
("jdbc:oracle:oci8:@", "scott", "tiger");
CallableStatement cs = conn.prepareCall ("begin ? := foo(?); end;");
cs.registerOutParameter(1,Types.CHAR);
cs.setString(2, "aa");
cs.executeUpdate();
String result = cs.getString(1);

Since:
8.1.7
See Also:
Connection.prepareCall

Fields inherited from class oracle.jdbc.OraclePreparedStatement
FORM_CHAR, FORM_NCHAR
 
Fields inherited from class oracle.jdbc.OracleStatement
EXPLICIT, IMPLICIT, NEW
 
Method Summary
 java.lang.Object getAnyDataEmbeddedObject(int parameterIndex)
          Retrieves data of an embedded object within AnyData
 ARRAY getARRAY(int parameterIndex)
          Retrieves data into an oracle.sql.ARRAY object.
 java.io.InputStream getAsciiStream(int parameterIndex)
          Retrieves data into an java.io.InputStream object.
 BFILE getBFILE(int parameterIndex)
          Retrieves data into an oracle.sql.BFILE object.
 java.io.InputStream getBinaryStream(int parameterIndex)
          Retrieves data into an java.io.InputStream object.
 BLOB getBLOB(int parameterIndex)
          Retrieves data into an oracle.sql.BLOB object.
 CHAR getCHAR(int parameterIndex)
          Retrieves data into an oracle.sql.CHAR object.
 java.io.Reader getCharacterStream(int parameterIndex)
          Retrieves data into an java.io.Reader object.
 CLOB getCLOB(int parameterIndex)
          Retrieves data into an oracle.sql.CLOB object.
 java.sql.ResultSet getCursor(int parameterIndex)
          Retrieves data into an java.sql.ResultSet object.
 java.lang.Object getCustomDatum(int parameterIndex, CustomDatumFactory factory)
          Deprecated.  
 DATE getDATE(int parameterIndex)
          Retrieves data into an oracle.sql.DATE object.
 INTERVALYM getINTERVALYM(int parameterIndex)
          Retrieves data into an oracle.sql.INTERVALYM object.
 NUMBER getNUMBER(int parameterIndex)
          Retrieves data into an oracle.sql.NUMBER object.
 OPAQUE getOPAQUE(int parameterIndex)
          Retrieves data into an oracle.sql.OPAQUE object.
 Datum getOracleObject(int parameterIndex)
          Retrieves data into an oracle.sql.Datum object.
 Datum[] getOraclePlsqlIndexTable(int paramIndex)
          Oracle OCI driver specific.
 java.lang.Object getORAData(int parameterIndex, ORADataFactory factory)
           
 java.lang.Object getPlsqlIndexTable(int paramIndex)
          Oracle OCI driver specific.
 java.lang.Object getPlsqlIndexTable(int paramIndex, java.lang.Class primitiveType)
          Oracle OCI driver specific.
 RAW getRAW(int parameterIndex)
          Retrieves data into an oracle.sql.RAW object.
 REF getREF(int parameterIndex)
          Retrieves data into an oracle.sql.REF object.
 ROWID getROWID(int parameterIndex)
          Retrieves data into an oracle.sql.ROWID object.
 STRUCT getSTRUCT(int parameterIndex)
          Retrieves data into an oracle.sql.STRUCT object.
 TIMESTAMP getTIMESTAMP(int paramIdx)
          Retrieves data into an oracle.sql.TIMESTAMP object.
 TIMESTAMPLTZ getTIMESTAMPLTZ(int paramIdx)
          Retrieves data into an oracle.sql.TIMESTAMPLTZ object.
 TIMESTAMPTZ getTIMESTAMPTZ(int paramIdx)
          Retrieves data into an oracle.sql.TIMESTAMPTZ object.
 java.io.InputStream getUnicodeStream(int parameterIndex)
          Retrieves data into an java.io.InputStream object.
 void registerIndexTableOutParameter(int paramIndex, int maxLen, int elemSqlType, int elemMaxLen)
          Oracle OCI driver specific.
 void registerOutParameter(int paramIndex, int sqlType, int scale, int maxLength)
          Special Oracle version of registerOutParameter for registering CHAR, VARCHAR, LONG, RAW and LONG RAW columns.
 void registerOutParameterBytes(int paramIndex, int sqlType, int scale, int maxLength)
          Special Oracle version of registerOutParameter for registering CHAR, VARCHAR, LONG, RAW and LONG RAW columns.
 void registerOutParameterChars(int paramIndex, int sqlType, int scale, int maxLength)
          Special Oracle version of registerOutParameter for registering CHAR, VARCHAR, LONG, RAW and LONG RAW columns.
 int sendBatch()
          Send the sets of parameters batched (for Oracle-style batching only).
 void setExecuteBatch(int nrows)
          Set the batch value (for Oracle-style batching only).
 
Methods inherited from interface java.sql.CallableStatement
getArray, getBigDecimal, getBigDecimal, getBlob, getBoolean, getByte, getBytes, getClob, getDate, getDate, getDouble, getFloat, getInt, getLong, getObject, getObject, getRef, getShort, getString, getTime, getTime, getTimestamp, getTimestamp, registerOutParameter, registerOutParameter, registerOutParameter, wasNull
 
Methods inherited from interface oracle.jdbc.OraclePreparedStatement
defineParameterType, defineParameterTypeBytes, defineParameterTypeChars, getExecuteBatch, OracleGetParameterMetaData, setARRAY, setBfile, setBFILE, setBLOB, setCHAR, setCheckBindTypes, setCLOB, setCursor, setCustomDatum, setDATE, setDisableStmtCaching, setFixedCHAR, setFormOfUse, setINTERVALYM, setNUMBER, setOPAQUE, setOracleObject, setORAData, setPlsqlIndexTable, setRAW, setREF, setRefType, setROWID, setSTRUCT, setStructDescriptor, setTIMESTAMP, setTIMESTAMPLTZ, setTIMESTAMPTZ
 
Methods inherited from interface oracle.jdbc.OracleStatement
clearDefines, closeWithKey, creationState, defineColumnType, defineColumnType, defineColumnType, defineColumnTypeBytes, defineColumnTypeChars, getRowPrefetch, isNCHAR, setResultSetCache, setRowPrefetch
 

Method Detail

getARRAY

public ARRAY getARRAY(int parameterIndex)
               throws java.sql.SQLException
Retrieves data into an oracle.sql.ARRAY object.
Parameters:
parameterIndex - the first parameter is 1, the second is 2, and so on
Returns:
data into an ARRAY
Throws:
java.sql.SQLException - if an error occurs (conversion or database-access error)

getAsciiStream

public java.io.InputStream getAsciiStream(int parameterIndex)
                                   throws java.sql.SQLException
Retrieves data into an java.io.InputStream object.
Parameters:
parameterIndex - the first parameter is 1, the second is 2, and so on
Returns:
data into an java.io.InputStream
Throws:
java.sql.SQLException - if an error occurs (conversion or database-access error)

getBFILE

public BFILE getBFILE(int parameterIndex)
               throws java.sql.SQLException
Retrieves data into an oracle.sql.BFILE object.
Parameters:
parameterIndex - the first parameter is 1, the second is 2, and so on
Returns:
data into an oracle.sql.BFILE
Throws:
java.sql.SQLException - if an error occurs (conversion or database-access error)

getBinaryStream

public java.io.InputStream getBinaryStream(int parameterIndex)
                                    throws java.sql.SQLException
Retrieves data into an java.io.InputStream object.
Parameters:
parameterIndex - the first parameter is 1, the second is 2, and so on
Returns:
data into an java.io.InputStream
Throws:
java.sql.SQLException - if an error occurs (conversion or database-access error)

getBLOB

public BLOB getBLOB(int parameterIndex)
             throws java.sql.SQLException
Retrieves data into an oracle.sql.BLOB object.
Parameters:
parameterIndex - the first parameter is 1, the second is 2, and so on
Returns:
data into an oracle.sql.BLOB
Throws:
java.sql.SQLException - if an error occurs (conversion or database-access error)

getCHAR

public CHAR getCHAR(int parameterIndex)
             throws java.sql.SQLException
Retrieves data into an oracle.sql.CHAR object.
Parameters:
parameterIndex - the first parameter is 1, the second is 2, and so on
Returns:
data into an oracle.sql.CHAR
Throws:
java.sql.SQLException - if an error occurs (conversion or database-access error)

getCharacterStream

public java.io.Reader getCharacterStream(int parameterIndex)
                                  throws java.sql.SQLException
Retrieves data into an java.io.Reader object.
Parameters:
parameterIndex - the first parameter is 1, the second is 2, and so on
Returns:
data into an java.io.Reader
Throws:
java.sql.SQLException - if an error occurs (conversion or database-access error)

getCLOB

public CLOB getCLOB(int parameterIndex)
             throws java.sql.SQLException
Retrieves data into an oracle.sql.CLOB object.
Parameters:
parameterIndex - the first parameter is 1, the second is 2, and so on
Returns:
data into an oracle.sql.CLOB
Throws:
java.sql.SQLException - if an error occurs (conversion or database-access error)

getCursor

public java.sql.ResultSet getCursor(int parameterIndex)
                             throws java.sql.SQLException
Retrieves data into an java.sql.ResultSet object.
Parameters:
parameterIndex - the first parameter is 1, the second is 2, and so on
Returns:
data into an java.sql.ResultSet
Throws:
java.sql.SQLException - if an error occurs (conversion or database-access error)

getCustomDatum

public java.lang.Object getCustomDatum(int parameterIndex,
                                       CustomDatumFactory factory)
                                throws java.sql.SQLException
Deprecated.  
Parameters:
parameterIndex - the first parameter is 1, the second is 2, and so on
factory -  
Returns:
 
Throws:
java.sql.SQLException - if an error occurs (conversion or database-access error)

getORAData

public java.lang.Object getORAData(int parameterIndex,
                                   ORADataFactory factory)
                            throws java.sql.SQLException
Parameters:
parameterIndex - the first parameter is 1, the second is 2, and so on
factory -  
Returns:
 
Throws:
java.sql.SQLException - if an error occurs (conversion or database-access error)

getAnyDataEmbeddedObject

public java.lang.Object getAnyDataEmbeddedObject(int parameterIndex)
                                          throws java.sql.SQLException
Retrieves data of an embedded object within AnyData
Parameters:
parameterIndex - the first parameter is 1
Returns:
data depending on the embedded type in AnyData
Throws:
java.sql.SQLException - if an error occurs (conversion or database-access error)

getDATE

public DATE getDATE(int parameterIndex)
             throws java.sql.SQLException
Retrieves data into an oracle.sql.DATE object.
Parameters:
parameterIndex - the first parameter is 1, the second is 2, and so on
Returns:
data into an oracle.sql.DATE
Throws:
java.sql.SQLException - if an error occurs (conversion or database-access error)

getNUMBER

public NUMBER getNUMBER(int parameterIndex)
                 throws java.sql.SQLException
Retrieves data into an oracle.sql.NUMBER object.
Parameters:
parameterIndex - the first parameter is 1, the second is 2, and so on
Returns:
data into an oracle.sql.NUMBER
Throws:
java.sql.SQLException - if an error occurs (conversion or database-access error)

getOPAQUE

public OPAQUE getOPAQUE(int parameterIndex)
                 throws java.sql.SQLException
Retrieves data into an oracle.sql.OPAQUE object.
Parameters:
parameterIndex - the first parameter is 1, the second is 2, and so on
Returns:
data into an oracle.sql.OPAQUE
Throws:
java.sql.SQLException - if an error occurs (conversion or database-access error)

getOracleObject

public Datum getOracleObject(int parameterIndex)
                      throws java.sql.SQLException
Retrieves data into an oracle.sql.Datum object.
Parameters:
parameterIndex - the first parameter is 1, the second is 2, and so on
Returns:
data into an oracle.sql.Datum
Throws:
java.sql.SQLException - if an error occurs (conversion or database-access error)

getRAW

public RAW getRAW(int parameterIndex)
           throws java.sql.SQLException
Retrieves data into an oracle.sql.RAW object.
Parameters:
parameterIndex - the first parameter is 1, the second is 2, and so on
Returns:
data into an oracle.sql.RAW
Throws:
java.sql.SQLException - if an error occurs (conversion or database-access error)

getREF

public REF getREF(int parameterIndex)
           throws java.sql.SQLException
Retrieves data into an oracle.sql.REF object.
Parameters:
parameterIndex - the first parameter is 1, the second is 2, and so on
Returns:
data into an oracle.sql.REF
Throws:
java.sql.SQLException - if an error occurs (conversion or database-access error)

getROWID

public ROWID getROWID(int parameterIndex)
               throws java.sql.SQLException
Retrieves data into an oracle.sql.ROWID object.
Parameters:
parameterIndex - the first parameter is 1, the second is 2, and so on
Returns:
data into an oracle.sql.ROWID
Throws:
java.sql.SQLException - if an error occurs (conversion or database-access error)

getSTRUCT

public STRUCT getSTRUCT(int parameterIndex)
                 throws java.sql.SQLException
Retrieves data into an oracle.sql.STRUCT object.
Parameters:
parameterIndex - the first parameter is 1, the second is 2, and so on
Returns:
data into an oracle.sql.STRUCT
Throws:
java.sql.SQLException - if an error occurs (conversion or database-access error)

getINTERVALYM

public INTERVALYM getINTERVALYM(int parameterIndex)
                         throws java.sql.SQLException
Retrieves data into an oracle.sql.INTERVALYM object.
Parameters:
parameterIndex - the first parameter is 1, the second is 2, and so on
Returns:
data into an oracle.sql.INTERVALYM
Throws:
java.sql.SQLException - if an error occurs (conversion or database-access error)
Since:
9i

getTIMESTAMP

public TIMESTAMP getTIMESTAMP(int paramIdx)
                       throws java.sql.SQLException
Retrieves data into an oracle.sql.TIMESTAMP object.
Parameters:
paramIdx - the first parameter is 1, the second is 2, and so on
Returns:
data into an oracle.sql.TIMESTAMP
Throws:
java.sql.SQLException - if an error occurs (conversion or database-access error)
Since:
9i

getTIMESTAMPTZ

public TIMESTAMPTZ getTIMESTAMPTZ(int paramIdx)
                           throws java.sql.SQLException
Retrieves data into an oracle.sql.TIMESTAMPTZ object.
Parameters:
paramIdx - the first parameter is 1, the second is 2, and so on
Returns:
data into an oracle.sql.TIMESTAMPTZ
Throws:
java.sql.SQLException - if an error occurs (conversion or database-access error)
Since:
9i

getTIMESTAMPLTZ

public TIMESTAMPLTZ getTIMESTAMPLTZ(int paramIdx)
                             throws java.sql.SQLException
Retrieves data into an oracle.sql.TIMESTAMPLTZ object.
Parameters:
paramIdx - the first parameter is 1, the second is 2, and so on
Returns:
data into an oracle.sql.TIMESTAMPLTZ
Throws:
java.sql.SQLException - if an error occurs (conversion or database-access error)
Since:
9i

getUnicodeStream

public java.io.InputStream getUnicodeStream(int parameterIndex)
                                     throws java.sql.SQLException
Retrieves data into an java.io.InputStream object.
Parameters:
parameterIndex - the first parameter is 1, the second is 2, and so on
Returns:
data into an java.io.InputStream
Throws:
java.sql.SQLException - if an error occurs (conversion or database-access error)

registerOutParameter

public void registerOutParameter(int paramIndex,
                                 int sqlType,
                                 int scale,
                                 int maxLength)
                          throws java.sql.SQLException
Special Oracle version of registerOutParameter for registering CHAR, VARCHAR, LONG, RAW and LONG RAW columns. Depending on the value of OracleConnection.getDataSizeUnits(), maxLength will be measured in bytes or characters.

registerOutParameterBytes

public void registerOutParameterBytes(int paramIndex,
                                      int sqlType,
                                      int scale,
                                      int maxLength)
                               throws java.sql.SQLException
Special Oracle version of registerOutParameter for registering CHAR, VARCHAR, LONG, RAW and LONG RAW columns. This version accepts a maxLength parameter measured in bytes.

Parameters:
paramIndex - parameter index (the first parameter is 1).
sqlType - type of the bind parameter
scale - not used
maxLength - maximum length of the column, specified in bytes. If not specified, maximum length allowed for that type is used.
Throws:
java.sql.SQLException - if an error occurs (conversion or database-access error)

registerOutParameterChars

public void registerOutParameterChars(int paramIndex,
                                      int sqlType,
                                      int scale,
                                      int maxLength)
                               throws java.sql.SQLException
Special Oracle version of registerOutParameter for registering CHAR, VARCHAR, LONG, RAW and LONG RAW columns. This version accepts a maxLength parameter measured in characters.

Parameters:
paramIndex - parameter index (the first parameter is 1).
sqlType - type of the bind parameter
scale - not used
maxLength - maximum length of the column, specified in characters. If not specified, maximum length allowed for that type is used.
Throws:
java.sql.SQLException - if an error occurs (conversion or database-access error)

sendBatch

public int sendBatch()
              throws java.sql.SQLException
Send the sets of parameters batched (for Oracle-style batching only).

Oracle-style batching is not supported for a callable statement. This method simply returns the number of valid rows.

Specified by:
sendBatch in interface OraclePreparedStatement
Throws:
java.sql.SQLException - if an error occurs (conversion or database-access error)

setExecuteBatch

public void setExecuteBatch(int nrows)
                     throws java.sql.SQLException
Set the batch value (for Oracle-style batching only).

Oracle-style batching is not supported for a callable statement. This method always sets the batch value to 1.

Specified by:
setExecuteBatch in interface OraclePreparedStatement
Throws:
java.sql.SQLException - if an error occurs (conversion or database-access error)

getPlsqlIndexTable

public java.lang.Object getPlsqlIndexTable(int paramIndex)
                                    throws java.sql.SQLException
Oracle OCI driver specific. Get the value of a PLSQL index table parameter as a Java array. The type of the Java arry will be the default Java object type corresponding to the element's SQL type, following the mapping for built-in types specified in the JDBC spec.
Parameters:
paramIndex - the first parameter is 1, the second is 2, and so on
Returns:
A java.lang.Object holding the parameter value.
Throws:
java.sql.SQLException - if a database-access error occurs.
java.sql.SQLException - if an error occurs (conversion or database-access error)

getPlsqlIndexTable

public java.lang.Object getPlsqlIndexTable(int paramIndex,
                                           java.lang.Class primitiveType)
                                    throws java.sql.SQLException
Oracle OCI driver specific. Get the value of a PLSQL index table parameter as a primitive array. 'primitiveType' speicifies the return array type.
Parameters:
paramIndex - the first parameter is 1, the second is 2, and so on
primitiveType - is a primitive type class. For example,java.lang.Double.Type
Returns:
A java.lang.Object holding the parameter value.
Throws:
java.sql.SQLException - if a database-access error occurs.
java.sql.SQLException - if an error occurs (conversion or database-access error)

getOraclePlsqlIndexTable

public Datum[] getOraclePlsqlIndexTable(int paramIndex)
                                 throws java.sql.SQLException
Oracle OCI driver specific. Get the value of a PLSQL index table parameter as a oracle.sql.Datum array.
Parameters:
paramIndex - the first parameter is 1, the second is 2, and so on
Returns:
A Datum array holding the parameter value.
Throws:
java.sql.SQLException - if a database-access error occurs.
java.sql.SQLException - if an error occurs (conversion or database-access error)

registerIndexTableOutParameter

public void registerIndexTableOutParameter(int paramIndex,
                                           int maxLen,
                                           int elemSqlType,
                                           int elemMaxLen)
                                    throws java.sql.SQLException
Oracle OCI driver specific. Special Oracle version of registerOutParameter for registering PLSQL index table parameter.
Parameters:
paramIndex - the first parameter is 1, the second is 2, and so on
maxLen - the maximum possible number of elements.
curLen - the current number of elements
elemSqlType - index table element SQL type (as defined in java.sql.Types or OracleTypes).
elemMaxLen - maximum length of the element. If not specified, maximum length allowed for that type is used.
Throws:
java.sql.SQLException - if sqlType is invalid, or an error occurred.
Since:
8.1.7