Oracle SQL Syntax: Quoted Identifier

Oracle SQL Syntax: Quoted identifier

The table is named bar and not BAR or "bar" but because it is in lowercase you can only reference it using double quotes:

select * from bar; -- will fail

select * from "bar"; -- will succeed

The moral is: never create tables like this!

Oracle DB quote column names

It doesn't have to do with objects or tables, it has to do with how these objects/tables have been created.

If you do
create table "blabla" then you always need to address this table with "blabla", if you do create table blabla then you can address this table via BLABLA or blabla or bLabLa. Using " " makes the name case sensitive and that is the reason why most developers don't use " " because usually you don't want case sensitive names .

How do I use quoted identifier for user + table name combination in Oracle?

If you have created the table using quoted identifier, then you must always use double-quotation marks wherever you refer the object.

From documentation,

Database Object Naming Rules

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.

For example,

SQL> CREATE TABLE "USER"(A NUMBER);

Table created.

SQL>
SQL> SELECT COUNT(*) FROM LALIT.USER;
SELECT COUNT(*) FROM LALIT.USER
                           *
ERROR at line 1:
ORA-00903: invalid table name

SQL>
SQL> SELECT COUNT(*) FROM LALIT."USER";

  COUNT(*)
----------
         0

SQL>

So, you need to refer the table as a quoted identifier:

SELECT COUNT(*) FROM SYS0MYUSER."USER";

Update OP updated his question regarding table alias.

What's about table alias do I have to use double quotes too ?

Table alias has nothing to do with the quoted identifier.

For example,

SQL> SELECT t.* FROM LALIT."USER" t;

no rows selected

SQL>

A CREATE statement with quoted fields in Oracle

OK, I won't say it, I'll just think it loudly.

The documentation clearly says that if you have quoted identifiers, you have to quote them everywhere (my italics for emphasis):

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.

So you always have to do:

SELECT "id", "description" FROM test;

Which is a pain. But obviously I'm just thinking that too, not really saying it.

How to escape double quote character in Oracle identifier?

It is not possible:

Quoted identifiers can contain any characters and punctuations marks as well as spaces. However, neither quoted nor nonquoted identifiers can contain double quotation marks or the null character (\0).

Oracle SELECT - Double quotes or no double quotes?

The answers and links about casing are correct, but your situation goes a little beyond a simple case issue, both because your column name started with an underscore and because your client is apaprently usually hiding the quoting from you.

If you tried to create a table with a column called _id, without quoting it, then you'd get an 'ORA-00911: invalid character' error, the cause text of which says 'identifiers may not start with any ASCII character other than letters and numbers'; which is actually wrong as well since it can't start with a number either (for example, 0_id gives 'ORA-00904: : invalid identifier'). This is backed up by the database object naming rules:

Nonquoted identifiers must begin with an alphabetic character from your database character set. Quoted identifiers can begin with any
character.

So it looks like Aqua Data Studio is following a convention of enclosing the upper-case version of the object name you supply in double-quotes, a practice mentioned in one of the linked posts.

From what you've shown, select _id from ... gets passed to Oracle as select "_ID" from ..., which is fine if the column name was created as "_ID". It appears that is the case for table1, but table2 was created as "_id" - so that case mismatch generates the legitimate ORA-00904 you're seeing.

Your client is not modifying a column name that is already enclosed in double-quotes, so select "_id" from ... is passed through to Oracle as-is, and works OK for table2 (but, conversely, would fail for table1).

Oracle requires the name to be enclosed in double quotes if it doesn't follow the rules for unquoted identifiers, and if it was created as quoted - unless the original quoted value was valid anyway, i.e. follows the unquoted rules and was entered in uppercase. Since your column name starts with an underscore, as far as Oracle is concerned all references to it have to be enclosed in double-quotes regardless of the case. Your client is just doing that in the background if you haven't quoted it yourself.

Following the advice others have given to avoid quoted identifiers and to always use names that are valid unquoted would avoid issues like this.

What exactly do quotation marks around the table name do?

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

Quoted Identifier - EF Code First

The trick here is that you don't need to suppress quoted identifiers, you just need to force all your identifiers to all caps. In Oracle identifiers that aren't quoted are converted to all caps in the catalog. Then when parsing SQL, Oracle converts non-quoted identifiers in the SQL to all caps to match the identifiers in the catalog.

This is how Oracle creates the illusion of a non-case-sensitive catalog.

You can force your entities and properties to map to all-caps database identifiers using Attributes, the Fluent API, or a custom convention.

Here's an idea of how to use Custom Conventions to normalize the Oracle table and column identifiers to be all-caps-with-underscores (assuming they Entity Properties and CLR type names are PascalCase).

namespace OracleConventions
{
    using System.Data.Entity.Core.Metadata.Edm;
    using System.Data.Entity.Infrastructure;
    using System.Data.Entity.Infrastructure.Pluralization;
    using System.Data.Entity.ModelConfiguration.Conventions;
    using System.Text.RegularExpressions;

    static class Identifiers
    {
        public static string CreateIdentifier(string entityName)
        {
            var result = Regex.Replace(entityName, "[a-z][A-Z]", m => m.Value[0] + "_" + m.Value[1]);

            return result.ToUpper();
        }
    }

    public class AllCapsTableAndColumnConvention : System.Data.Entity.ModelConfiguration.Conventions.Convention
    {

        public AllCapsTableAndColumnConvention()
        {
            var ps = (IPluralizationService)DbConfiguration.DependencyResolver.GetService(typeof(IPluralizationService), null);

            this.Types().Configure(t => t.ToTable(Identifiers.CreateIdentifier(ps.Pluralize(t.ClrType.Name))));
            this.Properties().Configure(p => p.HasColumnName(Identifiers.CreateIdentifier(p.ClrPropertyInfo.Name)));           

        }
    }
    public class AllCapsForeignKeyConvention : IStoreModelConvention<AssociationType>
    {

        public void Apply(AssociationType association, DbModel model)
        {
            // Identify ForeignKey properties (including IAs)  
            if (association.IsForeignKey)
            {
                // rename FK columns  
                var constraint = association.Constraint;
                foreach (var p in constraint.FromProperties.Union(constraint.ToProperties))
                {
                    p.Name = Identifiers.CreateIdentifier(p.Name);
                }

            }
        }

    }

}

You would then register the conventions in OnModelCreating

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Conventions.Add(new OracleConventions.AllCapsTableAndColumnConvention());
    modelBuilder.Conventions.Add(new OracleConventions.AllCapsForeignKeyConvention());
    base.OnModelCreating(modelBuilder);
}

How to make Oracle keep the case of identifiers as they appear in the query?

I found a solution for my case, thanks to Your Common Sense's comment.

When initializing the library, I pass it a PDO object - I've configured it to always use lower case, by the following code:

$options = [
    PDO::ATTR_CASE => PDO::CASE_LOWER,
];
$connection = new PDO($dsn, $username, $password, $options);
init3rdPartyLibrary($connection);

Because of this, the keys of the $result variable returned by $statement->fetch() are now lowercase and therefore the library works as expected by its authors.

---

Related Topics