Executing Dynamic Sql query in Oracle -

i have select query in have a dynamic where condition. thing when try concatenate where condition parameter sql query not allowing me save procedure.

eg:

create procedure usp_mysearchquery (   qtype in int,   out_cur out sys_refcursor ) dyn_query varchar2; begin     if qtype=1        dyn_query :=' between 1 , 2';     else        dyn_query :='=10';         end if;      open out_cur          select * mytable type=qtype , id || dyn_query; end; 

this how procedure looks like. tried execute immediete in documentation itself, written wont work multiple row query.

in mssql have exec(not sure) command can execute text sent command. in same way have commands can run dynamic query in oracle

---

update: answer 

---

i tried this.

  open out_cur         ' select * mytable type=:qtype , id ' || dyn_query           using qtype; 

and worked

the dynamic string has enclosed within 'single quotes'

open out_cur      'select * mytable id '|| dyn_query; 

execute immediate allows multi row result, if use bulk collect

example:

declare type myarray   table of varchar2(100);   v_array myarray; begin   execute immediate 'select ''x'' dual union select ''y'' dual'     bulk collect v_array;   in 1..v_array.count   loop     dbms_output.put_line(v_array(i));   end loop; end; 

execute immediate using bind variables;

string := 'select * emp name = :name , age = :age , :name <> ''mahesh''';  execute immediate string using 'mahi',21,'mahi'; <or> execute immediate string using proc_variable1,proc_variable2,proc_variable1;