Re: Embedding a function call in a SELECT statement

In article <1167752016.528594.302670_at_48g2000cwx.googlegroups.com>, Vince says...
>
> 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,
>
> Your declarations appear to be different:
>
> p_id_array in utils.numarray
>
> is not the same as
>
> l_id_array ic_util.numarray
>
>

You are right. That was just a typo though in my post I'm afraid! They *are* defined with the same type.

As mentioned originally, I can make a call to the function OUTSIDE of a SELECT statement without any compile error.

cheers

-- 
jeremy