Menu
I am having issues calling an Oracle FUNCTION (not a Stored Procedure) from Java 1.6, using ojdbc14.jar.
I do not know what the function contains as I am calling it from a remote server, all I know is this:
FUNCTION ap_ch_get_acct_balances (VAR_PI_MOB_NO_ACCT_NO VARCHAR2, VAR_REPLY_CODE OUT NUMBER, VAR_EXT_RESPONSE OUT VARCHAR2, VAR_PO_ACC_BAL OUT CHAR, VAR_PO_ACCT_NO OUT CHAR) The schema I need to use is: FCRLIVE.AP_CH_GET_ACCT_BALANCES
I am trying this:
String call = "{ ? = call FCRLIVE.AP_CH_GET_ACCT_BALANCES(?, ?, ?, ?, ?) }"; CallableStatement cstmt = conn.prepareCall(call); cstmt.setQueryTimeout(1800); cstmt.setString(1, inputCode); cstmt.registerOutParameter(2, oracle.jdbc.OracleTypes.NUMBER); cstmt.registerOutParameter(3, oracle.jdbc.OracleTypes.VARCHAR); cstmt.registerOutParameter(4, oracle.jdbc.OracleTypes.CHAR); cstmt.registerOutParameter(5, oracle.jdbc.OracleTypes.CHAR); cstmt.executeUpdate(); But I keep seeing this in the log file:
java.sql.SQLException: ORA-01006: bind variable does not exist at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134) at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289) at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:573) at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1891) at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1093) at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2047) at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:1940) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2688) at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:589) Am I calling the function wrong? Or what could this possibly be?
Thanks in advance for the help!
876
The java.sql.CallableStatement is an interface which can be used to execute SQL FUNCTIONS in java. Example/ Full Programs JDBC- Calling Oracle database FUNCTION - CallableStatement example in java System.out.println ("Connection established successfully!"); callableStmt = con.prepareCall (" { ? = call MYFUNCTION (?)}");
First, we can simply call the Oracle function just like any other SQL query: Another approach is to call the database function using plain JDBC API: " { ? = call fn_count_comments (?) }" )) {
Oracle functions. Oracle also supports database functions, which, unlike stored procedures, don’t use input and output parameters, but one or more function arguments and a single return value.
The java.sql.CallableStatement is an interface which can be used to execute SQL FUNCTIONS in java. Example/ Full Programs JDBC- Calling Oracle database FUNCTION - CallableStatement example in java
it should be:
String call = "{ ? = call FCRLIVE.AP_CH_GET_ACCT_BALANCES(?, ?, ?, ?, ?) }"; If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
