Name that Table’s Column

9 11 2012

November 9, 2012

I have not had a lot of time to browse through forums lately, but I noticed an interesting thread in the comp.databases.oracle.server Usenet group.  The OP in the thread was curious why quotes (double quotes) were required around a particular column when referenced in a SQL statement, because specifying quotes around the column name is apparently a little challenging in the PHP scripting language.

I thought about this issue a bit, wondering “how did that happen” and then thought about the benefits of this approach.  In theory, a sophisticated programmer could build a 16 column table using a single four character column name (with different letters capitalized).  Such an approach is sure to evoke a couple of choice four letter words!

I thought that I would throw together a quick example table:

CREATE TABLE T1(
  My_Data NUMBER,
  "My_Data" NUMBER,
  "my_data" NUMBER,
  "MY_DATA" NUMBER);

That almost worked:

SQL> CREATE TABLE T1(
  2    My_Data NUMBER,
  3    "My_Data" NUMBER,
  4    "my_data" NUMBER,
  5    "MY_DATA" NUMBER);
  "MY_DATA" NUMBER)
  *
ERROR at line 5:
ORA-00957: duplicate column name

Columns 1 and 4 have the same name.  Let’s fix that problem and try again:

CREATE TABLE T1(
  My_Data NUMBER,
  "My_Data" NUMBER,
  "my_data" NUMBER,
  "MY_DATa" NUMBER);

Table created.

That worked, now we have a table with four columns, where all of the column names are the same.  Notice that the first column name was not wrapped in quotes.

Let’s insert a row into the table:

INSERT INTO T1 VALUES(
  1,
  2,
  3,
  4);

1 row created.

Let’s see what happens when we query the table:

SELECT
  *
FROM
  T1
WHERE
  MY_DATA=1;

   MY_DATA    My_Data    my_data    MY_DATa
---------- ---------- ---------- ----------
         1          2          3          4

Notice that a row was returned, even though the upper/lower case of the column in the WHERE clause did not match the first column name when the T1 table was created (when the table was created, for the first column, the column name was created as if were specified in all uppercase letters).

Let’s see if a row is returned if we try the following (notice that the attempt at querying with the fourth column based on matching that column’s capitalization in the WHERE clause failed to restrict the query results based on the contents of the fourth column in the table):

SELECT
  *
FROM
  T1
WHERE
  MY_DATa=1;

   MY_DATA    My_Data    my_data    MY_DATa
---------- ---------- ---------- ----------
         1          2          3          4

Let’s try again, this time wrapping the column name found in the WHERE clause in quotes:

SELECT
  *
FROM
  T1
WHERE
  "MY_DATA"=1;

   MY_DATA    My_Data    my_data    MY_DATa
---------- ---------- ---------- ----------
         1          2          3          4

A row was still returned (as expected).  So then, how do we determine which rows in the table have a value of 1 in the fourth column?  We need to place the column name found in the WHERE clause within quotes as follows:

SELECT
  *
FROM
  T1
WHERE
  "MY_DATa"=1;

no rows selected

Notice that this time no rows were returned (as intended, and expected).

I guess that the lesson here is to be careful when creating tables in Oracle Database.  Just because other database products may place column names within quotes as standard practice, that does not indicate that you, as the developer, should continue the practice when working with Oracle Databases.  Now get out there and create some 16 column tables with choice four letter words.  :-)


Actions

Information

4 responses

21 11 2012
Yes, these really are different columns » SQLfail

[...] to a post from Charles Hooper and the ever brilliant Life of Brian for providing inspiration for [...]

26 11 2012
stebradshaw

It gets even messier…its possible to create tables with the same name as reserved words:

SQL> select * from user ;
select * from user
*
ERROR at line 1:
ORA-00903: invalid table name

SQL> create table “USER” (n number) ;

Table created.

SQL> insert into “USER” values (1) ;

1 row created.

SQL> select * from user
2 /
select * from user
*
ERROR at line 1:
ORA-00903: invalid table name

SQL> select * from USER ;
select * from USER
*
ERROR at line 1:
ORA-00903: invalid table name

SQL> select * from “USER” ;

N
———-
1

28 11 2012
Charles Hooper

Yes, that is a bit of a potential headache in the making.

Along the same lines:

CREATE TABLE T1(
  "USER" VARCHAR2(30),
  C2 VARCHAR2(30) DEFAULT USER);
. 
SELECT
  USER
FROM
  T1;
. 
no rows selected
. 
INSERT INTO T1("USER") VALUES ('HOOPER');
. 
SELECT
  USER
FROM
  T1;
. 
USER
------------------------------
TESTUSER
. 
SELECT
  USER,
  "USER"
FROM
  T1;
. 
USER                           USER
------------------------------ ------------------------------
TESTUSER                       HOOPER

Now, let’s create our own USER function to see if we can make things worse:

CREATE OR REPLACE FUNCTION "USER" RETURN VARCHAR2 IS 
  BEGIN
    RETURN 'CHARLES';
  END "USER";
/
.  
Function created.
. 
COLUMN USER FORMAT A15
. 
SELECT
  USER,
  "USER"
FROM
  T1;
. 
USER            USER
--------------- ---------------
TESTUSER        HOOPER
. 
SELECT
  TESTUSER."USER",
  "USER",
  USER
FROM
  T1;
. 
USER            USER            USER
--------------- --------------- ---------------
CHARLES         HOOPER          TESTUSER

Now introduce a public synonym:

CREATE PUBLIC SYNONYM "USER" FOR TESTUSER."USER";
. 
Synonym created.
. 
SELECT
  USER,
  "USER"
FROM
  T1;
. 
USER            USER
--------------- ---------------
TESTUSER        HOOPER
. 
SELECT
  USER,
  "USER"
FROM
  DUAL;
. 
USER            USER
--------------- ---------------
TESTUSER        CHARLES
. 
DROP PUBLIC SYNONYM "USER";
. 
Synonym dropped.
. 
SELECT
  USER,
  "USER"
FROM
  DUAL;
. 
USER            USER
--------------- ---------------
TESTUSER        CHARLES

Now to clean up the last of the mess:

DROP FUNCTION "USER";
. 
Function dropped.
. 
SELECT
  USER,
  "USER"
FROM
  DUAL;
. 
  *
ERROR at line 3:
ORA-00904: "USER": invalid identifier
. 
DROP TABLE T1 PURGE;
. 
Table dropped.

Precedence (and context)… the same excuse why (1 + 1 * 2) is equal to 3 and not 4.

(Edit:Workpress for some reason is now removing lines with a single space from pre formatted comment sections, so I added a . character at the start of those lines for additional readability).

28 11 2012
stebradshaw

I hadn’t even considered a reserved word as a column name, thats not good!

I found about USER table in an application originally developed against MySQL that has since had Oracle support added.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Follow

Get every new post delivered to your Inbox.

Join 137 other followers

%d bloggers like this: