Re: Functions

From: Bobby Z. <vladimir.zakharychev_at_gmail.com>
Date: Wed, 25 Feb 2009 12:32:30 -0800 (PST)
Message-ID: <cf84ca18-c769-4cec-9022-7a8233fbfff3_at_r18g2000vbi.googlegroups.com>

On Feb 22, 6:56 am, yf..._at_vtn1.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 any
> other language a construct such as that would be in scope, and indeed even
> though pl/sql itself is able to recognize some items (local variables)  
> 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)? 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 Received on Wed Feb 25 2009 - 14:32:30 CST