SELECT a table from oracle data dictionary

Question

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!

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) — a_horse_with_no_name, May 18 '15 at 12:58

DWright · Answer 1 · 2015-05-18T13:00:35.063

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.

edited May 18 '15 at 13:00

answered May 18 '15 at 12:55

DWright

9,152
4
35
53

Table names are stored in upper case in Oracle. – a_horse_with_no_name May 18 '15 at 12:59
I missed that subtle difference. It works with upper case FACULTY_T enclosed in quotes! – sedeh May 18 '15 at 12:59

SELECT a table from oracle data dictionary

1 Answers1