Re: Embedding a function call in a SELECT statement

Jeremy schrieb:
> ------------- 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 function works well in PL SQL, but it is illegal to use PL SQL Types in SQL ( the table of number index by binary integer is PL SQL datatype). Even if you change the parameter from associative array to nested table or varray ( which could be defined in package with TYPE NUMARRAY IS ...) it will be still illegal for calls in SQL ( again, in pl sql this call should work fine). You can solve it if you create a collection type in the database ( with CREATE OR REPLACE TYPE NUMARRAY AS ... ) - it should be either nested table or varray, then you can use collections as parameter of this datatype in SQL queries.

Best regards

Maxim Received on Tue Jan 02 2007 - 14:04:59 CST