aspose file tools*
The moose likes JDBC and the fly likes Passing specific format date to callable statement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Passing specific format date to callable statement" Watch "Passing specific format date to callable statement" New topic
Author

Passing specific format date to callable statement

carina caoor
Ranch Hand

Joined: Jun 23, 2007
Posts: 300

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

Joined: Nov 24, 2005
Posts: 14688
    
  16

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)


[My Blog]
All roads lead to JavaRanch
carina caoor
Ranch Hand

Joined: Jun 23, 2007
Posts: 300

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

Joined: Nov 24, 2005
Posts: 14688
    
  16

What's the type of the parameters ? VARCHAR ? DATE ?
carina caoor
Ranch Hand

Joined: Jun 23, 2007
Posts: 300

the data type is date.
Christophe Verré
Sheriff

Joined: Nov 24, 2005
Posts: 14688
    
  16

Can you show exactly how your procedure looks like ?
carina caoor
Ranch Hand

Joined: Jun 23, 2007
Posts: 300

Christophe Verré
Sheriff

Joined: Nov 24, 2005
Posts: 14688
    
  16

In your prepareCall, what is batchcode equal to ?
Christophe Verré
Sheriff

Joined: Nov 24, 2005
Posts: 14688
    
  16

I'm not sure this will help, but try stmt.execute(); instead of stmt.executeQuery();
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Passing specific format date to callable statement