Oracle Db Quote Column Names

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 .

Enclosing column names within double quoatation marks with CREATE command in Oracle doesn't work properly. Why?

Against common believe, Oracle is case sensitive in column and table names. It just converts everything to upper case by default.

But if you use names in double quotes, you tell Oracle to create the column in the exact spelling you provided (lower case in the CREATE statement).

Since in the SELECT statement, you don't use quotes, the names are converted to upper case and are therefore not equal to the lower case names in the CREATE statement.

So this should work:

SELECT "prod_id", "product_name" FROM products;

If you don't know how column or table names are specified, you can look it up in the data dictionary. You will find lower case column names for your product table, but upper case table name, since it wasn't quoted.

Use of Single Quotes Before Column Name Oracle

' ' work_instance selects a constant string value consisting of a single space and assigns the column alias work_instance to that value.

It's the same concept as

ROUND((sysdate-PR.proposal_date)) as processing_time

With the difference that the actual value isn't taken from a real column, but is a constant value and that the (optional) as keyword has not been used.

How do I escape a reserved word in Oracle?

From a quick search, Oracle appears to use double quotes (", eg "table") and apparently requires the correct case—whereas, for anyone interested, MySQL defaults to using backticks (`) except when set to use double quotes for compatibility.

Oracle Pivot query gives columns with quotes around the column names. What?

you can provide aliases to the new columns in the pivot statement's IN clause.
(NB: This is different from the standard where clause IN() which does not allow aliases.)

with testdata as
(
    select 'Fred' First_Name, 10 Items from dual
    union
    select 'John' First_Name, 5  Items from dual
    union 
    select 'Jane' First_Name, 12 Items from dual
    union
    select 'Fred' First_Name, 15 Items from dual
)
select * from testdata
pivot (
      sum(Items) 
      for First_Name
      in ('Fred' as fred,'John' as john,'Jane' as jane)
      )

and also for your aggregate clause which is necessary if you have multiple clauses..

with testdata as
(
    select 'Fred' First_Name, 10 Items from dual
    union
    select 'John' First_Name, 5  Items from dual
    union 
    select 'Jane' First_Name, 12 Items from dual
    union
    select 'Fred' First_Name, 15 Items from dual
)
select * from testdata
pivot (
    sum(Items) itmsum,
    count(Items) itmcnt
    for First_Name
    in ('Fred' as fred,'John' as john,'Jane' as jane)
   )

returns

FRED_ITMSUM FRED_ITMCNT JOHN_ITMSUM JOHN_ITMCNT JANE_ITMSUM JANE_ITMCNT
----------- ----------- ----------- ----------- ----------- -----------
         25           2           5           1          12           1

Of course you can then go full circle and use standard oracle aliasing and rename them to whatever you like including putting quotes back in again..

with testdata as
(
    select 'Fred' First_Name, 10 Items from dual
    union
    select 'John' First_Name, 5  Items from dual
    union 
    select 'Jane' First_Name, 12 Items from dual
    union
    select 'Fred' First_Name, 15 Items from dual
)
select FRED_ITMSUM "Fred's Sum", FRED_ITMCNT "Fred's Count"
     , JOHN_ITMSUM "John's Sum", JOHN_ITMCNT "John's Count"
     , JANE_ITMSUM "Janes's Sum", JANE_ITMCNT "Janes's Count"
from testdata
pivot (
    sum(Items) itmsum,
    count(Items) itmcnt
    for First_Name
    in ('Fred' as fred,'John' as john,'Jane' as jane)
   )

gives

Fred's Sum Fred's Count John's Sum John's Count Janes's Sum Janes's Count
---------- ------------ ---------- ------------ ----------- -------------
        25            2          5            1          12             1

Why do you only need double quotation marks in SQL for particular cases?

Yes, it is about keywords. You can double quote everything (tables, columns) to avoid this but I can understand you don't want to do this.

To have a list of standard keywords: SQL Keywords
But you can see UID is not in this list as I assume it is a reserved keyword by your database implementation. I had the same problem with a table called "order" as it contains orders. ORDER is a keyword so I had to quote it each time.

So best is to test your statements using a SQL client tool.

Since you mention Oracle: Oracle keywords: "You can obtain a list of keywords by querying the V$RESERVED_WORDS data dictionary view."

Oracle table column name with space

It is possible, but it is not advisable. You need to enclose the column name in double quotes.

create table my_table ("MY COLUMN" number);

But note the warning in the documentation:

Note: 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.

The name will be case-sensitive, and you wil have to enclose the name in double quotes every time you reference it:

select "MY COLUMN" from my_table;

So... don't, would be my advice...

Oracle query to fetch column names

The Oracle equivalent for information_schema.COLUMNS is USER_TAB_COLS for tables owned by the current user, ALL_TAB_COLS or DBA_TAB_COLS for tables owned by all users.

Tablespace is not equivalent to a schema, neither do you have to provide the tablespace name.

Providing the schema/username would be of use if you want to query ALL_TAB_COLS or DBA_TAB_COLS for columns OF tables owned by a specific user. in your case, I'd imagine the query would look something like:

String sqlStr= "
SELECT column_name
  FROM all_tab_cols
 WHERE table_name = 'USERS'
   AND owner = '" +_db+ "'
   AND column_name NOT IN ( 'PASSWORD', 'VERSION', 'ID' )"

Note that with this approach, you risk SQL injection.

EDIT: Uppercased the table- and column names as these are typically uppercase in Oracle; they are only lower- or mixed case if created with double quotes around them.

Rename column if it has single quote

Just use double quotes to wrap the columns' names even in your script; for example, this works:

alter table test rename column "'test1" to test1

Your script could be edited as:

EXECUTE IMMEDIATE
    'ALTER TABLE '
    || table_rec.TABLE_NAME
    || ' RENAME COLUMN "' /* open the quote */
    || column_rec.COLUMN_NAME
    || '" TO '            /* and close */
    || new_column_name;

Also, notice that in this way all the renamed columns will be upper case; if this is what you need, well done, otherwise you have to wrap with double quotes even the new names:

...
|| '" TO "'                /* and close, and reopen */
|| new_column_name || '"'; /* and close again */

---

Related Topics