On Feb 22, 6:56*am, yf... (AT) vtn1 (DOT) victoria.tc.ca (Malcolm Dew-Jones)
wrote:

It wouldn't - the SQL engine will see *bind* variables the PL/SQL
engine automatically creates and binds for PL/SQL variables in scope.
Functions can not be "bound" dynamically, so they must be declared
globally to be visible to the SQL engine.

This is not a PL/SQL implementation limitation, this is how SQL works.
Compare this to SQL calls in any other language: can you call your
local functions in, say, Java or C from an SQL statement that your
program executes (rhetorical question)? Yet if you properly bind your
local variables, SQL "has access" to them. That this access actually
involves variables values being copied to and from the SQL engine is
more obvious in other languages, but PL/SQL uses the same mechanism
for passing local variables to the SQL engine and has the same
limitations for local functions.

Actually, this PL/SQL behavior is absolutely correct: local functions
are supposed to be invisible to outside callers, including SQL engine.
After all, functions are not variables, which are mere placeholders
for the data, they are potentially capable of modifying database or
package state and/or produce different results depending on database
or package state when executed (RESTRICT_REFERENCES pragma, rings a
bell for anyone? so they obviously should be treated differently.

Regards,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com

On Feb 23, 8:02*pm, Robert Klemme <shortcut... (AT) googlemail (DOT) 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;
/

Regards,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com

Bobby Z. (vladimir.zakharychev (AT) gmail (DOT) com) wrote:
: On Feb 22, 6:56=A0am, yf... (AT) vtn1 (DOT) victoria.tc.ca (Malcolm Dew-Jones)
: wrote:

: > I noted that the sql statement he is building would have access to any
: > variables that are in scope within the do_something procedure.....

: It wouldn't - the SQL engine will see *bind* variables the PL/SQL
: engine automatically creates and binds for PL/SQL variables in scope.
: Functions can not be "bound" dynamically, so they must be declared
: globally to be visible to the SQL engine.

: >
: > The fact that the *function* is not in scope, even though in virtually an=
: y
: > other language a construct such as that would be in scope, and indeed eve=
: n
: > though pl/sql itself is able to recognize some items (local variables) =
: =A0
: > as being in scope, and indeed even though pl/sql itself has at least one
: > work around for another kind of object (types) so as to make it _appear_
: > to be in scope even when it isn't - the fact that the function is not in
: > scope is a limitation of the implementation of the pl/sql language.
: >

: This is not a PL/SQL implementation limitation, this is how SQL works.
: Compare this to SQL calls in any other language: can you call your
: local functions in, say, Java or C from an SQL statement that your
: program executes (rhetorical question)?


According to Oracle (** emphsis is mine)

(http://www.oracle.com/technology/tech/pl_sql/index.html)

"PL/SQL is an imperative 3GL that was designed specifically for
the ** seamless ** processing of SQL commands.

From another of their many documents

"Advantages of PL/SQL ...
** Tight integration ** with Oracle "

and

"The PL/SQL and SQL languages are ** tightly ** integrated


So I don't see a reason to compare pl/sql with Java or C in this regards.

The "seamless" processing of an an SQL statement that is within the scope
of a package would be for it to be able to "see" the parts of the package
that are in scope for any other type of statement.

Having to do something any more than that to make it visible is pretty
much the definition of a seam.

Anyway, it doesn't matter.
$0.10

On 25.02.2009 21:39, Bobby Z. wrote:
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

On Feb 27, 1:07*am, Robert Klemme <shortcut... (AT) googlemail (DOT) com> wrote:
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