Re: Tricky Dynamic SQL question

On 26 Dec 2002 10:48:25 -0800, rgaffuri_at_cox.net (Ryan Gaffuri) wrote:
>I thought I read on asktom, that pl/sql automatically takes pl/sql
>variables and makes them bind variables so I really dont have to use
>the 'using' command?

If you just code the statement directly in PL/SQL, PL/SQL turns the variables into bind variables:

select col1 into x from table1 where var1 = var2; for item in (select col1 from table1 where var1 = var2) loop...

The PL/SQL parser can tell pretty easily which things in the WHERE clauses are variables. But when you build up a string through concatenation and pass it to EXECUTE IMMEDIATE, this information isn't possible to determine at compile time.

EXECUTE IMMEDIATE 'delete from table1 ' || where_clauses;

How many variables are in the statement above, and what are their types? Can't tell.

EXECUTE IMMEDIATE 'insert into table2 select * from emp where empno > ' || something;

Does "something" represent a literal, a variable, a subselect...? Maybe it turns into 5 additional WHERE clauses and a UNION.

When you put placeholders in the EXECUTE IMMEDIATE string and call out the variables with USING, then there's no ambiguity.

[That's my understanding anyway.]

John Received on Thu Dec 26 2002 - 16:41:55 CST