You can only use bind variable for literal values in your select list and for values in your WHERE clause. You cannot use them for table or column names (including non-literal references in the select list).
So, your first version of the statement:
s1 := 'select ' || p_select || ' from ' || p_entity ||
' where effectivestartdate <= :param1 and effectiveenddate >= :param1 and status = 1 ' || p_where;
is the correct version. The only way you could use a bind variable on your p_where is if that supplied parameter always contains a fixed number of values. You can't use a bind variable for the entire p_where value.
So, if p_where = 'col2 = 'ABC'', then you could instead:
and then supply a value ('ABC') on the USING clause of EXECUTE IMMEDIATE. But, again, you would always have to supply p_where or else check in the code if it was supplied and do something like:
if p_where is not null then
execute immediate ... using p_asatdate, p_where_value;
else
execute immediate ... using p_asatdate;
end if;
On the insert question, you can use bind variables, but you'll have to parse out the comma-separated list into distinct values.
p1 := 'insert into ' || p_entity || ' values (:val1, :val2, ..., :valn)';
-- parse out values into v_value_1, v_value_2, etc.
dbms_sql.bind_variable(cur, ':val1', v_value_1);
] 
Blog
Search
Help
Members
Register
Login
Home
