When try compaile my packages get error.
PLS-00306: wrong number or types of arguments in call to '||'
v_sql := 'select count(*) from table ('|| p_month_ids ||') where column_value = ' || p_month_id;
Execute Immediate v_sql into v_count;
Add a comment
|
1 Answer
You are trying to concatenate values into a string, but that can only work if each individual value is either a string or can be implicitly converted to a string. If p_month_ids is a collection - as it seems to be from how you're using it - then you would have to add each element individually - as you can't implicitly convert the p_month_ids collection to a string.
But don't do that; use bind variables:
v_sql := 'select count(*) from table (:p_month_ids) where column_value = :p_month_id';
execute immediate v_sql into v_count using p_month_ids, p_month_id;
Or don't use dynamic SQL at all:
select count(*) into v_count
from table (p_month_ids) where column_value = p_month_id;
Or loop over the collection and count matches directly, instead of using a query (and context switch).
answered Oct 3, 2022 at 12:54
3 Comments
babayaro
Tnx, work fine with bind variables and using parameters...... Is that good solution then eg. >> || p_month_ids ||
Alex Poole
You can't implicitly convert the
p_month_ids collection to a string, which is what you're trying to do. You would have to loop over the collection and append each value individually - including quoting for strings and formatting for dates etc. Which is messy, error-prone, and dangerous. Bind variables let the query be reused and guard against SQL injection. (And, again, you don't need dynamic SQL here at all.)babayaro
Tnx I'm use your second example from db<>fiddle