Dynamic IN Clause with Native Dynamic SQL

Question:
Hi Steven: How can I use a parameter that has multiple values in the IN clause for a column?
For example, p_id is the parameter for a proc and it has the value of 1,2,3. I want to use this p_id parameter in select * from a_table where id in p_id. Can I do that?
Answer:
Hi Ravi; there are several different ways to implement a dynamic IN clause in PL/SQL. I have created a package (in_clause.pks/pkb) that demonstrates each of these approaches. I list them below and show the implementation, as well as any issues of which you should be aware.
Run the in_clause.test_varieties procedure to exercise the programs and also compare their performance (see in_clause.tst for an example of calling this procedure). These programs are all built around the database objects defined in the in_clause_setup.sql script, namely:
CREATE TABLE in_clause_tab (
ID INTEGER,
title VARCHAR2(100),
description VARCHAR2(2000));

CREATE TYPE in_clause_ot AS OBJECT (
ID INTEGER
, title VARCHAR2 (100)
, description VARCHAR2 (2000)
);
/

CREATE OR REPLACE TYPE in_clause_tab_nt IS TABLE OF in_clause_ot;
/

CREATE OR REPLACE TYPE pky_nt IS TABLE OF INTEGER;
/
1. Use native dynamic SQL (NDS) to construct and execute a query with a list of values provided at run-time and concatenated into the query string. The result set is returned as a cursor variable.
FUNCTION nds_list (list_in IN VARCHAR2)
RETURN sys_refcursor
IS
retval sys_refcursor;
BEGIN
OPEN retval FOR 'SELECT * FROM in_clause_tab WHERE id IN ('
|| list_in
|| ')';

