Oracle PL/SQL: Case Sensitivity in Object Names

Oracle has introduced some functionality that allows you to use non-upper-case identifiers for your objects (and also to use some previously reserved characters). By default however oracles tool set presumes everything is still in upper-case so if you have make use of the new functionality then there is a new set of problems you need to be aware of.


First of all a very quick example to show you what the problem is. Log onto an Oracle database and execute the following SQL;

create table OBJECTTEST
(
  "columna" number,
  "column b" number,
  columnc number
)
;

This creates a simple table called OBJECTTEST which has three columns the first two are overriding Oracles default behaviour and using non-upper-case (columna) and invalid (i.e. including a space, column b) names for the columns.

To test this works try;

SQL> select * from objecttest;

   columna   column b    COLUMNC
---------- ---------- ----------

No values in the table, everything returns correctly.

Now try this;

SQL> select columna from objecttest;

select columna from objecttest

ORA-00904: "COLUMNA": invalid identifier

Exactly what should happen but it's not obvious why. Oracle is treating columna as a column identifier and converting it to upper-case for you before it does the check to see if it exists.

You need to use quotes around the column name to tell Oracle not to do the conversion;

SQL> select "columna" from objecttest;

   columna
----------

Putting quotes round the item we are after basically tells Oracle to treat the information within the quotes as a literal string - don't do any conversion. Therefore the following works;

select "columna", "column b", columnc from objecttest;

   columna   column b    COLUMNC
---------- ---------- ----------

So why would you use this? Isn't it just making your job harder? Well I have to admit it's quite difficult to see a general use for it but I have found one place where it's incredibly useful; working with Microsoft SQL Server Reporting Services.

SSRS attempts to convert the database name into something "readable" to end-users. It does this by replacing all underscores with spaces (it isn't exactly sophisticated!) in the query result sets you're working with so, for example, the query column "ITEM_NUMBER" when added to a report using the wizard has the display name "ITEM NUMBER". Almost useful.

Now if you rename the field (using quotes) to "Item Number" in the query then SSRS will do a straight convert across and you can just use the wizard to build reports rather than using hte wizard and then having to go in an correct the case on everything.

Here is a small example (from Oracle e-Business Suite - getting the alternate item number for an item);

SELECT
  IIMB.ITEM_NO "Item Number",
  IIMB.ITEM_DESC1 "Item Description",
  IIMB.ALT_ITEMA "Alternate Item Number"
FROM GMI.IC_ITEM_MST_B IIMB
WHERE IIMB.ITEM_NO = :Item_Number