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

Home -> Community -> Usenet -> c.d.o.misc -> Re: User-defined function question

Re: User-defined function question

From: Noah Arc <bowed_zombie_drone.spam.begone_at_zombie.co.uk>
Date: Wed, 10 Oct 2001 15:00:07 +0100
Message-ID: <IvYw7.38995$uM2.6141422@monolith.news.easynet.net>


That's what it is - thanks.

--
Noah Arc
Remove ".spam.begone"
Thomas Kyte <tkyte_at_us.oracle.com> wrote in message
news:9plenm0qj6_at_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 Wed Oct 10 2001 - 09:00:07 CDT

Original text of this message

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