User-defined function question 
Author Message
 User-defined function question

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"



Tue, 23 Mar 2004 15:30:39 GMT
 User-defined function question


Quote:
> 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"

This is not true. So without the code of your function no conclusive answer
can be provided.

Hth,

Sybrand Bakker, Senior Oracle DBA



Wed, 24 Mar 2004 01:24:16 GMT
 User-defined function question

Quote:

>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



Wed, 24 Mar 2004 07:11:50 GMT
 User-defined function question
That's what it is - thanks.

--
Noah Arc
Remove ".spam.begone"

Quote:

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:


>   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



Sun, 28 Mar 2004 22:00:07 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. A user defined function question

2. user defined function question

3. User Defined Function Question

4. User defined function question

5. call a user defined function from another user-defined function

6. Question on user defined function (SQL Server 2000)

7. Question about user-defined functions

8. Newbie questions on User-defined functions in SQL server

9. Question on user defined function (SQL Server 2000)

10. user-defined function client server question

11. questions on user-defined function

12. more question about adding values from user defined function


 
Powered by phpBB® Forum Software