Home » SQL & PL/SQL » SQL & PL/SQL » Oracle Bug - PLSQL variable names (Oracle 10gR2)
Oracle Bug - PLSQL variable names [message #296914] Tue, 29 January 2008 08:10 Go to next message
amcghie
Messages: 35
Registered: March 2005
Location: Sunny Dubai
Member
Can anyone tell me if the following is an Oracle Bug. I don't seem to be able to declare a PLSQL variable where the variable name is the same as a table and column.

For example:
CREATE TABLE week_day
  ( week_day_id  NUMBER      NOT NULL
  , week_day     VARCHAR2(3) NOT NULL );

DECLARE
  week_day week_day.week_day%TYPE;
BEGIN
  NULL;
END;
/

Running this code gives the following error:
ERROR at line 2:
ORA-06550: line 2, column 12:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 2, column 12:
PL/SQL: Item ignored

Normally I would prefix my variables in PLSQL with l_ (or whatever depending on scope), but I discovered this "feature" when declaring a field in a PLSQL record.

Anyone else seen this?

Cheers
Re: Oracle Bug - PLSQL variable names [message #296915 is a reply to message #296914] Tue, 29 January 2008 08:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It seems it is a PL/SQL bug, you can't have the same name for the variable AND the table (column name does not matter).
If you change either one or the other this works.

Next step: open a SR and report the bug (unless this is a known restriction).

Regards
Michel
Re: Oracle Bug - PLSQL variable names [message #296939 is a reply to message #296915] Tue, 29 January 2008 09:49 Go to previous messageGo to next message
amcghie
Messages: 35
Registered: March 2005
Location: Sunny Dubai
Member
Hi Michel,

I looked at the docs and couldn't see any reference to a restriction like this. It seems the way round the issue is to prefix the table name with the schema name in the declaration:

DECLARE
  week_day  dev.week_day.week_day%TYPE;
BEGIN
  NULL;
END;
/
Re: Oracle Bug - PLSQL variable names [message #296947 is a reply to message #296939] Tue, 29 January 2008 10:36 Go to previous message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Not very useful in real world. Sad
A better way is to rename the variable.

Regards
Michel
Previous Topic: function & procedure
Next Topic: Materilized View Refresh
Goto Forum:
  


Current Time: Tue Dec 06 14:10:43 CST 2016

Total time taken to generate the page: 0.13437 seconds