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.
Welcome to our forum for Oracle error: ORA-17041 Add your own message
org.springframework.jdbc.UncategorizedSQLException: CallableStatementCallback; uncategorized SQLException for SQL [{? = call requestCardDetails(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}]; SQL state [null]; error code [17041]; Missing IN or OUT parameter at index:: 20; nested exception is java.sql.SQLException: Missing IN or OUT parameter at index:: 20
Caused by: java.sql.SQLException: Missing IN or OUT parameter at index:: 20
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:111)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:145)
at oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(OraclePreparedStatement.java:1813)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3279)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3389)
at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4222)
at org.springframework.jdbc.core.JdbcTemplate$5.doInCallableStatement(JdbcTemplate.java:877)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:842)
at org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:875)
at org.springframework.jdbc.object.StoredProcedure.execute(StoredProcedure.java:113)
at com.btcom.test.RequestCardDetailsDaoImpl$MyStoredProcedure.execute(RequestCardDetailsDaoImpl.java:134)
at com.btcom.test.RequestCardDetailsDaoImpl.showCardDetails(RequestCardDetailsDaoImpl.java:33)
at com.btcom.test.TestDAO.main(TestDAO.java:15)
What is the value of registerOutParameter(20, Types.NUMERIC) set to?
we are using spring dao +jdbcdaosupport i dont exactly where i have to look to slove this problem
M tryng to fill in a feild with a sequesnce like ths
PreparedStatement psmt = conn.prepareStatement("insert into feed (cir_num,cir_date,cir_sub,cir_dept,emp_name,emp_code,emp_des,emp_dept,emp_add,emp_branch,emp_contact_num,emp_email,emp_date,comments,ip_add,id,ref_num) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,s2.nextval,?)");
It throws the exception:
javax.servlet.ServletException: Missing IN or OUT parameter at index:: 16
PreparedStatement psmt = conn.prepareStatement("insert into feed (cir_num,cir_date,cir_sub,cir_dept,emp_name,emp_code,emp_des,emp_dept,emp_add,emp_branch,emp_contact_num,emp_email,emp_date,comments,ip_add,id,ref_num) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,s2.nextval,?)");
It throws the exception:
javax.servlet.ServletException: Missing IN or OUT parameter at index:: 16
Add your message
Ask Your Question
If you need more information about this particular error message, you can leave a forum message.
We are replying to this message whenever we have some spare time, so please do not consider this as a private 'solve my critical issue asap' service.
Should you need professional Oracle Assistance to make your project a success, please have a look at our consultancy services.
Spam Protection
In order to prevent automatic generation of messages, we are asking for a validation code. This code is unique and is generated every time a new message is asked.
If you do not enter the validation correctly, your message will not be recorded.
Forum Rules
Please be polite, do not USE ALL UPPERCASE, no insults, violance or any other threats.