This article presents a number of methods for parameterizing the IN-list of a query.
The examples in this article require the following table.
--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;
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_vc_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 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; /
The following query shows the table function in action.
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>
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 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; /
The following query shows the pipelined table function in action.
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>
Adrian Billington pointed out that in Oracle 10g the MEMBER
condition can be used to replace the sub-select, as shown below.
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>
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.
The following is a complete example using a number in-list.
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>
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.
The following is a complete example using a number in-list.
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>
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 vc_in_list_data ( element VARCHAR2(4000) );
Next, create a stored procedure to poplate the temporary table.
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; /
Finally, execute the procedure and include the temporary table in a sub-select to return the correct IN-list data.
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>
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.
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>
For more information see:
Hope this helps. Regards Tim...
Back to normal view: https://oracle-base.com/articles/misc/dynamic-in-lists