DBA Hub

📋Steps in this guide1/4

Dynamic IN-Lists

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

oracle miscconfigurationintermediate
by OracleDba
15 views
1

Setup

The examples in this article require the following table.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
--DROP TABLE emp PURGE;

CREATE TABLE emp (
  empno    NUMBER(4) CONSTRAINT pk_emp PRIMARY KEY,
  ename    VARCHAR2(10),
  job      VARCHAR2(9),
  mgr      NUMBER(4),
  hiredate DATE,
  sal      NUMBER(7,2),
  comm     NUMBER(7,2),
  deptno   NUMBER(2)
);

INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
COMMIT;
2

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. 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. The following query shows the table function in action. 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. The following query shows the pipelined table function in action. Adrian Billington pointed out that in Oracle 10g the condition can be used to replace the sub-select, as shown below. The 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 . The following is a complete example using a number in-list.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
CREATE OR REPLACE TYPE t_vc_in_list_tab AS TABLE OF VARCHAR2 (4000);
/

CREATE OR REPLACE FUNCTION vc_in_list (p_in_list  IN  VARCHAR2)
  RETURN t_vc_in_list_tab
AS
  l_tab   t_vc_in_list_tab := t_vc_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;
/

SELECT *
FROM   emp
WHERE  job IN (SELECT * FROM TABLE(vc_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>

CREATE OR REPLACE FUNCTION vc_in_list (p_in_list  IN  VARCHAR2)
  RETURN t_vc_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;
/

SELECT *
FROM   emp
WHERE  job IN (SELECT * FROM TABLE(vc_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>

SELECT *
FROM   emp
WHERE  job MEMBER OF vc_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>

CREATE OR REPLACE TYPE t_num_in_list_tab AS TABLE OF NUMBER(5);
/

CREATE OR REPLACE FUNCTION num_in_list (p_in_list  IN  VARCHAR2)
  RETURN t_num_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 (TO_NUMBER(TRIM(SUBSTR(l_text, 1, l_idx - 1))));
    l_text := SUBSTR(l_text, l_idx + 1);
  END LOOP;

  RETURN;
END;
/

SELECT *
FROM   emp
WHERE  empno IN (SELECT * FROM TABLE(num_in_list('7499, 7698, 7782')))
ORDER BY ename;

     EMPNO ENAME      JOB              MGR HIREDATE                     SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------------------- ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 20-FEB-81                   1600        300         30
      7698 BLAKE      MANAGER         7839 01-MAY-81                   2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81                   2450                    10

3 rows selected.

SQL>
3

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 privilege. Next, create a package to manage the context. Next, do a quick test of the context using the following query. Once you are happy that the context is working correctly, create a row and table type that matches your expected output. 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. The following query shows the pipelined table function in action. 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. The following is a complete example using a number in-list.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
CREATE OR REPLACE CONTEXT parameter USING context_api;

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

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

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

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

1 row selected.

SQL>

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;
/

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;
/

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>

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 empno IN (';

  LOOP
    l_idx := INSTR(l_text, ',');
    EXIT WHEN NVL(l_idx, 0) = 0;
    context_api.set_parameter('empno' || l_count,TRIM(SUBSTR(l_text, 1, l_idx - 1)));
    l_sql := l_sql || 'SYS_CONTEXT(''parameter'', ''empno' || 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;
/

SELECT *
FROM   TABLE(get_emp('7499, 7698, 7782'));

     EMPNO ENAME      JOB              MGR HIREDATE                     SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------------------- ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 20-FEB-81                   1600        300         30
      7698 BLAKE      MANAGER         7839 01-MAY-81                   2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81                   2450                    10

3 rows selected.

SQL>
4

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. Next, create a stored procedure to poplate the temporary table. Finally, execute the procedure and include the temporary table in a sub-select to return the correct IN-list data. The temporary table is populated as a separate action, which is a major disadvantage in some situations. The following is a complete example using a number in-list. For more information see: Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
CREATE GLOBAL TEMPORARY TABLE vc_in_list_data (
  element  VARCHAR2(4000)
);

CREATE OR REPLACE PROCEDURE setup_vc_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 vc_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 vc_in_list_data (element)
    VALUES (l_element);
  END LOOP;
END;
/

EXEC setup_vc_in_list('SALESMAN, MANAGER');

PL/SQL procedure successfully completed.

SELECT *
FROM   emp
WHERE  job IN (SELECT * FROM vc_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>

CREATE GLOBAL TEMPORARY TABLE num_in_list_data (
  element  NUMBER(5)
);

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

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

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

EXEC setup_vc_in_list('7499, 7698, 7782');

SELECT *
FROM   emp
WHERE  empno IN (SELECT * FROM vc_in_list_data)
ORDER BY ename;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10

3 rows selected.

SQL>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!