Imported a table into Oracle 12g using data pump. Some of the columns, though present in the table as shown by describe, cannot be selected but they show up in select *.

Cannot select them individually or apply where clause on only these columns.

  • 1
    "Cannot select them individually or apply where clause on only these columns." - what does this mean? Do you get an error message?
    – Philᵀᴹ
    Apr 18 '19 at 20:11
  • Yes, invalid identifier.
    – Ananth N
    Apr 19 '19 at 6:46

Let me guess: when you describe the table, you see lowercase letters in column names, meaning the column name is case-sensitive, but you tried to select it without double quotes, in a case-insensitive manner.

Try quotes as in the below example:

SQL> create table t1(column1 number, "column2" number);

Table created.

SQL> insert into t1 values (1, 1);

1 row created.

SQL> commit;

Commit complete.

SQL> desc t1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COLUMN1                                            NUMBER
 column2                                            NUMBER

SQL> select COLUMN1, column2 from t1;
select COLUMN1, column2 from t1
ERROR at line 1:
ORA-00904: "COLUMN2": invalid identifier

SQL> select * from t1;

   COLUMN1    column2
---------- ----------
         1          1

SQL> select COLUMN1, "column2" from t1;

   COLUMN1    column2
---------- ----------
         1          1
  • Tried with quoted column name exactly as it was in desc. And it works!
    – Ananth N
    Apr 19 '19 at 6:45
  • Thanks a lot Balazs!
    – Ananth N
    Apr 19 '19 at 6:47
  • @Balazs Good spot +1
    – Philᵀᴹ
    Apr 19 '19 at 8:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.