Oracle Table Column Name with Space

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

how to SELECT a column which has a space in between its name

Single quotes (') denote character literals. I.e., you're selecting the string 'REJECTED COST', which, obviously, cannot be cast to a number.
In order to select a column name with a space, you should use double quotes ("). Note that they need to be escaped, as you're using them inside a c++ string, which is also denoted by double quotes:

string sqlStmt = "SELECT \"REJECTED COST\", APPROVED_COST FROM COST_TABLE where PART_NUM= 'PN4879-1'";

How to address column names with spaces in oracle, when the query is already inside double quotes?

If you are using C#, Java or PHP on this, escape the double quotes with backslash \

result = db.Query("SELECT ACTUAL_N_WELLS, TARGET_N_WELLS, \"YTD ACTUAL\" AS YTD_ACTUAL....");

Error trying to rename columns with space in oracle table. Error - SQL Error : ORA- 00946 : missing TO keyword

Since Oracle doesn't know the space is part of the column name, you have to enclose it in quotes to make sure it recognizes it correctly:

Alter table employee rename column "Employee Name" to Employee_Name

Oracle: How to reference an alias with a space from a subquery in a comparison operation

Yes, you can do this, but you have to enclose those names in double quotes:

-- SQL Example
SELECT "Just one" FROM (
  SELECT 1 AS "Just one" FROM dual
);

Output:

  Just one
----------
         1 

-- PL/SQL Example
BEGIN
  FOR v_rec IN (SELECT 1 AS "Just one" FROM dual)
  LOOP
    dbms_output.put_line(v_rec."Just one");
  END LOOP;
END;
/

Output:

1

And a little curiosity:

BEGIN
  FOR v_rec IN (SELECT COUNT(1) * 10 FROM dual)
  LOOP
    dbms_output.put_line(v_rec."COUNT(1)*10");
  END LOOP;
END;

This works.. and prints:

10

Edit

You can reference the aliased column elsewhere in your query, for example:

SELECT 'It is just one' FROM (
  SELECT "Just one" FROM (
    SELECT 1 AS "Just one" FROM dual
  ) t
WHERE t."Just one" = 1 -- prefixing with t. is not necessary in this particular example
);

Output:

It is just one

How to avoid space(s) while inserting data in a table in oracle

You can use a trigger:

CREATE OR REPLACE TRIGGER EMPLOYEE_BIU
  BEFORE INSERT OR UPDATE ON EMPLOYEE
  FOR EACH ROW
BEGIN
  :NEW.DEPT := TRIM(:NEW.DEPT);
END EMPLOYEE_BIU;

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.

---

Related Topics