CREATE OR REPLACE PROCEDURE read_and_output(
input1 IN VARCHAR2, -- this is a mandatory field
input2 IN VARCHAR2,
input3 IN VARCHAR2,
OBJ1 OUT CUSTOM_OBJ_LIST
)
IS
TYPE t1 IS TABLE OF VARCHAR2(100);
t1_inst := t1();
pos_param :=1;
v_select_clause VARCHAR(1000);
v_from_clause VARCHAR(1000);;
v_where_in_clause VARCHAR(1000);;
v_where_clause VARCHAR(1000);
BEGIN
v_where_in_clause := ' where A.x = '|| pos_param;
t1_inst.EXTEND;
t1_inst(1) := input1;
IF input2 IS NOT NULL THEN
pos_param := pos_param + 1;
t1_inst.EXTEND;
t1_inst(pos_param) := input2;
v_where_clause := ' AND A.x =:' || POS_PARAM;
END IF;
IF input3 IS NOT NULL THEN
pos_param := pos_param + 1;
t1_inst.EXTEND;
t1_inst(pos_param) := input3;
v_where_clause := v_where_clause ||' AND A.y LIKE %:'|| pos_param ||'% ';
END IF;
v_select_clause:= 'SELECT obj1(col1, col2, col3) FROM
(SELECT cola as col1, colb as col2, colc as col3 )';
v_from_clause:= ' FROM A, B ';
v_where clause := v_where_clause|| ' AND A.key1 = B.Key1 ';
IF (POS_PARAM = 1) THEN
EXECUTE IMMEDIATE v_select_caluse||v_from_clause||v_where_in_clause||v_where_clause BULK COLLECT INTO obj1 USING t1_inst(1), t1_inst(2);
END IF;
IF (pos_param = 2) THEN
EXECUTE IMMEDIATE v_select_clause||v_from_clause||v_where_in_clause||v_where_clause BULK COLLECT INTO obj1 USING t1_inst(1), t1_inst(2), t1_inst(3);
END IF;
END;
When I call a similarly developed procedure with input3 or input2 & input3 parameter values, it is giving me **Bind variable does not exist**
I am not sure whether the issue is with the WHERE clause or the USING clause.Please help.