Thanks Connor.

When I implemented the same in the WHERE condition of my code it error-ed out
"ORA-00932: inconsistent datatypes: expected CHAR got SYS.ODCIVARCHAR2LIST"

Please find below some sample queries:

CREATE TABLE EMP
(
EMPNO NUMBER(4) ,
ENAME VARCHAR2(50 BYTE) ,
JOB VARCHAR2(50 BYTE) ,
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(10,2),
COMM NUMBER(10,2),
DEPTNO NUMBER(2) ,
REPORT_STATUS VARCHAR2(20 BYTE)
);

Insert into EMP
(EMPNO, ENAME, JOB, MGR, HIREDATE,
SAL, COMM, DEPTNO, REPORT_STATUS)
Values
(7369, 'SMITH', 'CLERK', 7902, TO_DATE('06/27/2017 18:42:12', 'MM/DD/YYYY HH24:MI:SS'),
800, 0, 20, 'new');
Insert into EMP
(EMPNO, ENAME, JOB, MGR, HIREDATE,
SAL, COMM, DEPTNO, REPORT_STATUS)
Values
(7499, 'ALLEN', 'SALESMAN', 7698, TO_DATE('06/26/2017 18:42:53', 'MM/DD/YYYY HH24:MI:SS'),
1600, 300, 30, 'sent');
Insert into EMP
(EMPNO, ENAME, JOB, MGR, HIREDATE,
SAL, COMM, DEPTNO, REPORT_STATUS)
Values
(7521, 'WARD', 'SALESMAN', 7698, TO_DATE('06/25/2017 18:42:53', 'MM/DD/YYYY HH24:MI:SS'),
1250, 500, 30, 'sent');

commit;

select * from emp
where report_status =
(CASE
WHEN ( :lv_empno IS NULL
AND :lv_ename IS NULL)
THEN
sys.odcivarchar2list('new')
else sys.odcivarchar2list('new','sent')
end);



When I pass NULL to all the parameters, it must pick the data with status 'new'.
When I pass a non-NULL value to all the parameters then it could pick data with status 'new' or 'sent'

Thanks

SQL> select *
  2  from table(
  3  select
  4  case
  5  when to_char(sysdate,'SS') > 30 then sys.odcivarchar2list('Sent')
  6  else sys.odcivarchar2list('New','Sent')
  7  end options
  8  from dual
  9  );

COLUMN_VALUE
--------------------------------------------------------------------------
New
Sent

select *
from emp
where report_status in 
(
select *
from table(
select
case
when to_char(sysdate,'SS') > 30 then sys.odcivarchar2list('Sent')
else sys.odcivarchar2list('New','Sent')
end options
from dual
)
)

You don't know how many hours i wasted just to find your solution.....

Hi
I used the method shown in the example for one of my queries but got an error. Please help. I need to use this query in my APEX parameter driven interactive report.

Query:

select *
from table(
select
case
when to_number(:P70_CURRENT_STATUS) = 1
then sys.odcivarchar2list(
'Base Period', 'Evergreen', 'Option Period (Annual)',
'Option Period (Monthly)', 'Option Period (up to ''n'' Days)',
'Until Canceled', 'Board Extension'
)
when to_number(:P70_CURRENT_STATUS) = 2
then sys.odcivarchar2list( 'Base Period', 'Canceled', 'Evergreen', 'Expired', 'Option Period (Annual)',
'Option Period (Monthly)', 'Option Period (up to ''n'' Days)', 'Until Canceled',
'Board Extension'
)
end options
from dual
);


Error:
ORA-22905: cannot access rows from a non-nested table item
22905. 00000 - "cannot access rows from a non-nested table item"
*Cause: attempt to access rows of an item whose type is not known at
parse time or that is not of a nested table type
*Action: use CAST to cast the item to a nested table type
Error at Line: 2 Column: 6

select * from table ( 
  sys.odcivarchar2list (
    'Base Period',
    'Evergreen',
    'Option Period (Annual)',
    'Option Period (Monthly)',
    'Option Period (up to ''n'' Days)',
    'Until Canceled',
    'Board Extension'
  )
)
where to_number ( :p70_current_status ) = 1
union all
select * from table ( 
  sys.odcivarchar2list (
    'Base Period',
    'Canceled',
    'Evergreen',
    'Expired',
    'Option Period (Annual)',
    'Option Period (Monthly)',
    'Option Period (up to ''n'' Days)',
    'Until Canceled',
    'Board Extension'
  )
)
where to_number ( :p70_current_status ) = 2

You same my life.

Thank you so much for your quick response.

Your help is greatly appreciated.