SQL & PL/SQL

Hello all,

I have some SQL that will have to run dynamically, which I can get to work by building a long statement, then getting it hard-parsed

example:

sql_stmt VARCHAR2(256) :=
'select distinct some_id from table1
where lname = ''Smith''
and fname in(''Joe'', ''Joey'', ''Joseph'')
and ssn = 111223333';

execute immediate sql_stmt into new_id_value;

the above state executes fine and returns the desired result.


HOWEVER, when I try to use bind variables (this statement will be called several million times), I get a no rows returned error.

I use bind variables as follows:

sql_stmt VARCHAR2(256);
vlname VARCHAR2(50) := 'Smith';
vssn NUMBER := 111223333;
vfname VARCHAR2(100) := '''Joe'', ''Joey'', ''Joseph''';

sql_stmt :=
'select distinct some_id from table1
where lname = :vlname
and fname in(:vfname)
and ssn = :vssn;

execute immediate sql_stmt into new_id_value
using vlname, vfname, vssn;


I know the issue involves the 'in' clause because I've substituted hardcoded values in and it has worked.

any suggestions?