> I'm trying to use one of these functions in a cursor within
> one of the package's procedures, but keep getting error
> messages when I try & create the package:

> "PLS-00320: the declaration of the type of this expression is
> incomplete or malformed"

> and

> "PLS-00231: function <func name> may not be used in SQL"

> The cursor statement in the procedure is thus:

> CURSOR ccursor_name
> (parameter IN VARCHAR2)
> IS
>   SELECT  'x'
>   FROM    table
>   WHERE   function(column) = function(parameter);

> Thanks for any assistance. My guess is that you can't use
> user-defined functions in cursor declarations, but I'd be
> grateful of any clarification.
> --
> Noah Arc
> Remove ".spam.begone"

>I have a package, in which I've defined some functions.

>I'm trying to use one of these functions in a cursor within
>one of the package's procedures, but keep getting error
>messages when I try & create the package:

>"PLS-00320: the declaration of the type of this expression is
>incomplete or malformed"

>and

>"PLS-00231: function <func name> may not be used in SQL"

>The cursor statement in the procedure is thus:

>CURSOR ccursor_name
>(parameter IN VARCHAR2)
>IS
>  SELECT  'x'
>  FROM    table
>  WHERE   function(column) = function(parameter);

>Thanks for any assistance. My guess is that you can't use
>user-defined functions in cursor declarations, but I'd be
>grateful of any clarification.
>--
>Noah Arc
>Remove ".spam.begone"

> >I have a package, in which I've defined some functions.

> >I'm trying to use one of these functions in a cursor within
> >one of the package's procedures, but keep getting error
> >messages when I try & create the package:

> >"PLS-00320: the declaration of the type of this expression is
> >incomplete or malformed"

> >and

> >"PLS-00231: function <func name> may not be used in SQL"

> >The cursor statement in the procedure is thus:

> >CURSOR ccursor_name
> >(parameter IN VARCHAR2)
> >IS
> >  SELECT  'x'
> >  FROM    table
> >  WHERE   function(column) = function(parameter);

> >Thanks for any assistance. My guess is that you can't use
> >user-defined functions in cursor declarations, but I'd be
> >grateful of any clarification.
> >--
> >Noah Arc
> >Remove ".spam.begone"

> I'll have to guess since you didn't provide code to reproduce with (makes
it
> much much harder on us out here....)

> You are calling a private function in the package, one that is not
globally
> known.  The SQL is executed like a subroutine would be -- it cannot see
that
> private function.  Consider:

>   2  as
>   3          procedure bar;
>   4  end;
>   5  /

> Package created.

>   2  as
>   3
>   4  function foo( p_data in varchar2 ) return number
>   5  is
>   6  begin
>   7          return 0;
>   8  end;
>   9
>  10  procedure bar
>  11  is
>  12          cursor c1( c_data in varchar2 )
>  13          is
>  14          select 'x'
>  15            from dual
>  16           where foo( dummy ) = foo( c_data ) ;
>  17  begin
>  18          null;
>  19  end;
>  20
>  21  end;
>  22  /

> Warning: Package Body created with compilation errors.

> Errors for PACKAGE BODY DEMO_PKG:

> LINE/COL ERROR
> -------- -----------------------------------------------------------------
> 14/2     PL/SQL: SQL Statement ignored
> 16/9     PLS-00231: function 'FOO' may not be used in SQL

> there is your error, now lets just recompile the spec and expose foo:

>   2  as
>   3          function foo( p_data in varchar2 ) return number;
>   4
>   5          procedure bar;
>   6  end;
>   7  /

> Package created.

>   2  /

> Package body altered.

> No errors.

> wah-lah, the cursor is OK now with no other change.

> --

> Expert one on one Oracle, programming techniques and solutions for Oracle.
> http://www.amazon.com/exec/obidos/ASIN/1861004826/
> Opinions are mine and do not necessarily reflect those of Oracle Corp