Re: Functions

From: Malcolm Dew-Jones <>
Date: 21 Feb 2009 20:56:43 -0700
Message-ID: <49a0db0b$>

Robert Klemme ( wrote:
: On 21.02.2009 18:03, Malcolm Dew-Jones wrote:
: > Robert Klemme ( wrote:
: > : On 20.02.2009 22:03, gym dot scuba dot kennedy at gmail wrote:
: > : > "Mtek" <> wrote in message
: > : >
: > : >> 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. Something like

        package XXX

	function  GET_PRICE( p number ) is
		return p*10;

	procedure do_something is
	  v_select varchar2(4000);
	  v_select := 'SELECT col1, col2, GET_PRICE(col3) FROM products';
	  open p_output for v_select;

	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 Received on Sat Feb 21 2009 - 21:56:43 CST

Original text of this message