Dynamic IN-Lists

This article presents a number of methods for parameterizing the IN-list of a query.

Table Function Sub-Select
Dynamic SQL and Context
Temporary Table Sub-Select

Table Function Sub-Select

This approach uses a table function to return the individual elements of the IN-list as rows of data, allowing it to be queried in a sub-select.

First, create a table object as the return value for the table function.

CREATE OR REPLACE TYPE t_in_list_tab AS TABLE OF VARCHAR2 (4000);
/

Next, create the table function. This function accepts a comma-delimited string, which it splits and turns into rows in a table. Once the table is fully populated it is returned.

CREATE OR REPLACE FUNCTION in_list (p_in_list  IN  VARCHAR2)
  RETURN t_in_list_tab
AS
  l_tab   t_in_list_tab := t_in_list_tab();
  l_text  VARCHAR2(32767) := p_in_list || ',';
  l_idx   NUMBER;
BEGIN
  LOOP
    l_idx := INSTR(l_text, ',');
    EXIT WHEN NVL(l_idx, 0) = 0;
    l_tab.extend;
    l_tab(l_tab.last) := TRIM(SUBSTR(l_text, 1, l_idx - 1));
    l_text := SUBSTR(l_text, l_idx + 1);
  END LOOP;

  RETURN l_tab;
END;
/

The following query shows the table function in action.

SELECT *
FROM   emp
WHERE  job IN (SELECT * FROM TABLE(in_list('SALESMAN, MANAGER')))
ORDER BY ename;

     EMPNO ENAME      JOB              MGR HIREDATE                    SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------------- ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 20-FEB-1981 00:00:00       1600        300         30
      7698 BLAKE      MANAGER         7839 01-MAY-1981 00:00:00       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-1981 00:00:00       2450                    10
      7566 JONES      MANAGER         7839 02-APR-1981 00:00:00       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-1981 00:00:00       1250       1400         30
      7844 TURNER     SALESMAN        7698 08-SEP-1981 00:00:00       1500          0         30
      7521 WARD       SALESMAN        7698 22-FEB-1981 00:00:00       1250        500         30

7 rows selected.

SQL>

The following code uses a similar approach, but with a pipelined table function. The advantage here is that rows are piped to the query as they are produced, rather than building the entire table before the rows are returned.

CREATE OR REPLACE FUNCTION in_list (p_in_list  IN  VARCHAR2)
  RETURN t_in_list_tab PIPELINED
AS
  l_text  VARCHAR2(32767) := p_in_list || ',';
  l_idx   NUMBER;
BEGIN
  LOOP
    l_idx := INSTR(l_text, ',');
    EXIT WHEN NVL(l_idx, 0) = 0;
    PIPE ROW (TRIM(SUBSTR(l_text, 1, l_idx - 1)));
    l_text := SUBSTR(l_text, l_idx + 1);
  END LOOP;

  RETURN;
END;
/

The following query shows the pipelined table function in action.

SELECT *
FROM   emp
WHERE  job IN (SELECT * FROM TABLE(in_list('SALESMAN, MANAGER')))
ORDER BY ename;

     EMPNO ENAME      JOB              MGR HIREDATE                    SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------------- ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 20-FEB-1981 00:00:00       1600        300         30
      7698 BLAKE      MANAGER         7839 01-MAY-1981 00:00:00       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-1981 00:00:00       2450                    10
      7566 JONES      MANAGER         7839 02-APR-1981 00:00:00       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-1981 00:00:00       1250       1400         30
      7844 TURNER     SALESMAN        7698 08-SEP-1981 00:00:00       1500          0         30
      7521 WARD       SALESMAN        7698 22-FEB-1981 00:00:00       1250        500         30

7 rows selected.

SQL>

Adrian Billington pointed out that in Oracle 10g the MEMBER operator can be used to replace the sub-select, as shown below.

SELECT *
FROM   emp
WHERE  job MEMBER OF in_list('SALESMAN, MANAGER')
ORDER BY ename;

     EMPNO ENAME      JOB              MGR HIREDATE                    SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------------- ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 20-FEB-1981 00:00:00       1600        300         30
      7698 BLAKE      MANAGER         7839 01-MAY-1981 00:00:00       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-1981 00:00:00       2450                    10
      7566 JONES      MANAGER         7839 02-APR-1981 00:00:00       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-1981 00:00:00       1250       1400         30
      7844 TURNER     SALESMAN        7698 08-SEP-1981 00:00:00       1500          0         30
      7521 WARD       SALESMAN        7698 22-FEB-1981 00:00:00       1250        500         30

7 rows selected.

SQL>

The OF keyword is optional, but it makes the line scan better when reading the query. On small in-lists it's difficult to see performance differences, but for large in-lists the sub-select version performs better, as seen in Adrian's article.

Dynamic SQL and Context

An alternative approach is to enclose the whole query in a pipelined table function and use dynamic SQL to generate the query. In this example a context is used to give the advantages of bind variable, rather than concatinating the IN-list into the query.

First, create a context to hold our parameters. This requires the CREATE ANY CONTEXT privilege.

CREATE OR REPLACE CONTEXT parameter USING context_api;

Next, create a package to manage the context.

CREATE OR REPLACE PACKAGE context_api AS
PROCEDURE set_parameter(p_name   IN  VARCHAR2,
                        p_value  IN  VARCHAR2);
END context_api;
/
SHOW ERRORS


CREATE OR REPLACE PACKAGE BODY context_api IS

PROCEDURE set_parameter (p_name   IN  VARCHAR2,
                         p_value  IN  VARCHAR2) IS
