Plsql / jdbc date input argument as string

Question

I have a sql that retrieves data based on certain date and my Java application calls a plsql — Oracle to do so.

The new request is to get data based on more than one date selection made on the screen .

I tried to form these dates as comma separated string and pass this as an varchar input argument ‘I_dates’for pl/SQL . At Pl/sql end I tried using IN clause for the where condition but I get an error that I_dates is a invalid identifier.

Question : I tried running the sql separately using multiple date conditions and it works fine .

Select * from table1 where date in (‘2019-05-01’, ‘2019-06-01’) works fine.

But the same sql inside the plsql with the date input argument (I_dates where value of I_dates is ‘2019-05-01’, ‘2019-06-01’) does not work and says I_date is invalid identifier

How can I achieve getting results for multiple date conditions in pl/sql

A bind variable is a single value - in your case, a single date. If you have 2 dates (2 variables...) , then you need 2 bind variables. A single bind variable cannot represent a list of dates - that is causing the error. Check this [question](https://stackoverflow.com/questions/4973809/sqlplus-using-a-bind-variable-in-in-clause) — Koen Lostrie, Nov 10 '21 at 19:43

score 0 · Answer 1 · answered Nov 18 '21 at 12:10

You can use a single bind variable and the lookup date using a little trick. Example:

create table mytable (id number, datevalue varchar2(200));

insert into mytable values (1, '29-AUG-2021');
insert into mytable values (2, '01-NOV-2021');
insert into mytable values (3, '15-DEC-2021');
insert into mytable values (4, '21-DEC-2021');
insert into mytable values (5, '01-JAN-2022');


select *
from MYTABLE
where ','||'29-AUG-2021,30-AUG-2021,01-NOV-2021'||',' like '%'||datevalue||'%'

  ID    DATEVALUE
----  -----------
   1  29-AUG-2021
   2  01-NOV-2021

In this case, I have specified '29-AUG-2021,30-AUG-2021,01-NOV-2021' as the multiple field bind variable. You can put a bind variable in this location as wel.

Of course, you have to make sure that the NLS settings and format for the DATE that you put in are always the same as the example will not give a result if you put '01-01-2022' in as the bind variable.

Plsql / jdbc date input argument as string

1 Answers1