CODEWITHSUNDEEP
oracle-databaseoracle11g
sedeh
sedeh

Reputation: 7313

SELECT a table from oracle data dictionary

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!

Upvotes: 1

Views: 1153

Answers (1)

DWright
DWright

Reputation: 9500

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.

Upvotes: 3

Related Questions