Bind Variables in Dynamic SQL

[balajiyes]

i wud like to use bind variables in dynamic sql with an IN clause. for example,

declare
q varchar2(1000) := 'select count(*) from emp where sal in (:1)';
n number;
w varchar2(1000) := '1250,500,750';
begin
execute immediate q into n using w;
exception when others then
dbms_output.put_line(sqlerrm);
end;
/

the above pl/sql block gives the foll. error:
ORA-01722: invalid number

can someone throw some lite on why this happens?

[ales]

See
http://asktom.oracle.com/pls/ask/f?p...A:110612348061

[Mnemonical]

try one bind variable for each parameter:

declare
q varchar2(1000) := 'select count(*) from emp where sal in (:a, :b, :c)';
n number;
a varchar2(10) := '1250';
b varchar2(10) := '500';
c varchar2(10) := '750';
begin
execute immediate q into n using a, b, c;
end;


F.