The problem with using "execute immediately" in PL SQL procedure

Question

I use the following code in a PL SQL procedure:

execute immediate 'select count(distinct item_type) into counter_variable
from items where ' || field_name || ' is not null'

Here

counter_variable is declared in the declaration section of the field_name procedure - this is the IN parameter for the PL SQL procedure, and the values ​​passed will be the column names from the "items" table

The error I am getting is an "Invalid SQL expression" and I cannot understand the reason. Any ideas?

thank

Answer 1

A clause intois PL / SQL and is not valid in an SQL statement. Try the following:

execute immediate 'select count(distinct item_type) 
from items where ' || field_name || ' is not null' into counter_variable