Re: Embedding a function call in a SELECT statement

On Jan 2, 8:51 am, Jeremy <jeremy0..._at_gmail.com> wrote:
> ------------- Oracle 9i R2 ----------------------
>
> Hello experts, the following is valid in a procedure within a package
> where the myfunc function is also defined:
>
> select myfunc(1,ID) from my_id_tab;
>
> The function myfunc is defined as
>
> function myfunc
> (p_id_array in number,
> p_id in number)
> return varchar2;
>
> NOW the problem.
>
> Suppose myfunc is defined like this:
>
> function myfunc
> (p_id_array in utils.numarray,
> p_id in number)
> return varchar2;
>
> And the datatype numarray is defined in a UTILS package header as
> type numarray is table of number index by binary_integer;
>
> Then within my procedure, I have a local variable defined:
>
> l_id_array ic_util.numarray;
>
> Then I change the original select statement to be
>
> select myfunc(l_id_array,ID) from my_id_tab;
>
> Now if I try to compile my procedure, I get error:
> 630/7 PL/SQL: SQL Statement ignored
> 782/12 PLS-00306: wrong number or types of arguments in call to
> 'MYFUNC'
>
> 782/12 PL/SQL: ORA-00904: "MYPACKAGE"."MYFUNC": invalid identifier
> 782/24 PLS-00382: expression is of wrong type
>
> I think all the data types match correctly, is there a reaon why I
> should be able to embed the function in the select clause only if the
> input parameters are NOT of a user-defined type?
>
> A simple call to the function outside of the select clause works fine:
>
> l_str := id_in_array(l_id_array,1);
>
> --
> jeremy
>
> ============================================================
> ENVIRONMENT:
> Oracle 9iR2 / Oracle HTTP Server / mod_plsql / Solaris 8
> ============================================================

jeremy, have both of the following requirements been complied with?

>>

User-defined functions must be created as top-level functions or declared with a package specification before they can be named within a SQL statement.

To use a user function in a SQL expression, you must own or have EXECUTE privilege on the user function
<< (from 9.2 SQL manual material on user-defined functions)

Does the owner of the function have a direct grant (execute) on the "utils" package or own it?

HTH -- Mark D Powell -- Received on Tue Jan 02 2007 - 08:22:03 CST