What exactly do quotation marks around the table name do?

by

Putting double-quotes around an identifier in Oracle causes Oracle to treat the identifier as case sensitive rather than using the default of case-insensitivity. If you create a table (or a column) with double-quotes around the name, you must always refer to the identifier with double quotes and by correctly specifying the case (with the exception of all upper case identifiers, where double-quotes are meaningless).

Under the covers, Oracle is always doing case-sensitive identifier matching. But it always casts identifiers that are not double-quoted to upper case before doing the matching. If you put double-quotes around an identifier, Oracle skips the casting to upper case.

So if you do something like

CREATE TABLE my_table( 
  col1 number,
  col2 number
)

you can

SELECT * FROM my_table
SELECT * FROM MY_TABLE
SELECT * FROM My_Table
SELECT * FROM "MY_TABLE"

but something like

SELECT * FROM "my_table"

will fail.

On the other hand, if you do something like

CREATE TABLE "my_other_table"( 
  col1 number,
  col2 number
)

you cannot do

SELECT * FROM my_other_table
SELECT * FROM MY_OTHER_TABLE
SELECT * FROM My_Other_Table
SELECT * FROM "MY_OTHER_TABLE"

but this

SELECT * FROM "my_other_table"

will work

More Related Contents:

  1. Current date in Oracle Db
  2. ORA-01843 Not a valid month
  3. Inner join vs Where
  4. Get value based on max of a different column grouped by another column [duplicate]
  5. ORA-00918: column ambiguously defined in SELECT *
  6. Multiplication aggregate operator in SQL
  7. Pivoting rows into columns dynamically in Oracle
  8. SQL ROWNUM how to return rows between a specific range
  9. MySQL equivalent of DECODE function in Oracle
  10. How to get Oracle create table statement in SQL*Plus
  11. How can you tell if a value is not numeric in Oracle?
  12. How do I select all the columns from a table, plus additional columns like ROWNUM?
  13. Disable all table constraints in Oracle
  14. ORA – 00933 confusion with inner join and “as”
  15. OVER clause in Oracle
  16. PARTITION BY with and without KEEP in Oracle
  17. Comparing with date in Oracle sql
  18. Is it possible to query a comma separated column for a specific value?
  19. Different CURRENT_TIMESTAMP and SYSDATE in oracle
  20. SQL selecting rows by most recent date with two unique columns
  21. How to extract group from regular expression in Oracle?
  22. How to use Timestamp_to_scn and Scn_to_timestamp in Oracle?
  23. Using LIKE in an Oracle IN clause
  24. How to handle optional parameters in SQL query?
  25. Return number from Oracle Select statement after parsing date
  26. How do you interpret a query’s explain plan?
  27. How to call Oracle MD5 hash function?
  28. Oracle Convert TIMESTAMP with Timezone to DATE
  29. SQL Server equivalent of WM_CONCAT function [duplicate]
  30. Oracle: ‘= ANY()’ vs. ‘IN ()’

Leave a Comment