BEGIN
  DBMS_SESSION.set_context('parameter', p_name, p_value);
END set_parameter;

END context_api;
/
SHOW ERRORS

Next, do a quick test of the context using the following query.

EXEC context_api.set_parameter('variable1','value1');

SELECT SYS_CONTEXT('parameter','variable1')
FROM   dual;

SYS_CONTEXT('PARAMETER','VARIABLE1')
------------------------------------
value1

1 row selected.

SQL>

Once you are happy that the context is working correctly, create a row and table type that matches your expected output.

CREATE OR REPLACE TYPE t_emp_row AS OBJECT (
  empno     NUMBER(4),
  ename     VARCHAR2(10),
  job       VARCHAR2(9),
  mgr       NUMBER(4),
  hiredate  DATE,
  sal       NUMBER(7,2),
  comm      NUMBER(7,2),
  deptno    NUMBER(2)
);
/

CREATE OR REPLACE TYPE t_emp_tab AS TABLE OF t_emp_row;
/

Next, create the pipelined table function to return the desired rows. Notice that a context variable is defined and included in the statement for each element of the IN-list parameter.

CREATE OR REPLACE FUNCTION get_emp (p_in_list  IN  VARCHAR2)
  RETURN t_emp_tab PIPELINED
AS
  l_row     emp%ROWTYPE;
  l_cursor  SYS_REFCURSOR;
  l_sql     VARCHAR2(32767);
  l_text    VARCHAR2(32767) := p_in_list || ',';
  l_idx     NUMBER;
  l_count   NUMBER := 1;
BEGIN
  l_sql := 'SELECT * FROM emp WHERE job IN (';

  LOOP
    l_idx := INSTR(l_text, ',');
    EXIT WHEN NVL(l_idx, 0) = 0;
    context_api.set_parameter('job' || l_count,TRIM(SUBSTR(l_text, 1, l_idx - 1)));
    l_sql := l_sql || 'SYS_CONTEXT(''parameter'', ''job' || l_count || '''),';
    l_count := l_count + 1;
    l_text := SUBSTR(l_text, l_idx + 1);
  END LOOP;

  l_sql := SUBSTR(l_sql, 1, LENGTH(l_sql) - 1) || ') ORDER BY ename';

  OPEN l_cursor FOR l_sql;
  LOOP
    FETCH l_cursor
    INTO  l_row;
    EXIT WHEN l_cursor%NOTFOUND;

    PIPE ROW (t_emp_row(l_row.empno,
                        l_row.ename,
                        l_row.job,
                        l_row.mgr,
                        l_row.hiredate,
                        l_row.sal,
                        l_row.comm,
                        l_row.deptno));
  END LOOP;
  CLOSE l_cursor;

  RETURN;
END;
/

The following query shows the pipelined table function in action.

SELECT *
FROM   TABLE(get_emp('SALESMAN, MANAGER'));

     EMPNO ENAME      JOB              MGR HIREDATE                    SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------------- ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 20-FEB-1981 00:00:00       1600        300         30
      7698 BLAKE      MANAGER         7839 01-MAY-1981 00:00:00       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-1981 00:00:00       2450                    10
      7566 JONES      MANAGER         7839 02-APR-1981 00:00:00       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-1981 00:00:00       1250       1400         30
      7844 TURNER     SALESMAN        7698 08-SEP-1981 00:00:00       1500          0         30
      7521 WARD       SALESMAN        7698 22-FEB-1981 00:00:00       1250        500         30

7 rows selected.

SQL>

This method will only support statement reuse if all statements have the same number of elements in the IN-list. If there is some variation in the length of the IN-list, several versions of the statement will appear in the shared pool.

Temporary Table Sub-Select

This approach uses a temporary table to hold the IN-list data, which is queried as part of a sub-select.

First, create a global temporary table.

CREATE GLOBAL TEMPORARY TABLE in_list_data (
  element  VARCHAR2(4000)
);

Next, create a stored procedure to poplate the temporary table.

CREATE OR REPLACE PROCEDURE setup_in_list (p_in_list  IN  VARCHAR2) AS
  l_text     VARCHAR2(32767) := p_in_list || ',';
  l_idx      NUMBER;
  l_element  VARCHAR2(32767);
BEGIN
  DELETE FROM in_list_data;

  LOOP
    l_idx := INSTR(l_text, ',');
    EXIT WHEN NVL(l_idx, 0) = 0;
    l_element := TRIM(SUBSTR(l_text, 1, l_idx - 1));
    l_text := SUBSTR(l_text, l_idx + 1);

    INSERT INTO in_list_data (element)
    VALUES (l_element);
  END LOOP;
END;
/

Finally, execute the procedure and include the temporary table in a sub-select to return the correct IN-list data.

EXEC setup_in_list('SALESMAN, MANAGER');

PL/SQL procedure successfully completed.

SELECT *
FROM   emp
WHERE  job IN (SELECT * FROM in_list_data)
ORDER BY ename;

     EMPNO ENAME      JOB              MGR HIREDATE                    SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------------- ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 20-FEB-1981 00:00:00       1600        300         30
      7698 BLAKE      MANAGER         7839 01-MAY-1981 00:00:00       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-1981 00:00:00       2450                    10
      7566 JONES      MANAGER         7839 02-APR-1981 00:00:00       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-1981 00:00:00       1250       1400         30
      7844 TURNER     SALESMAN        7698 08-SEP-1981 00:00:00       1500          0         30
      7521 WARD       SALESMAN        7698 22-FEB-1981 00:00:00       1250        500         30

7 rows selected.

SQL>

The temporary table is populated as a separate action, which is a major disadvantage in some situations.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.