| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: PL/SQL BUG?
A copy of this was sent to "Keith Jamieson" <jamiesonk_at_phoenix.ie>
(if that email address didn't require changing)
On Tue, 26 Oct 1999 14:17:33 +0100, you wrote:
>Can anyone explain the following.
>
>The following (declared in a package specification), fails to compile
>
>TYPE phenomenontype_rec IS RECORD
>(phenomenontype phenomenontype.phenomenontype%TYPE,
> memberid indexingphenomenontype.memberid%TYPE,
> name phenomenontype.name%TYPE,
> valuetype phenomenontype.valuetype%TYPE,
> input indexingphenomenontype.input%TYPE,
> purpose phenomenontype.purpose%TYPE,
> sequence indexingphenomenontype.sequence%TYPE );
>
bad idea to have the tablename the same as the column name the same as a variable name. The scoping/name resolution is getting way mixed up trying to figure out what pheonemnontype.phenomenontype is.
Here is an example showing why this is true:
tkyte_at_8.0> create table x ( x int);
Table created.
tkyte_at_8.0>
tkyte_at_8.0> declare
2 type x_rec is record ( y int, x y%type );
3 begin
4 null;
5 end;
6 /
PL/SQL procedure successfully completed.
tkyte_at_8.0>
tkyte_at_8.0> declare
2 type x_rec is record ( x x.x%type );
3 begin
4 null;
5 end;
6 /
type x_rec is record ( x x.x%type );
*
The first record shows that %TYPE can use either a PLSQL variable (x is of type y%type) or a database table column. The second record shows that plsql prefers to use the plsql variable over the table -- it is getting confused with the X in the X.x -- it is thinking "X the variable", not "X the table". It can get more confusing with variables outside of the record having the same name as the tables:
tkyte_at_8.0> declare
2 x int; 3 type x_rec is record ( y x.x%type );4 begin
type x_rec is record ( y x.x%type );
*
ORA-06550: line 3, column 27: PLS-00487: Invalid reference to variable 'X' ORA-06550: line 3, column 2:
Since we have a variable outside of the record named X, this again gets picked up first and throws an error.
Many people use naming conventions. For example, I like to start all local procedure names with "l_", parameters with "p_", global variables in packages with "g_" and record variables with "r_". EG:
create or replace procedure foo( p_param1 in int ) as
type my_record_rec is record (
( r_field1 int,
....
l_a_record my_record_rec;
begin
...
end;
You can run into big problems with scoping if you use plsql variable names that are the same as column names. for example, i see this alot:
create procedure update_employee( ENAME in varchar2, SAL in number )
as
begin
update emp set sal = SAL where ename = ENAME;
end;
that has the unintended effect of updating every row in the table setting the sal = itself. The way I would write that would be
create procedure update_employee( p_ENAME in varchar2, p_SAL in number )
as
begin
update emp set sal = p_SAL where ename = p_ENAME;
end;
>however. the following declared instead of the above compiles successfully.
>
>TYPE phenomenontype_rec IS RECORD
>(phenomenontype INTEGER,
> memberid indexingphenomenontype.memberid%TYPE,
> name phenomenontype.name%TYPE,
> valuetype phenomenontype.valuetype%TYPE,
> input indexingphenomenontype.input%TYPE,
> purpose phenomenontype.purpose%TYPE,
> sequence indexingphenomenontype.sequence%TYPE );
>
>This appears to me to be a bug in PL/SQL(Oracle 7.3.4) . Can someone confirm this, or otherwise give me a good explanation of why this is not a bug.
>
--
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 Tue Oct 26 1999 - 08:39:18 CDT
 |
 |