Query to dynamic sql

Question

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;

Answer 1

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).

db<>fiddle

• 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