|
ORA-17041: Missing IN or OUT parameter at index:
eg:
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Types;
import oracle.jdbc.OracleDriver;
public class Test17041 {
public static void main(String[] args) {
try {
new OracleDriver();
Connection conn = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:ORCL", "scott", "tiger");
CallableStatement stat = conn.prepareCall("{ call ? := test_17041(?) }");
//stat.registerOutParameter(1, Types.NUMERIC);
stat.setInt(2, 42);
stat.execute();
System.out.println(stat.getInt(1));
} catch(SQLException e) {
System.err.println("Error: " + e.getErrorCode());
e.printStackTrace();
} catch(Exception e) {
e.printStackTrace();
}
}
}
Solution: add a call to the registerOutParameter.
Alternatively, there is a problem with the Oracle 10g JDBC driver when trying to use the :NEW or :OLD Oracle keywords in a PreparedStatement. This will happen if you try to create a trigger using PrepareStatement instead of the Statement. eg:
final Connection conn = DriverManager.getConnection( "jdbc:oracle:thin:@"+cs, user, pw );
String sql = "create or replace trigger t_bir before insert on t for each row ";
sql += "begin :new.c := :new.c+1; end;";
PreparedStatement pstmt;
pstmt = conn.prepareStatement(sql);
pstmt.execute();
pstmt.close();
Previous example fails with a ORA-17041 when using the Oracle 10g JDBC driver because we reference the :NEW keyword inside the PrepareStatement call.
If we change the example to use Statement instead, we solve the problem:
Statement pstmt;
pstmt = conn.createStatement();
pstmt.executeQuery(sql);
pstmt.close();
Creating triggers using the Oracle Enterprise Manager are know to have this problem.Solution: Either use a different version of the JDBC driver or change the code so it's using the Statement call. Here's another style that worked in 9i but fails in 10g with an (inappropriate) ORA-17041 error:
String sql = "BEGIN" +
" insert into FOO (ID,VALUE) values 1001,'Something' RETURNING ID into :1;" +
" update BAR set FOO_ID = :1 where ID = 20002;";
CallableStatement stat = conn.prepareCall(sql); stat.registerOutParameter(1, Types.NUMERIC); stat.execute();
LOG.info("Result: " + stat.getLong(1));
With the Oracle-style bind parameters, you can now use each bind variable only once.
Forum Messages
Add your message for ORA-17041
| ||||||||||||||||||||||||
Home