0

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 at 20:11
  • Yes, invalid identifier. – Ananth N Apr 19 at 6:46
1

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 at 6:45
  • Thanks a lot Balazs! – Ananth N Apr 19 at 6:47
  • @Balazs Good spot +1 – Philᵀᴹ Apr 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.