Problem using 'execute immediate' in PL SQL Procedure

Question

I am using 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 procedure field_name is the IN parameter to the PL SQL procedure and the values passed will be column names from the 'items' table

The error that I get is 'Invalid SQL Statement' and I am not able to figure out the reason. Any ideas?

Thanks

Klas Lindbäck · Accepted Answer · 2011-06-07 11:46:37Z

10

The into clause is PL/SQL and not valid in an SQL statement. Try this:

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

answered Jun 7, 2011 at 11:46

Klas Lindbäck

33.4k5 gold badges61 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Klas Lindbäck Over a year ago

Don't forget: If field_name is supplied by a user it has to be checked for SQL injection.

Collectives™ on Stack Overflow

Problem using 'execute immediate' in PL SQL Procedure

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related