DATE or NUMBER as a column name

One could expect that if it is possible to name a table’s column “DATE” then it is safe to do so. It appears, however, that using such a column’s name would cause the following compilation error in any procedure using an input parameter of the row type based on this table: ORA-06552: PL/SQL: Compilation unit analysis terminated ORA-06553: PLS-320: the declaration of the type of this expression is incomplete or malformed Surprisingly, view on this table works correctly and you can insert data through it. It seems that only PL/SQL has this error. The same errors occurs when the column is named NUMBER. However there is no problem with names: VARCHAR, VARCHAR2, INTEGER, FLOAT, TIMESTAMP, CLOB, LOB, CHAR, LONG Here is the test for DATE name

create table test_date ( "DATE" date );
create table test_date2 ( "DATE2" date );

-- this procedure would not compile
create procedure pr_test_date(p_date test_date%rowtype) as
begin
 null;
end;

create procedure pr_test_date2(p_date2 test_date2%rowtype) as
begin
 null;
end;

create view v_test_date as select * from test_date;
create view v_test_date2 as select * from test_date2;

insert into v_test_date("DATE") values (sysdate);
You May Also Like

Devoxx 2012 review


I'm sitting in a train to Charleroi, looking through a window at the Denmark landscape, street lights flashing by, people comming home from work, getting out for a Friday night party, or having a family dinner. To my left, guys from SoftwareMill are playing cards.
I don't really see them. My mind is busy elsewhere, sorting out and processing last two days in Antwerp, where 3400 developers, from 41 different countries, listened to 200 different sessions at the Devoxx, AFAIK the biggest Java conference this year.