Re: Embedding a function call in a SELECT statement

Jeremy 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,

Your declarations appear to be different:

p_id_array in utils.numarray

is not the same as

l_id_array ic_util.numarray Received on Tue Jan 02 2007 - 09:33:36 CST