pass multiple value in NVL2 function

I want to add multiple values in nvl2 function of oracle database.

For example i have created a table and added some values

create table test_class(name1 varchar2(30), class varchar2(30), points number);
    insert into test_class values('ABC1', 'FIRST', 3);
    insert into test_class values('ABC2', 'FIRST', 4);
    insert into test_class values('ABC3', 'SECOND', 1);
    insert into test_class values('ABC4', 'SECOND', 6);
    insert into test_class values('ABC5', 'SECOND', 8);
    insert into test_class values('ABC6', 'THIRD', 9);
    insert into test_class values('ABC7', 'THIRD', 2);

I am running below SQL query to get data

select * from test_Class where points in (nvl2(:point,:point,points))

above SQL query works only if i will pass a single value in :point. And same query will fail if i will add multiple values like :point will have value 2,4,8

Solution

That will not work as a bind variable represents a single scalar value; it does not represent a list of scalar values. If you bind 2,4,8 then you will get one string containing '2,4,8' and not three values.

Similarly, NLV2 accepts singular values in each argument; so using NVL2 is probably inappropriate (and it would be much simpler to compare OR :point IS NULL).

You can either, pass a string and look for a sub-string match (including surrounding delimiters so that ensure that you match entire terms):

select *
from   test_Class
where  :point IS NULL
OR     ',' || :point || ',' LIKE '%,' || points || ',%'

or you can pass a collection to the bind variable, for example a SYS.ODCINUMBERLIST collection, and then use:

select *
from   test_Class
where  :point IS NULL
OR     points in (SELECT COLUMN_VALUE FROM TABLE(:point))

Or, if you pass a user-defined table-type collection then you could use the MEMBER OF operator (but it does not work with VARRAYs, which is what the SYS.ODCI*LIST types are defines as):

select *
from   test_Class
where  :point IS NULL
OR     points MEMBER OF :point

fiddle