Pass comma separated list as parameter in Oracle function

Question

I'm trying to create a function to pass a list separated with commas as a parameter to the IN clause.

CREATE OR REPLACE EDITIONABLE FUNCTION "GET_EMPLOYEES_COUNT"(DEPT_STRING VARCHAR2) RETURN VARCHAR2 AS

count_emp VARCHAR2(10);

BEGIN

  select count(*) from employees where deptname IN (DEPT_STRING);

  RETURN count_emp;

END GET_EMPLOYEES_COUNT;

However, I am getting null when trying to call the following:

SELECT GET_EMPLOYEES_COUNT('''AGRICULTURE'',''IT''') FROM DUAL;

krokodilko · Answer 1 · 2018-05-25T17:38:53.253

0

Try:

CREATE OR REPLACE FUNCTION "GET_EMPLOYEES_COUNT"(DEPT_STRING VARCHAR2) 
RETURN VARCHAR2 AS

count_emp NUMBER;

BEGIN
    EXECUTE IMMEDIATE q'[DECLARE q number;
          begin  select count(*) INTO q from employees
          where deptname IN ( ]' || DEPT_STRING || q'[);
          :x := q;end;]'
    USING OUT count_emp;

   RETURN count_emp;

END GET_EMPLOYEES_COUNT;
/

Live demo: http://sqlfiddle.com/#!4/a1360/1

edited May 25 '18 at 17:38

answered May 25 '18 at 17:28

krokodilko

35,300
7
55
79

No. that will not work. – OldProgrammer May 25 '18 at 17:29
@OldProgrammer I've updated the answer, this time it is tested and worked. – krokodilko May 25 '18 at 17:39
This works, but doesn't use bind variables;) – Marmite Bomber May 25 '18 at 17:46
`GET_EMPLOYEES_COUNT( '1) OR EXISTS(SELECT 1 FROM passwords_table WHERE username = ''Alice'' AND hash = ''12345678''' )` is probably not the functionality you were intending to allow. – MT0 May 25 '18 at 18:42
deptname is of type varchar2 actually. I'm trying to make it work here: http://sqlfiddle.com/#!4/4802b/1 – user3234428 May 26 '18 at 09:23

Pass comma separated list as parameter in Oracle function

1 Answers1