Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Embedding a function call in a SELECT statement

Re: Embedding a function call in a SELECT statement

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 2 Jan 2007 06:22:03 -0800
Message-ID: <1167747723.710864.320780@k21g2000cwa.googlegroups.com>

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US