PLSQL Execute Immediate with Dynamic Using

Question

I am dynamically building a search query with bind variables with at least 1 and at most 7 different potential criteria. I know I can do this -

EXECUTE IMMEDIATE sql USING bind_var1, bind_var2 or
EXECUTE IMMEDIATE sql USING bind_var3, bind_var5, bind_var7. 

Is it possible to include the bind variables within the sql?

sql = 'SELECT * FROM table WHERE id = :bind_var1 AND name = :bind_var2 USING bind_var1, bind_var2'

and do

EXECUTE IMMEDIATE sql?  

I want and need to dynamically build the USING piece instead of writing a lot of IF THEN statements.

Answer 1

your USING bind_var1, bind_var2 pice of code should be out side os your sql string and come at the end of execute immediate statement and also for select senarios try to use dynamic sql for select with a cursor unless you want to select into a variable

Answer 2

According to your tags, I assume this will be used inside some kind of PL/SQL block. So, maybe are you looking for the open for statement.

This allows you to get a cursor on an dynamic query:

sql := 'SELECT * FROM table WHERE id = :bind_var1 AND name = :bind_var2';
open my_cursor for sql using bind_var1, bind_var2';

-- do whatever you need with your cursor