Re: Functions
From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Thu, 26 Feb 2009 23:07:29 +0100
Message-ID: <70oi59Ffsn3uU1_at_mid.individual.net>
On 25.02.2009 21:39, Bobby Z. wrote:
> On Feb 23, 8:02 pm, Robert Klemme <shortcut..._at_googlemail.com> wrote:
>
> 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;
> /
Date: Thu, 26 Feb 2009 23:07:29 +0100
Message-ID: <70oi59Ffsn3uU1_at_mid.individual.net>
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 Received on Thu Feb 26 2009 - 16:07:29 CST