Dynamic queries with an unknown number of inputs...

Sometimes there is a need to pass dynamic number of inputs to a query, based on conditions satisfied or data from some other source/query, as the program executes. Here, we will see how to pass dynamic/unknown number of inputs to a query.

Let's see how to do it. We assume a situation where we get inputs, to be passed to a dynamic query, from a different select query.  

DECLARE CURSOR c_fetch_ids IS 

SELECT ids FROM employee WHERE name LIKE 'John %'; 

v_counter NUMBER; 

d NUMBER; 

v_dyn_stmt VARCHAR2(1000); 

v_cur NUMBER; 

v_temp employee.ids%TYPE; 

v_tab_ids DBMS_SQL.NUMBER_TABLE; 

bind_names DBMS_SQL.VARCHAR2_TABLE; 

v_names DBMS_SQL.VARCHAR2_TABLE; 

v_salaries DBMS_SQL.NUMBER_TABLE; 

BEGIN v_counter := 0; 

OPEN c_fetch_ids; 

LOOP FETCH c_fetch_ids INTO v_temp; 

EXIT WHEN c_fetch_ids%NOTFOUND; 

v_counter := v_counter+1; 

v_tab_ids(v_counter) := v_temp; 

END LOOP; CLOSE c_fetch_ids; 

v_dyn_stmt := 'SELECT name, salary FROM employee WHERE ids IN ('; 

FOR v_counter IN 1 .. v_tab_ids.count 

LOOP 

bind_names(v_counter) := v_counter; 

IF v_counter=1 

THEN 

v_dyn_stmt := v_dyn_stmt||' :1'; 

ELSE v_dyn_stmt := v_dyn_stmt||' ,:'||v_counter; 

END IF; 

END LOOP; 

v_dyn_stmt := v_dyn_stmt||')'; 

v_cur := DBMS_SQL.OPEN_CURSOR; 

DBMS_SQL.PARSE(v_cur, v_dyn_stmt, DBMS_SQL.NATIVE); 

for v_counter IN 1 .. v_tab_ids.count 

LOOP 

DBMS_SQL.BIND_VARIABLE(v_cur,bind_names(v_counter),v_tab_ids(v_counter)); 

END LOOP; 

DBMS_SQL.DEFINE_ARRAY(v_cur, 1, v_names, 10, 1); 

DBMS_SQL.DEFINE_ARRAY(v_cur, 2, v_salaries, 10, 1); 

d := DBMS_SQL.EXECUTE(v_cur); 

LOOP 

d := DBMS_SQL.FETCH_ROWS(v_cur); 

DBMS_SQL.COLUMN_VALUE(v_cur, 1, v_names); 

DBMS_SQL.COLUMN_VALUE(v_cur, 2, v_salaries); 

EXIT WHEN d!=10; 

END LOOP; 

DBMS_SQL.CLOSE_CURSOR(v_cur); 

END;

The above block fetches names and salaries of employees, 10 at a time, after taking dynamic number of inputs(ids) which come from a different query with the condition that name should be starting as 'John %'.

For more info visit