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: Jeremy <jeremy0505_at_gmail.com>
Date: Tue, 2 Jan 2007 14:33:29 -0000
Message-ID: <MPG.20047d98787f848d98a3b6@news.individual.net>


In article <1167747723.710864.320780_at_k21g2000cwa.googlegroups.com>, Mark D Powell says...
>
>
> On Jan 2, 8:51 am, Jeremy <jeremy0..._at_gmail.com> wrote:
> >
> > 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?
>

Hi Mark,

Yes the same owner owns the UTILS package as well as this one. Note that the function call compiles AND executes fine if the 1st paramater to the function is defined as a NUMBER but only causes compile problem when defined as UTILS.NUMARRAY;

The function call is defined in the package specifcation as well.

-- 
jeremy
Received on Tue Jan 02 2007 - 08:33:29 CST

Original text of this message

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