0

Possible Duplicate:
TO_DATE issue with Oracle DBMS_SQL.EXECUTE function

I am using a function that takes date as varchar input and the n passes it to another procedure.I could see that the time past associated with the date is not getting inserted at some scenario's. please find below my function

iv_plsql4 :='10-AUG-2012 07:30:30';
ln_dbms_cur := DBMS_SQL.OPEN_CURSOR;
iv_plsql2 := BEGIN PKG_PRADEEP.P_INSERTDATE(to_date(iv_plsql4,'DD-MM-YYYY HH24:MI:SS'));

DBMS_OUTPUT.put_line(iv_plsql);
DBMS_SQL.PARSE(ln_dbms_cur,iv_plsql,DBMS_SQL.NATIVE);
ln_cur_execute := DBMS_SQL.EXECUTE(ln_dbms_cur);

This code inserts the date in to the database but the time comes as 12:00 A.M.

but if I change the string iv_plsql2 as given below the date gets inserted with the TIME field.

iv_plsql2 := BEGIN PKG_PRADEEP.P_INSERTDATE(to_date('10-AUG-2012 07:30:30','DD-MM-YYYY HH24:MI:SS'));

Can someone explain why this happens?

Community
  • 1
  • 1
Cooldude
  • 31
  • 3
  • 9
  • The code you posted doesn't seem to make sense. You're assigning a PL/SQL block to the `iv_plsql2` variable which won't compile. The fact that you're using dynamic SQL makes me suspect that you're actually assigning a string to `iv_plsql2` that happens to contain a PL/SQL block. That, in turn, strongly implies that the error comes when you are building up this string which is what you have apparently edited out of your code in order to post the question. Additionally, I'm hoping that there really is a reason that you need to use dynamic SQL-- nothing here indicates a need to do so. – Justin Cave Aug 10 '12 at 21:57
  • Yes Justin.I have edited the code for posting it in the forum which is similar to the query I have in the production environment.Do you have any suggestions on the to_date issue? – Cooldude Aug 10 '12 at 22:02
  • How is this related to Java? I only see PL/SQL. – siebz0r Aug 10 '12 at 22:06
  • 1
    I strongly suspect that the bug is in the code that you edited out. I suspect that the code that you edited out is, at some point, doing an implicit date to string conversion. Since I can't see the code you've edited out, however, it's impossible to say where in that code the implicit conversion is taking place. – Justin Cave Aug 10 '12 at 22:07

1 Answers1

0

PL/SQL: Your date format should be DD-MON-YYYY, not DD-MM-YYYY.

Java: You are using setInt on your CallableStatement to set the date string. This can't be correct, in fact I'm not sure how that would even compile.

JamesB
  • 7,774
  • 2
  • 22
  • 21
  • It worked after changing the date format.Still I am seeing that the time part of the date going as 12:00 A.M. if I am calling the function from JAVA code by passing the date.Do you have any suggestions? – Cooldude Aug 10 '12 at 22:37
  • How are you passing the date from the Java code to the PL/SQL? – JamesB Aug 10 '12 at 22:51
  • I am passing it as String from JAVA and storing it in the iv_plsql4 varchar variable in my function.The passed date will be in the DD-MON-YYYY HH24:MI:SS format. – Cooldude Aug 10 '12 at 23:00
  • Could you add the Java code? Also, can you mark up my answer please, cheers. – JamesB Aug 11 '12 at 07:15
  • CallableStatement cstmt = session.connection().prepareCall( "{? = call PKG_PRADEEP.P_INSERT_TIME(?,)}"); cstmt.setFetchSize(10000); cstmt.registerOutParameter(1,oracle.jdbc.driver.OracleTypes.CURSOR); cstmt.setInt(2, "10-08-2012 09:30:30"); cstmt.execute(); – Cooldude Aug 11 '12 at 09:21
  • I have pasted my JAVA code above. – Cooldude Aug 11 '12 at 09:21
  • cstmt.setString(2, "10-08-2012 09:30:30"); Sorry that was a typo.It is actually a String. – Cooldude Aug 11 '12 at 09:52
  • I think we should close this thread and let it continue on http://stackoverflow.com/questions/11730144/to-date-issue-with-oracle-dbms-sql-execute-function – JamesB Aug 11 '12 at 10:58
  • Do you have any suggestions for this isssue? – Cooldude Aug 11 '12 at 11:03