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.

Now, I have a line like this:

v_select := 'SELECT col1, col2, GET_PRICE(col3) FROM products';
open p_output for v_select;

That fails. But, if I create the function by itself, as a normal
function, then it works fine. Why is this?

"Mtek" <mtek (AT) mtekusa (DOT) com> wrote

didn't quote the cursor. If the function is public in the package then you
can do

cursor foo is SELECT col1, col2, my_package.GET_PRICE(col3) get_price FROM
products;
....
Jim

On 20.02.2009 22:03, gym dot scuba dot kennedy at gmail wrote:
Here you have provided the explanation yourself.

Mtek, I suggest you read the basic documentation about PL/SQL:

http://download.oracle.com/docs/cd/B...b14261/toc.htm

Regards

robert

Mtek schreef:
within your package, right?
Dynamic SQL is not executed within the context of your package, so for
this statement, GET_PRICE is invisible.
If you did something like this in your package: SELECT GET_PRICE(col3)
.... into ... in your package, it would be within the scope of your
package. Furthermore, get_price must be declared BEFORE the call, or
otherwise have a forward declaration.

Shakespeare

Robert Klemme (shortcutter (AT) googlemail (DOT) com) wrote:
: On 20.02.2009 22:03, gym dot scuba dot kennedy at gmail wrote:
: > "Mtek" <mtek (AT) mtekusa (DOT) com> wrote in message
: > news:c468a5f4-da3f-4027-99db-1f528bbf27d7 (AT) m40g2000yqh (DOT) 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. It isn't obvious why it should not also have
access to other things that are in scope within the package.

Basicaly that is a limitation of the implementation of pl/sql. For the
sql to have access to the function the function must be visible outside of
the package.

Types are a bit like that also. You can create a type that is local to a
package, but then Oracle actually creates a type outside of the package
using a system generated name so that the sql statements in the package
can appear to be accessing the type that is private to the package. But
if you drop the system generated type then you'll quickly earn which type
was actually being accessed (and it isn't the thing in the package).

Oracle doesn't do anything like that for functions though, you have to do
it yourself by making them visible and then using the full name.

On 21.02.2009 18:03, Malcolm Dew-Jones wrote:
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.

Cheers

robert

Robert Klemme (shortcutter (AT) googlemail (DOT) com) wrote:
: On 21.02.2009 18:03, Malcolm Dew-Jones wrote:
: > Robert Klemme (shortcutter (AT) googlemail (DOT) com) wrote:
: > : On 20.02.2009 22:03, gym dot scuba dot kennedy at gmail wrote:
: > : > "Mtek" <mtek (AT) mtekusa (DOT) com> wrote in message
: > : > news:c468a5f4-da3f-4027-99db-1f528bbf27d7 (AT) m40g2000yqh (DOT) 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. 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

"Malcolm Dew-Jones" <yf110 (AT) vtn1 (DOT) victoria.tc.ca> wrote

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

gym dot scuba dot kennedy at gmail (kennedyii (AT) verizon (DOT) net) wrote:

: 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

Yes, that is certainly the underlying reason, though unless the package is
part of something like an fmb file, then the pl/sql engine is itself
running on the same server as the sql.

On 22 Feb., 04:56, yf... (AT) vtn1 (DOT) victoria.tc.ca (Malcolm Dew-Jones)
wrote:
Might well be.

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...packages..html

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.

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