How to pass date object to PL-SQL as the first and only input parameter

Question

1. First I will be taking a date value as a input variable from the database. Date format is coming in the format as (2020-08-31).

2. I need to convert this date and pass it into the PL-SQL block in the format as below.

   to_date('31082020','ddmmyyyy')

3. To get the above format I did the conversion using the java as below.

   // Converting the date format for the required type for PL-SQL block
    SimpleDateFormat simpleDateFormat = new SimpleDateFormat("ddMMyyyy");
    String modifiedDate = simpleDateFormat.format(processingDate);
    String modifiedDateParameter = "to_date(\'"+modifiedDate+"\','ddmmyyyy')";
   

4. Now I will be passing the above modifiedDateParameter to my PL-SQL block as the input parameter. Code for this is as below. Here SQL is the procedure I will be calling.

   callableStatement = conn.prepareCall(SQL); callableStatement.setDate(1,java.sql.Date.valueOf(billEndDate)); callableStatement.executeUpdate();

5. However I getting the below exception.

Exception in thread "main" java.lang.IllegalArgumentException at java.sql/java.sql.Date.valueOf(Date.java:141)

Can I know how what modification I need to do in order to fix this issue please?

Answer 1

Have a look at the javadoc of java.sql.Date.valueOf: https://docs.oracle.com/javase/7/docs/api/java/sql/Date.html#valueOf(java.lang.String)

Converts a string in JDBC date escape format to a Date value. s - a String object representing a date in in the format "yyyy-[m]m-[d]d". The leading zero for mm and dd may also be omitted.

So this call works: java.sql.Date.valueOf("2020-2-2");

This throws the exception you mention: java.sql.Date.valueOf("20200909");