Is oracle case sensitive in ddl statements

Question

Following fails to execute

create table product_instance (
  "ID" number(19,0),
  constraint pro_instance_pk primary key ("id")
)

giving the output

SQL Error: ORA-00904: "id": invalid identifier
00904. 00000 -  "%s: invalid identifier"

However the changing "id" to "ID" works fine

create table product_instance (
  "ID" number(19,0),
  constraint pro_instance_pk primary key ("ID")
)

Following also works fine

create table product_instance (
  ID number(19,0),
  constraint pro_instance_pk primary key (id)
)

Appreciate if someone could answer the effect of specifying "ID" instead of just id in the DDL statements. Is oracle case sensitive or insensitive

score 1 · Answer 1 · edited May 23 '17 at 11:50

1

When you use quotes, it makes the column name case sensitive. See this question.

edited May 23 '17 at 11:50

Community

answered Aug 02 '13 at 09:35

jle

This behaviour is especially nasty when people use reserved words as names for columns or tables... Column-name "exclusive" will not match "EXCLUSIVE", e.g. ... :P – AKDADEVIL Aug 02 '13 at 13:11

score -1 · Answer 2 · answered Aug 02 '13 at 09:31

-1

Oracle is insensitive in DDL statements. for creating a table we cannot use field name in the ("") double quotes.

 sql>create table product_instance (
     "ID" number(19,0)***,***
      constraint pro_instance_pk primary key ("ID")
      )

This code doesn't execute on the sql promt. and to apply constraints on the table there should not be semicolon in between datatype and constraint.

answered Aug 02 '13 at 09:31

Mohsin Shaikh

1

Using double-quotes in DDL *makes* Oracle case sensitive. The posted script does run, because that's a comma not a semi-colon; the comma is the correct syntax when defining a table level cronstraint. – APC Aug 02 '13 at 09:36

2 Answers2