Passing comma delimtied string to a stored procedure

It would be nice if we could always avoid dynamic SQL in our PL/SQL. The execution path would be mostly known upfront, it would be so easy debug and troubleshoot the code, etc. However, there are times when it seems almost impossible to write SQL queries in PL/SQL without relying on dynamic SQL functionality. One of the great examples is passing multiple ids separated by some delimiter (mostly by a comma).

Let’s consider a stored procedure with the following signature:

PROCEDURE sp_get_empployees(p_dept_ids VARCHAR2, p_result OUT SYS_REFCURSOR)

We need to retrieve all employees who work in one of the departments with department numbers listed in p_dept_ids line.

The query may look something like this:

SELECT empno, ename, job, deptno
FROM emp
WHERE deptno IN ([list of ids])
ORDER BY deptno, ename

Let’s first review a “traditional” approach based on dynamic SQL:

CREATE OR REPLACE PROCEDURE sp_get_empployees(p_dept_ids VARCHAR2, p_result OUT SYS_REFCURSOR)
AS
  v_sql VARCHAR2(2000);
BEGIN
  v_sql:='SELECT empno, ename, job, deptno
	  FROM emp
	  WHERE deptno IN (' || p_dept_ids || ')
	  ORDER BY deptno, ename';
  OPEN p_result FOR v_sql;

END sp_get_empployees;
/

If you need to test this procedure in SQL*Plus, here is how to do it:

SQL> var c refcursor
SQL> exec sp_get_empployees('10,20', :c)

PL/SQL procedure successfully completed.

SQL> print c

     EMPNO ENAME      JOB           DEPTNO
---------- ---------- --------- ----------
      7782 CLARK      MANAGER           10
      7839 KING       PRESIDENT         10
      7934 MILLER     CLERK             10
      7876 ADAMS      CLERK             20
      7902 FORD       ANALYST           20
      7566 JONES      MANAGER           20
      7788 SCOTT      ANALYST           20
      7369 SMITH      CLERK             20

8 rows selected.

Everything seems to look good at the first glance, but there are some issues.

What if p_dept_ids value is NULL (empty) which could mean that we don’t want any employees to be returned back?

Passing NULL will lead to an error:

SQL> exec sp_get_empployees('', :c)
BEGIN sp_get_empployees('', :c); END;

*
ERROR at line 1:
ORA-00936: missing expression
ORA-06512: at "SCOTT.SP_GET_EMPPLOYEES", line 9
ORA-06512: at line 1

Yes, we could add a validation of the input parameter, and this is not a bad idea in general. The main purpose of this post is to show you a workaround that would not rely on the dynamic SQL at all and also it won’t use the input parameter validation.
We are going to leverage the power of regular expressions to split the comma delimited line:

CREATE OR REPLACE PROCEDURE sp_get_empployees2(p_dept_ids VARCHAR2, p_result OUT SYS_REFCURSOR)
AS
BEGIN
  OPEN p_result FOR 
  SELECT empno, ename, job, deptno
  FROM emp
  WHERE deptno IN (SELECT REGEXP_SUBSTR(p_dept_ids, '[^,]+', 1, LEVEL) deptno
                   FROM dual
                   CONNECT BY LEVEL <= LENGTH(p_dept_ids) - LENGTH(REPLACE(p_dept_ids,','))+1 
                  )
  ORDER BY deptno, ename;
END sp_get_empployees2;
/

Here is the test:

SQL> exec sp_get_empployees2('10,20', :c)

PL/SQL procedure successfully completed.

SQL> print c

     EMPNO ENAME      JOB           DEPTNO
---------- ---------- --------- ----------
      7782 CLARK      MANAGER           10
      7839 KING       PRESIDENT         10
      7934 MILLER     CLERK             10
      7876 ADAMS      CLERK             20
      7902 FORD       ANALYST           20
      7566 JONES      MANAGER           20
      7788 SCOTT      ANALYST           20
      7369 SMITH      CLERK             20

8 rows selected.

SQL> exec sp_get_empployees2('', :c)

PL/SQL procedure successfully completed.

SQL> print c

no rows selected

In subsequent posts I will show some more workarounds to the presented solution.

If you want to learn how to come up with numerous workarounds on your own, check my book “Oracle SQL Tricks and Workarounds” for instructions.