CODEWITHSUNDEEP
sqloracle-databasequoted-identifier
Bear
Bear

Reputation: 3

Create view in ORACLE

Is there any different between below two statements Statement one:

CREATE OR REPLACE VIEW ABC AS
SELECT "ORDER_NO","OBJKEY"
FROM TEST_TABLE;

Statement two:

CREATE OR REPLACE VIEW ABC AS
SELECT ORDER_NO,OBJKEY
FROM TEST_TABLE;

Second statement doesn't have double quotes. I would like to know what is the difference, Oracle does not complain it does compile both codes.

Upvotes: 0

Views: 228

Answers (1)

Alex Poole
Alex Poole

Reputation: 191275

They are exactly the same in your example. From the documentation:

Every database object has a name. In a SQL statement, you represent the name of an object with a quoted identifier or a nonquoted identifier.

  • A quoted identifier begins and ends with double quotation marks ("). If you name a schema object using a quoted identifier, then you must use the double quotation marks whenever you refer to that object.

  • A nonquoted identifier is not surrounded by any punctuation.

You can use either quoted or nonquoted identifiers to name any database object. ...

and

Nonquoted identifiers are not case sensitive. Oracle interprets them as uppercase. Quoted identifiers are case sensitive.

So, ORDER_NO is nonquoted, and not case-sensitive, and Oracle treats the name as uppercase - so effectively, when it looks in the data dictionary for the matching column name (in the all_tab_columns view), it looks for the exact string value 'ORDER_NO'. That would also be the case if you had nonquoted order_no or Order_No or any other mix of case; because it's nonquoted Oracle still treats it as uppercase, and looks internally for 'ORDER_NO'.

"ORDER_NO" is quoted, so that is case-sensitive, but as it's uppercase anyway it makes no difference. Oracle is still looking for a column in the data dictionary called 'ORDER_NO'.

If the actual object identifier (e.g. column name) is in uppercase in the data dictionary then it doesn't matter if you supply it as a nonquoted identifier in any case, or as a quoted identifier in uppercase.

What you can't do is use quotes and a different case. "ORDER_NO" is fine; "order_no" or "Order_No" or any other quoted mixed case will not match what is in the data dictionary.

While you can create objects with quoted identifiers that are not in uppercase (or which include or start with otherwise illegal characters, as shown in the rules listed in that documentation), it's usually considered a bad idea as you then always have to use quotes and exactly the same case. And as the documentation also notes:

Oracle does not recommend using quoted identifiers for database object names. These quoted identifiers are accepted by SQL*Plus, but they may not be valid when using other tools that manage database objects.

Upvotes: 3

Related Questions