1

I am new to SQL and recently installed Oracle 11g. I read the post here on selecting all tables from user_tables. I'm trying to select a specific table and following some of the suggestions in the post does not appear to work.

The following executes fine and returns all tables available to me including a table named faculty_t:

select * from user_tables;
select * from dba_tables;
select * from all_tables;
desc faculty_t;

But I get error when I do the following:

select * from user_tables where table_name = FACULTY_T;

The first set of statements confirm that I do have a table named faculty_t. However, trying to select this table from user_tables, all_tables, or dba_tables does not appear to work for me right now. The error message reads something like:

ORA-00904: "FACULTY_T": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:
Error at Line: 208 Column: 8

Any thoughts? Thanks!

Community
  • 1
  • 1
sedeh
  • 7,083
  • 6
  • 48
  • 65
  • maybe because SQL is case sensitive?? try faculty_t not FACULTY_T – user3640056 May 18 '15 at 12:52
  • That was the first thing I tried. As you can see from the error message itself, oracle seems to read it as FACULTY_T anyway. – sedeh May 18 '15 at 12:55
  • does the first statement work with you? "select faculty_t from user_tables;" – user3640056 May 18 '15 at 12:56
  • Please read the chapter [Basic Elements of Oracle SQL](http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements003.htm#SQLRF00218) –  May 18 '15 at 12:58

1 Answers1

3

String literals in SQL are wrapped in '. So:

select * from user_tables where table_name = 'FACULTY_T';

When you did a desc faculty_t, the SQL engine knew that a table name was expected at that spot (the syntax expects a table name there). But in your select query, sql is just looking for the value of a column that happens to have a string data type, so you need to use the ' for a string literal.

DWright
  • 9,258
  • 4
  • 36
  • 53