Re: %type in fucntion retrun

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 15 Dec 1999 14:44:24 -0500
Message-ID: <0qrf5so38pbkaed1tue3sesjas94jodhmi@4ax.com>

A copy of this was sent to Marc Weinsock <MWeinstock_at_lds.com> (if that email address didn't require changing) On Wed, 15 Dec 1999 19:14:11 GMT, you wrote:

>Does anyone know why I am getting this error. I am trying to return
>%type in a function. The %type works in the parameter section but not
>in the return section.
>
>The error:
>PLS-00487: Invalid reference to variable 'RATE_TYPE'
>
>Broken:
>function validate_rate_type(rate_type rate_type.rate_type%type) RETURN
>rate_type.rate_type%type IS
>BEGIN
> null;
>END validate_rate_type;
>
>This works:
>function validate_rate_type(rate_type rate_type.rate_type%type) RETURN
>varchar2 IS
>BEGIN
> null;
>END validate_rate_type;

Too many rate_type's, thats why. After the list of input variables (after the return) there are 2 rate_types

the variable rate_type in the parameter list
the table rate_type

It is getting confused by having 2 of them to pick from. It isn't really a good practice to name plsql variables after tables in columns -- the rules of scoping will burn you every time. I suggest you rename your plsql parameter as i did in the following example:

tkyte_at_8.0> create table rate_type ( rate_type varchar2(5) );

Table created.

tkyte_at_8.0>
tkyte_at_8.0> create or replace function verify_rate_type( rate_type in   2 rate_type.rate_type%type ) return rate_type.rate_type%type   3 as
  4 begin
  5 null;
  6 end;
  7 /

Warning: Function created with compilation errors.

tkyte_at_8.0> show errors
Errors for FUNCTION VERIFY_RATE_TYPE:

LINE/COL ERROR

-------- -----------------------------------------------------------------
0/0      PL/SQL: Compilation unit analysis terminated
2/35     PLS-00487: Invalid reference to variable 'RATE_TYPE'

tkyte_at_8.0>
tkyte_at_8.0> create or replace function verify_rate_type( p_rate_type in   2 rate_type.rate_type%type ) return rate_type.rate_type%type   3 as
  4 begin
  5 null;
  6 end;
  7 /

Function created.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Dec 15 1999 - 13:44:24 CST