00936. 00000 - "missing expression"

Question

I have written pl/sql function getEmpNums('3A,5B,6B') , This function will return string like this "EMP_NO IN ('3A','5B','6B')" . I want to use this function in where clause . like below

SELECT EMP_NO FROM EMP_CUST_VALUES  WHERE (select getEmpNums('3A,5B,6B') from dual)

when i call like below getting "ORA-00936: missing expression". how to call function in where clause like this

I tried

SELECT EMP_NO FROM EMP_CUST_VALUES  WHERE (select getEmpNums('3A,5B,6B') from dual)

Its not working when i give directly its working

SELECT EMP_NO FROM EMP_CUST_VALUES  WHERE (EMP_NO IN ('3A','5B','6B'))

Answer 1

Although function your wrote probably returns what you wanted, the principle you chose works in a limited number of cases (Oracle Forms, Reports, maybe somewhere else) where you can substitute function result with a bind variable.

Usually, you'd rather opt to return a collection and use it with a table function, either as a join or in a subquery. Here's an example.

Function first; I used built-in datatype:

SQL> create or replace function getempnums(par_str in varchar2)
  2    return sys.odcivarchar2list
  3  is
  4    retval sys.odcivarchar2list;
  5  begin
  6    select trim(regexp_substr(par_Str, '[^,]+', 1, level))
  7      bulk collect into retval
  8      from dual
  9      connect by level <= regexp_count(par_str, ',') + 1;
 10    return retval;
 11  end;
 12  /

Function created.

Sample table:

SQL> select empno, ename from emp where rownum <= 5;

     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN

What does function return? A collection:

SQL> select getempnums('7369,7499, 7521') from dual;

GETEMPNUMS('7369,7499,7521')
--------------------------------------------------------------------------------
ODCIVARCHAR2LIST('7369', '7499', '7521')

How to use it: table function:

SQL> select e.empno, e.ename
  2  from emp e join table(getempnums('7369,7499, 7521')) t on t.column_value = e.empno;

     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
      7521 WARD

Subquery:

SQL> select e.empno, e.ename
  2  from emp e
  3  where e.empno in (select * from table(getempnums('7369,7499, 7521')));

     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
      7521 WARD

SQL>