Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Passing specific format date to callable statement

 
carina caoor
Ranch Hand
Posts: 300
Eclipse IDE Oracle Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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




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é
Sheriff
Posts: 14691
16
Eclipse IDE Ubuntu VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 300
Eclipse IDE Oracle Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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

 
Christophe Verré
Sheriff
Posts: 14691
16
Eclipse IDE Ubuntu VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What's the type of the parameters ? VARCHAR ? DATE ?
 
carina caoor
Ranch Hand
Posts: 300
Eclipse IDE Oracle Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
the data type is date.
 
Christophe Verré
Sheriff
Posts: 14691
16
Eclipse IDE Ubuntu VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Can you show exactly how your procedure looks like ?
 
carina caoor
Ranch Hand
Posts: 300
Eclipse IDE Oracle Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
 
Christophe Verré
Sheriff
Posts: 14691
16
Eclipse IDE Ubuntu VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
In your prepareCall, what is batchcode equal to ?
 
Christophe Verré
Sheriff
Posts: 14691
16
Eclipse IDE Ubuntu VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm not sure this will help, but try stmt.execute(); instead of stmt.executeQuery();
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic