Re: Functions
Date: Sun, 22 Feb 2009 07:48:42 GMT
Message-ID: <ur7ol.886$tw4.552_at_nwrddc01.gnilink.net>
"Malcolm Dew-Jones" <yf110_at_vtn1.victoria.tc.ca> wrote in message
news:49a0db0b$1_at_news.victoria.tc.ca...
> Robert Klemme (shortcutter_at_googlemail.com) wrote:
> : On 21.02.2009 18:03, Malcolm Dew-Jones wrote:
> : > Robert Klemme (shortcutter_at_googlemail.com) wrote:
> : > : On 20.02.2009 22:03, gym dot scuba dot kennedy at gmail wrote:
> : > : > "Mtek" <mtek_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.
>> : or something else. In fact, I would rather consider it a bug if
> : 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
> : 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. Something like
>
> package XXX
>
> function GET_PRICE( p number ) is
> begin
> return p*10;
> end
>
> procedure do_something is
> v_select varchar2(4000);
> begin
> v_select := 'SELECT col1, col2, GET_PRICE(col3) FROM products';
> open p_output for v_select;
> ...etc...
> end;
>
> end package XXX;
>
> 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
> (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.
>
> 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.
>
> $0.10
I think the piece that he is missing is that sql statements are executed by the server not by the pl/sql engine. So the function is private to the pl/sql engine and the sql processor cannot see it. So it is a subtle difference that does not occur in most programming languages. Jim Received on Sun Feb 22 2009 - 01:48:42 CST