RETURN retval;
END nds_list;
Issues:
• Concatenation of an undifferentiated string into the WHERE clause opens the possibility of code injection: a clever user might pass code in the list_in argument that causes unintended activity to occur. A good way to avoid this problem is to bind variables, which I'll demonstrate later with DBMS_SQL.
• The overhead of executing this statement dynamically may make the solution undesirable.
2. Use native dynamic SQL as above, but in this case use BULK COLLECT to retrieve the rows more rapidly. Use DBMS_SQL to construct and execute a query with a list of values provided at runtime, relying in this case on bind variables. I offer two implementations: one that returns a nested table, populated by the BULK COLLECT statement; and a second that invokes the first function from within a query using the TABLE operator, allowing me to return the data as a cursor variable, which can be easily used from non-PL/SQL host environments.
FUNCTION nds_bulk_list (list_in IN VARCHAR2)
RETURN in_clause_tab_nt
IS
retval in_clause_tab_nt;
BEGIN
EXECUTE IMMEDIATE
'SELECT in_clause_ot (id, title, description)
FROM in_clause_tab WHERE id IN ('
|| list_in
|| ')'
BULK COLLECT INTO retval;

RETURN retval;
END nds_bulk_list;

FUNCTION nds_bulk_list2 (list_in IN VARCHAR2)
RETURN sys_refcursor
IS
retval sys_refcursor;
BEGIN
OPEN retval FOR
SELECT ic.ID, ic.title, ic.description
FROM TABLE (nds_bulk_list (list_in)) ic;

RETURN retval;
END nds_bulk_list2;
Issues:
• Again, there is overhead from executing dynamic SQL, although the use of BULK COLLECT should compensate nicely.
3. Use a nested table containing the list of values, employing CAST and TABLE to query from that collection inside my query.
FUNCTION nested_table_list (list_in IN pky_nt)
RETURN sys_refcursor
IS
retval sys_refcursor;
BEGIN
OPEN retval FOR
SELECT *
FROM in_clause_tab
WHERE ID IN (SELECT column_value
FROM TABLE (CAST (list_in AS pky_nt)));

RETURN retval;
END nested_table_list;
4. Use the Oracle Database 10g MEMBER OF syntax in place of the IN operator.
FUNCTION member_of_list (list_in IN pky_nt)
RETURN sys_refcursor
IS
retval sys_refcursor;
BEGIN
OPEN retval FOR
SELECT *
FROM in_clause_tab
WHERE ID MEMBER OF list_in;

RETURN retval;
END member_of_list;
Issues:
• This is a very elegant solution, but you should check the performance of this new feature in your installation and make sure it will meet your needs.
5. Use a static query with an "all-purpose" IN clause that contains up to the maximum 1,000 elements in the list, so you can use it for any number of items.
FUNCTION in_clause.static_in_list (list_in IN pky_nt)
RETURN sys_refcursor
IS
retval sys_refcursor;
BEGIN
OPEN retval FOR
SELECT *
FROM in_clause_tab
WHERE ID IN
(list_in (1)
, list_in (2)
...
, list_in (999)
, list_in (1000)
);

RETURN retval;
END static_in_list;
Issues:
• This is definitely NOT an elegant solution (and you will probably want to generate this code, which you can do by calling the in_clause.gen_static_in_query procedure; see in_clause_gen_query.sql for an example of calling this program), but it does avoid the problem of relying on dynamic SQL.
• You will need to identify a value that will never appear in the column you are checking against, and use that for any rows in the collection that are not populated with "real" values. NULL is probably a good choice for most columns, but make your choice carefully.
6. Use DBMS_SQL to construct and execute a query with a list of values provided at runtime, relying in this case on bind variables. This is the longest and most complicated solution, as is usually the case with DBMS_SQL, but it does avoid the problem of code injection, as we are binding individual variable values into the IN list.
FUNCTION dbms_sql_list (list_in IN VARCHAR2)
RETURN in_clause_tab_nt
IS
l_query VARCHAR2 (32767)
:= 'SELECT * FROM in_clause_tab WHERE id IN (';
l_cur PLS_INTEGER := DBMS_SQL.open_cursor;
l_feedback PLS_INTEGER;
l_ids pky_nt := pky_nt ();
l_row PLS_INTEGER;
l_onerow in_clause_tab%ROWTYPE;
retval in_clause_tab_nt := in_clause_tab_nt ();

PROCEDURE string_to_list ... see file for details...
END string_to_list;
BEGIN
-- Parse the delimited list to the collection.
string_to_list (list_in, l_ids);
l_row := l_ids.FIRST;

-- Build the list of bind variables.
WHILE (l_row IS NOT NULL)
LOOP
l_query := l_query || ':bv' || l_row || ',';
l_row := l_ids.NEXT (l_row);
END LOOP;
l_query := RTRIM (l_query, ',') || ')';

-- Define the columns to be queried.
DBMS_SQL.parse (l_cur, l_query, DBMS_SQL.native);
DBMS_SQL.define_column (l_cur, 1, 1);
DBMS_SQL.define_column (l_cur, 2, 'a', 100);
DBMS_SQL.define_column (l_cur, 3, 'a', 2000);

-- Bind each variable in the provided list.
l_row := l_ids.FIRST;
WHILE (l_row IS NOT NULL)
LOOP
DBMS_SQL.bind_variable (l_cur, ':bv' || l_row, l_ids (l_row));
l_row := l_ids.NEXT (l_row);
END LOOP;

-- Execute and then fetch each row.
l_feedback := DBMS_SQL.EXECUTE (l_cur);
LOOP
l_feedback := DBMS_SQL.fetch_rows (l_cur);
EXIT WHEN l_feedback = 0;

-- Retrieve individual column values and move them to the nested table.
DBMS_SQL.column_value (l_cur, 1, l_onerow.ID);
DBMS_SQL.column_value (l_cur, 2, l_onerow.title);
DBMS_SQL.column_value (l_cur, 3, l_onerow.description);
retval.EXTEND;
retval (retval.LAST) :=
in_clause_ot (l_onerow.ID
, l_onerow.title
, l_onerow.description
);
END LOOP;

DBMS_SQL.close_cursor (l_cur);
RETURN retval;
END dbms_sql_list;
Issues:
• As always with DBMS_SQL, an issue is the complexity of the code you have to write and thus maintain. It is, however, the most secure of the dynamic solutions, and may be worth that complexity.
Finally, there is one other option to keep in mind: for very long lists of values, you could also consider creating a global temporary table and use that as you would a regular relational table in a SQL-only solution.