Not a valid month but Number datatype Java-Oracle-CodePudding

I try to collect some information from database Oracle through Java.

The string that i execute is:

String sql="SELECT * FROM SAP_PHOTOVOLTAIC WHERE REF_MONTH=? ";

try (PreparedStatement pst=connection.prepareStatement(sql)
{
pst.setInt(1,Integer.parseInt(stringDateThatGetsFromUser))
 try(ResultSet rs=pst.executeQuery()){ //throws exception
.......
}}

The exception thrown is

java.sql.SQLDataException: ORA-01843: not a valid month

Caused by: Error : 1843, Position : 1440, Sql = SELECT * FROM SAP_PHOTOVOLTAIC WHERE REF_MONTH=:1 , OriginalSql = SELECT * FROM SAP_PHOTOVOLTAIC WHERE REF_MONTH=?, Error Msg = ORA-01843: not a valid month

AN example of ref month that i set is '202101'. In Oracle it is a NUMBER(10) data type not a DATE.

I also tried to execute as some other post on 'not valid month' suggested.

      connection.createStatement().execute("alter session set NLS_DATE_FORMAT='YYYYMM'");

The exception changed to:

Caused by: Error : 1858, Position : 1440, Sql = SELECT * FROM SAP_PHOTOVOLTAIC WHERE REF_MONTH=:1 , OriginalSql = SELECT * FROM SAP_PHOTOVOLTAIC WHERE REF_MONTH=?, Error Msg = ORA-01858: a non-numeric character was found where a numeric was expected

I have also tried TO_NUMBER(?,'999999') with setString. Same error as previous.

When i execute though Oracle the following, it runs ok.

SELECT * FROM SAP_PHOTOVOLTAIC WHERE REF_MONTH='202101';

***UPDATE

i dont know why but this works through Java, but it gives zero results.

 String sql="SELECT * FROM SAP_PHOTOVOLTAIC WHERE REF_MONTH='20210101'";

CodePudding user response：

Case closed. We found the error. The sql code of the table in the database wasn't properly modified for some values in two columns.