Interesting Bug

Michael posted a comment on Martin’s blog, that I previously quoted, about a “hack” to add a comment on a default value definition. Here is an example of how it works (tested in 11.2.0.1):

  SQL> CREATE TABLE t (
    2     x VARCHAR2 (10) DEFAULT 'Hi' -- There. This will be stored too.
    3  );

  Table created.

Notice the comment after the default value. The comment would be ignored, right? think again:

  SQL> COLUMN column_name FORMAT a14
  SQL> COLUMN data_default FORMAT a40
  SQL> SELECT column_name, data_default
    2    FROM user_tab_columns
    3   WHERE table_name = 'T';

  COLUMN_NAME    DATA_DEFAULT
  -------------- ----------------------------------------
  X              'Hi' -- There. This will be stored too.

  1 row selected.

Well, basically this is bug 8546537:

When trying to add a COMMENT to a table, if the ‘)’ is on a new line, then the COMMENT is successfuly created when an error should be raised. The COMMENT also makes it to the Data Dictionary. If the ‘)’ is on the same line, an error is raised.

Interesting!

Filed in Oracle on 24 Jan 12 | Tags: database

---