Re: Functions
Date: Mon, 23 Feb 2009 09:02:35 -0800 (PST)
Message-ID: <d37adc45-e276-4724-985f-d44d092898ba_at_m42g2000yqb.googlegroups.com>
On 22 Feb., 04:56, yf..._at_vtn1.victoria.tc.ca (Malcolm Dew-Jones) wrote:
> Robert Klemme (shortcut..._at_googlemail.com) wrote:
>
> : On 21.02.2009 18:03, Malcolm Dew-Jones wrote:
> : > Robert Klemme (shortcut..._at_googlemail.com) wrote:
> : > : On 20.02.2009 22:03, gym dot scuba dot kennedy at gmail wrote:
> : > : > "Mtek" <m..._at_mtekusa.com> wrote in message
> : > : >news:c468a5f4-da3f-4027-99db-1f528bbf27d7_at_m40g2000yqh.googlegroups.com...
> : > : >> This is interesting. Say I have a package with a function in it. The
> : > : >> function is local to that package only and is not called from the
> : > : >> outside.
> : >
> : > : Here you have provided the explanation yourself.
> : >
> : > Hardly. After all, the sql statement has access to variables that are
> : > private but within scope.
>
> : I am not sure what you intend to say with this. The OP wrote about a
> : package private function which he attempted to call from outside of the
> : package. I do not see how code outside of a package can have access to
> : anything which is private to the package - be it a function, a variable
> : or something else. In fact, I would rather consider it a bug if
> : something private could be accessed from the outside.
>
> No, he said the function "is not called from the outside." I don't see
> any way to interpret that except that the line he mentioned must be built
> and used within his package.
Might well be.
> I noted that the sql statement he is building would have access to any
> variables that are in scope within the do_something procedure - so
I think this is not true: when using EXECUTE IMMEDIATE you are basically calling a global procedure and there is nothing known about your current scope. For example, you cannot access local variables in the SQL statement. I have set up a test script which covers quite a few cases and shows these effects:
http://ora-0815.blogspot.com/2009/02/scoping-of-functions-in-plsql-packages.html
> (rhetorical question) why does the sql not have access to the function
> that appears to be within scope?
>
> From a logical point of view any statement or expression at that point in
> the code should have access to the function. If the function was used at
> that point in the code in any other type of statement then it would be in
> scope and visible. If he was using a local variable instead of a function
> then the variable would be in scope and visible for any type of statement
> even an SQL statement.
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.
> 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.
It could also well be an intended restriction in order to not leak private functionality of the package. I could not list all the implications from the top off my head but I'd say the functions and variables are sufficiently different enough to warrant different treatment.
Kind regards
robert Received on Mon Feb 23 2009 - 11:02:35 CST