Re: User-defined function question

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 5 Oct 2001 16:11:50 -0700
Message-ID: <9plenm0qj6@drn.newsguy.com>

In article <Fkdv7.34167$uM2.5065331_at_monolith.news.easynet.net>, "Noah says...
>
>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:

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create or replace package demo_pkg   2 as
  3 procedure bar;
  4 end;
  5 /

Package created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> 
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> 
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create or replace package body demo_pkg

  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.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> show errors package body demo_pkg 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:

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create or replace package demo_pkg   2 as
  3 function foo( p_data in varchar2 ) return number;   4
  5 procedure bar;
  6 end;
  7 /

Package created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> alter package demo_pkg compile body 2 /

Package body altered.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> show errors package body demo_pkg No errors.
ops$tkyte_at_ORA817DEV.US.ORACLE.COM>

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

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
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

Received on Fri Oct 05 2001 - 18:11:50 CDT