Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Tricky Dynamic SQL question
John Russell <netnews4_at_johnrussell.mailshell.com> wrote in message news:<7j0n0vkfhtu86mmui7kteusrt1dan46764_at_4ax.com>...
> 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
oh ok thanks. Ill change that. Its hard to keep track of what you read where sometimes. Received on Fri Dec 27 2002 - 06:31:19 CST