How do I read a PL/SQL NUMBER into a Java ResultSet?

Question

Our PL/SQL Database has three NUMBER variables that represent an exact Day, Month, and Year.

I want to read those values into a String in Java to display, but rs.getString("[Number_field]") trhows an "Invalid Column Name" exception, and rs.getInt("[Number_field]") does the same (See code snippet below). And, because the word "number" is used to describe every string conversion ever created in the history of coding, it is impossible for me to find a reference for the correct function to use for my ResultSet.

String sDate1= rs.getString("DATE_MNTH")+"/"+rs.getString("DATE_DT"+"/"+rs.getString("DATE_YEAR"));

Date date=new SimpleDateFormat("MM/DD/yyyy").parse(sDate1);

What is the correct way to read a PL/SQL NUMBER from a ResultSet?

What do you mean by **does not work**? If there is an exception please add full stacktrace to the question. — Ivan, Jan 24 '19 at 20:56

score 2 · Answer 1 · answered Jan 24 '19 at 20:57

According to the Oracle JDBC mappings table, Oracle's NUMBER type, which is an exact type, maps over to Java's BigDecimal. So, you may try using ResultSet#getBigDecimal:

String day = rs.getBigDecimal("day_field");
String month = rs.getBigDecimal("month_field");
String year = rs.getBigDecimal("year_field");

String output = year + "-" + month + "-" + day;

However, you might want to reconsider your current database design. Is there some reason why you are not just storing all this information within a single date or timestamp field? Had you done this, you could just retrieve a single date field from your result set, and avoid this problem completely.

The exact reason we're not doing this is that the table I'm talking about is supposed to represent the *exact* set of data that we send to an important legal group in our business - as for why we haven't just stored it as a date alongside those fields...poor project planning, and a slow Data Structure team. — Zibbobz, Jan 24 '19 at 21:02

score 0 · Answer 2 · answered Jan 24 '19 at 21:18

This actually turned out to be an error in how I returned these columns in the result set - which is to say, I forgot to add them to the query!

Once I did add them so that they were actually part of the resultset, I found that rs.getString() works perfectly fine - as is rs.getInt(), and the rs.getBigDecimal() suggested by Tim Biegeleisen.

Thank you to Ivan in the comments for getting me to re-read the error message that came up - and making me think about why the Column Name would be invalid.

Vadim · Answer 3 · 2019-01-24T21:39:27.597

You have a typo in your statement:

rs.getString("DATE_MNTH")+"/"+rs.getString("DATE_DT"+"/"+rs.getString("DATE_YEAR"));

you misplaced a closing ")" for DATE_DT column.

In real you try to get two columns out of rs:

DATE_MNTH and
result of concatenation which looks like: DATE_DT/{whatever rs.getString("DATE_YEAR") has} ...

as example "DATE_DT/2019"

I'm pretty sure there are no such column as "DATE_DT/2019" in Result Set... and exception tells you it.

BTW: there are no such things like "PL/SQL DB" (it is Oracle DB) and term "variable" has completelly different meaning from "column"...

How do I read a PL/SQL NUMBER into a Java ResultSet?

3 Answers3