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