Environment (JDeveloper 220.127.116.11.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.
you can test the view using the ADF Model Tester and pass a comma-separated string of departments IDs as shown below: