Passing specific format date to callable statement

[carina caoor]

Hi , An oracle procedure accepts date in the format 01-Jan-2009 (dd-MMM-yyyy) and i need to paas the date in callable statement for executing the procedure, and my code is

    

  SimpleDateFormat sdf=new SimpleDateFormat("dd-MMM-yyyy");

  CallableStatement stmt = connection.prepareCall("{call "+batchcode+"(?,?)}")

	    		  java.sql.Date dt1 = new java.sql.Date( sdf.parse("01-JAN-2009").getTime() );
	    		  java.sql.Date dt2 = new java.sql.Date( sdf.parse("10-JAN-2009").getTime() );
	    		  stmt.setDate(1, dt1);
	    		  stmt.setDate(2, dt2);
	    		  stmt.executeQuery();

As the procedure accepts the date in dd-mmm-yyyy format and the date returned in dt1 and dt2 variables are in the format 2009-01-01(yyyy-mm-dd) the procedure is not being called and throws out error:

java.sql.SQLException: ORA-01008: not all variables bound

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:590)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1973)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1119)
at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2191)
at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:2064)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2989)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:658)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:584)
at excelread.ExecuteBatchParameter.run(ExecuteBatchParameter.java:84)
at java.lang.Thread.run(Unknown Source)




Can anybody suggest me how do i pass the sql date to the callable statement in specific format/.

[Christophe Verré]

As the procedure accepts the date in dd-mmm-yyyy format and the date returned in dt1 and dt2 variables are in the format 2009-01-01(yyyy-mm-dd) the procedure is not being called and throws out error:

Date don't have any notion of format. This should not be the problem. What about your stored procedure ? Does it execute normally without using JDBC ? (e.g. via SQL*Plus)

[carina caoor]

Yes when i execute it without JDBC it runs normally. My procedure passes the date into the following query which accepts the date in specific format of
dd-mmm-yyyy

 

SELECT * FROM DI_PAY WHERE  trunc(PE.PAY_DT) BETWEEN '01-JAN-2009' AND '21-JAN-2009'


   

[Christophe Verré]

What's the type of the parameters ? VARCHAR ? DATE ?

[carina caoor]

the data type is date.

[Christophe Verré]

Can you show exactly how your procedure looks like ?

[carina caoor]

   


create or replace
PROCEDURE          "CM_PAYUSERCNT_VER1" ( p_from_Dt IN DATE :=NULL, p_to_dt IN DATE :=NULL)      IS
BEGIN
INSERT into CM_TEST_DATA(PAY_DT,USER_ID,COUNT)(
SELECT TO_CHAR(PE.PAY_DT, 'DD-MON-YYYY') PAY_DT , T.USER_ID , COUNT(*) CNT
FROM CI_PAY_EVENT PE, CI_PAY_TNDR P, CI_TNDR_CTL T
WHERE PE.PAY_EVENT_ID = P.PAY_EVENT_ID
AND P.TNDR_CTL_ID = T.TNDR_CTL_ID (+)
AND PE.PAY_DT BETWEEN p_from_Dt AND p_to_dt
GROUP BY PE.PAY_DT, T.USER_ID);
END;






 

[Christophe Verré]

In your prepareCall, what is batchcode equal to ?

[Christophe Verré]

I'm not sure this will help, but try stmt.execute(); instead of stmt.executeQuery();