CODEWITHSUNDEEP
javasqloracle-databasejdbc
Max
Max

Reputation: 11

Oracle date conversion error ORA-01858

I'm trying to run an Oracle stored procedure and pass the result as a data source to a report in a java web application. I'm using spring MVC and my OS is Windows 7- 32bit.

The code is something like this:

DataSource ds = jdbcTemplate.getDataSource();
Connection conn = ds.getConnection();
CallableStatement cs = conn.prepareCall(report.getStoredSQL());

... preparing the parameters...

cs.registerOutParameter(7, oracle.jdbc.OracleTypes.CURSOR);
cs.execute();
ResultSet resultSet = (ResultSet)cs.getObject( outputParamNumber );

rs = new CachedRowSetImpl();
rs.populate(resultSet);

then I pass the rs to my report.

When I go to my Control Panel-->Region and Language-->Format and set it to English(United States) everything works fine but if I set it to English(Canada) I'll get this exception when executing this line:

rs.populate(resultSet);

This is the exception:

[22/09/14 14:13:25:166 EDT] 00000076 SystemErr R java.sql.SQLDataException: ORA-01858: a non-numeric character was found where a numeric was expected ORA-06512: at "IBMS.PK_COT_BLD_REPORTS", line 4913

And this is line 4913 of my stored procedure: d_CutOffDate Date := '1-jan-2004';

Is it possible to configure the format for Oracle session so it would not depend on OS settings?

Thanks for your help in advance.

Upvotes: 1

Views: 835

Answers (1)

Justin Cave
Justin Cave

Reputation: 231851

Don't rely on implicit data type conversion. If you want to declare a date, use a date. Either using a date literal or via an explicit conversion

d_CutOffDate Date := date '2004-01-01';

or

d_CutOffDate Date := to_date( '1-jan-2004', 'DD-MON-YYYY' );

Of course, the latter assumes that your session is configured to use English (otherwise the three-letter abbreviation would be different). If you're building applications that may be accessed by individuals with non-English session settings, using a numeric month generally makes more sense.

Upvotes: 2

Related Questions