Using comma separated string as a bind variable for SQL query with IN clause

Environment (JDeveloper 11.1.2.0.0, hr schema)

Bind variables are place holders in the SQL string whose value  can be easily changed at runtime without altering the query itself. Since the query text doesn’t change from execution to execution, the database can efficiently reuse the same parsed statement each time (parse once and execute many times). But how can we define a bind variable for IN clause, as an example suppose that we want to pass a comma-separated string of department IDs and execute the query based on our IN clause, and here is the query we wish to execute look like:

select * from departments where department_id IN (:comma-separated-list)

Fortunately, I have found how to do this based on  Steve Muench’s example #126.Using Comma-Separated String Bind for Variable IN List and in this post I am only repeating what Steve has shown before for the sake of sharing. Our goal is to pass a comma-separated string of department IDs at run time and execute our query.

To support bind variable IN clause we need to add the function below to our schema, this function accepts a comma-separated string argument and returns a NUM_TABLE as its result and this allows us to make our bind variable of String type.

CREATE TYPE num_table AS TABLE OF NUMBER;
/
CREATE OR REPLACE FUNCTION in_number_list (p_in_list  IN  VARCHAR2)
RETURN num_table
AS
l_tab   num_table := num_table();
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) := to_number(TRIM(SUBSTR(l_text, 1, l_idx - 1)));
l_text := SUBSTR(l_text, l_idx + 1);
END LOOP;
RETURN l_tab;
END;
/

our DepartmentsView should look like this

SELECT Departments.DEPARTMENT_ID,
Departments.DEPARTMENT_NAME,
Departments.MANAGER_ID,
Departments.LOCATION_ID
FROM DEPARTMENTS Departments
WHERE DEPARTMENT_ID IN (SELECT * FROM TABLE( CAST ( in_number_list(:CommaSeparatedListOfDeptId) as num_table)))

where CommaSeparatedListOfDeptId is a bind variable of type String.

DepartmentsView

you can test the view using the ADF Model Tester and pass a comma-separated string of departments IDs as shown below:

Oracle ADF Model Tester

About these ads

2 Comments

Filed under ADF

2 responses to “Using comma separated string as a bind variable for SQL query with IN clause

  1. Pingback: Passing comma separated string as bind variable for VO query’s IN operator « oracle fusion identity

  2. Ghadah

    Thanks for your post.
    you could consider using regular expression as well
    example:

    select regexp_substr(:bind_var,'[^,]+’, 1, level)
    from dual
    connect by
    regexp_substr(:bind_var, ‘[^,]+’, 1, level)
    is not null

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s