Re: Functions
Date: Fri, 27 Feb 2009 01:23:42 -0800 (PST)
Message-ID: <f68aaca8-3890-4c2d-96f4-0fb17b577b3c_at_a12g2000yqm.googlegroups.com>
On Feb 27, 1:07 am, Robert Klemme <shortcut..._at_googlemail.com> wrote:
> On 25.02.2009 21:39, Bobby Z. wrote:
>
>
>
> > On Feb 23, 8:02 pm, Robert Klemme <shortcut..._at_googlemail.com> wrote:
> >> Right, the local variable is visible - the function is not. The
> >> difference is there only for direct usage of both - when using EXECUTE
> >> IMMEDIATE you're lost in both cases.
>
> > Not really: you can still reference your PL/SQL variables with EXECUTE
> > IMMEDIATE - but you need to explicitly bind them yourself (which PL/
> > SQL does for you automatically with native SQL):
>
> > declare
> > v sys.dual.dummy%type;
> > x sys.dual.dummy%type := 'X';
> > begin
> > execute immediate 'select dummy from sys.dual where dummy=:x'
> > INTO v
> > USING x;
> > dbms_output.put_line(v);
> > end;
> > /
>
> Maybe my wording was a bit too sloppy. Of course we are not lost
> because there is a solution. But: The statement in EXECUTE IMMEDIATE
> still does not have access to local or private variables. Instead, you
> propagate their values into the statement via the bind variable
> mechanism. That's similar how a function gets "access" to local
> variables defined outside: you hand their values into the function via
> parameters. No real access to local variables either.
>
> Regards
>
> robert
Exactly what I was trying to say: the mechanism is the same regardless. SQL can not see your PL/SQL variables, it only sees bind variables. PL/SQL silently rewrites your SQL statements and replaces all references to your local variables with corresponding bind variable placeholders, and transparently handles binding and fetching at runtime for you. For example, a SELECT statement in a block like this:
declare
v sys.dual.dummy%type;
x sys.dual.dummy%type := 'X';
begin
select dummy into v from sys.dual where dummy = x;
end;
is automatically rewritten into
SELECT DUMMY FROM SYS.DUAL WHERE DUMMY = :B1 and v and x are bound to this statement at runtime.
Regards,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com
Received on Fri Feb 27 2009 - 03:23:42 CST