Is this a bug?

Anurag Varma schrieb:

> The bug (or inconsistency as you might want to call it) is that oracle
> should
> not allow a column name and datatype to be of the same name IF the
> column
> name is not enclosed in double quotes.
>
> So create table test (timestamp timestamp); should throw an error
> since all other datatypes do throw an error.
>

You are far too optimistic ;-)

scott-at-ORA102> create table t(id number,bfile bfile);

Table created.

scott-at-ORA102> drop table t;

Table dropped.

scott-at-ORA102> create table t(id number,clob clob);

Table created.

scott-at-ORA102> drop table t;

Table dropped.

scott-at-ORA102> create table t(id number,blob blob);

Table created.

scott-at-ORA102> drop table t;

Table dropped.

scott-at-ORA102> create table t(id number,nchar nchar);

Table created.

scott-at-ORA102> drop table t;

Table dropped.


In all the cases behaviour with plsql units referencing the table is
identical to those with timestamp...

Best regards

Maxim

.... or go even further and run:

SELECT DISTINCT owner, column_name, data_type
FROM dba_tab_columns
WHERE column_name IN (SELECT keyword
FROM v$reserved_words) AND owner LIKE
'SYS%';

.... or go even further and run:

SELECT DISTINCT owner, column_name, data_type
FROM dba_tab_columns
WHERE column_name IN (SELECT keyword
FROM v$reserved_words) AND owner LIKE
'SYS%';

Maxim Demenko wrote:
> Anurag Varma schrieb:
>
> > The bug (or inconsistency as you might want to call it) is that oracle
> > should
> > not allow a column name and datatype to be of the same name IF the
> > column
> > name is not enclosed in double quotes.
> >
> > So create table test (timestamp timestamp); should throw an error
> > since all other datatypes do throw an error.
> >
>
> You are far too optimistic ;-)
>
> scott-at-ORA102> create table t(id number,bfile bfile);
>
> Table created.
>
> scott-at-ORA102> drop table t;
>
> Table dropped.
>
> scott-at-ORA102> create table t(id number,clob clob);
>
> Table created.
>
> scott-at-ORA102> drop table t;
>
> Table dropped.
>
> scott-at-ORA102> create table t(id number,blob blob);
>
> Table created.
>
> scott-at-ORA102> drop table t;
>
> Table dropped.
>
> scott-at-ORA102> create table t(id number,nchar nchar);
>
> Table created.
>
> scott-at-ORA102> drop table t;
>
> Table dropped.
>
>
> In all the cases behaviour with plsql units referencing the table is
> identical to those with timestamp...
>
> Best regards
>
> Maxim


Thanks for the correction.



Anurag

Maxim Demenko wrote:
> Anurag Varma schrieb:
>
> > The bug (or inconsistency as you might want to call it) is that oracle
> > should
> > not allow a column name and datatype to be of the same name IF the
> > column
> > name is not enclosed in double quotes.
> >
> > So create table test (timestamp timestamp); should throw an error
> > since all other datatypes do throw an error.
> >
>
> You are far too optimistic ;-)
>
> scott-at-ORA102> create table t(id number,bfile bfile);
>
> Table created.
>
> scott-at-ORA102> drop table t;
>
> Table dropped.
>
> scott-at-ORA102> create table t(id number,clob clob);
>
> Table created.
>
> scott-at-ORA102> drop table t;
>
> Table dropped.
>
> scott-at-ORA102> create table t(id number,blob blob);
>
> Table created.
>
> scott-at-ORA102> drop table t;
>
> Table dropped.
>
> scott-at-ORA102> create table t(id number,nchar nchar);
>
> Table created.
>
> scott-at-ORA102> drop table t;
>
> Table dropped.
>
>
> In all the cases behaviour with plsql units referencing the table is
> identical to those with timestamp...
>
> Best regards
>
> Maxim


Thanks for the correction.



Anurag

Valentin Minzatu wrote:
> ... or go even further and run:
>
> SELECT DISTINCT owner, column_name, data_type
> FROM dba_tab_columns
> WHERE column_name IN (SELECT keyword
> FROM v$reserved_words) AND owner LIKE
> 'SYS%';
>

To me, this explains why you can create columns with certain reserved
words. If Oracle precluded you from doing so, then the Data Dictionary
would break.

Cheers,
Brian


--
================================================== =================

Brian Peasland
dba-at-nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

Valentin Minzatu wrote:
> ... or go even further and run:
>
> SELECT DISTINCT owner, column_name, data_type
> FROM dba_tab_columns
> WHERE column_name IN (SELECT keyword
> FROM v$reserved_words) AND owner LIKE
> 'SYS%';
>

To me, this explains why you can create columns with certain reserved
words. If Oracle precluded you from doing so, then the Data Dictionary
would break.

Cheers,
Brian


--
================================================== =================

Brian Peasland
dba-at-nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

Serge Rielau wrote:

>> I'm not quite sure I can agree that something violating Oracle's advice,
>> and generating an error, is a bug. At least not as long as the word
>> TIMESTAMP is present in gv$reserved_words.
> I wish my customers were as tolerant as you are....

When a company goes to the effort to put a reference of keywords into
their product then it becomes the obligation of the developers and DBAs
to use it.

When you buy an car ... do you expect the dealer to teach you how to
drive it?
--
Daniel A. Morgan
University of Washington
damorgan-at-x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Serge Rielau wrote:

>> I'm not quite sure I can agree that something violating Oracle's advice,
>> and generating an error, is a bug. At least not as long as the word
>> TIMESTAMP is present in gv$reserved_words.
> I wish my customers were as tolerant as you are....

When a company goes to the effort to put a reference of keywords into
their product then it becomes the obligation of the developers and DBAs
to use it.

When you buy an car ... do you expect the dealer to teach you how to
drive it?
--
Daniel A. Morgan
University of Washington
damorgan-at-x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Anurag Varma wrote:
> DA Morgan wrote:
>> EscVector wrote:
>>> Anurag Varma wrote:
> -snip-
>>>> You are right .. it should be a bug. However, probably not easy
>>>> to fix ...
>>>>
>>>> You might want to read Bug 4703738 where this is discussed.
>>>>
>>>> Anurag
>>> Quote from doc:
>>> This issue is fixed in
>>> * 9.2.0.8 (Server Patch Set)
>>>
>>> Well they didn't kill this bug. It is back again in 10.2.0.2
>> I'm not quite sure I can agree that something violating Oracle's advice,
>> and generating an error, is a bug. At least not as long as the word
>> TIMESTAMP is present in gv$reserved_words.
>> --
>> Daniel A. Morgan
>
> The bug (or inconsistency as you might want to call it) is that oracle
> should
> not allow a column name and datatype to be of the same name IF the
> column
> name is not enclosed in double quotes.
>
> So create table test (timestamp timestamp); should throw an error
> since all other datatypes do throw an error.

I would agree that throwing an error would be a "nice to have" but I
wouldn't call it a bug any more than I would expect Oracle to hold my
hand if I am fool enough to write this code.

CREATE SYNONYM dummy FOR dual;

set serveroutput on

DECLARE
dummy VARCHAR2(1);
BEGIN
SELECT dummy
INTO dummy
FROM dummy;

dbms_output.put_line(dummy);
END;
/
--
Daniel A. Morgan
University of Washington
damorgan-at-x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org