Oracle query to fetch column names


I have a mySQL query to get columns from a table like this:

String sqlStr="select column_name 
from information_schema.COLUMNS 
where table_name='users' 
and table_schema='"+_db+"' 
and column_name not in ('password','version','id')"

How do I change the above query in Oracle 11g database? I need to get columns names as a resultset for table 'users' excluding certain columns, specifying a schema. Right now I have all tables in my new tablespace, so do I specify tablespace name in place of schema name?

Also is there a generic HQL for this? In my new Oracle database (I am new to Oracle), I only have tablespace name, so is that equivalent to schema name (logically?)

8/8/2017 2:46:12 PM

Accepted Answer

The Oracle equivalent for information_schema.COLUMNS is USER_TAB_COLS for tables owned by the current user, ALL_TAB_COLS or DBA_TAB_COLS for tables owned by all users.

Tablespace is not equivalent to a schema, neither do you have to provide the tablespace name.

Providing the schema/username would be of use if you want to query ALL_TAB_COLS or DBA_TAB_COLS for columns OF tables owned by a specific user. in your case, I'd imagine the query would look something like:

String sqlStr= "
SELECT column_name
  FROM all_tab_cols
 WHERE table_name = 'USERS'
   AND owner = '" +_db+ "'
   AND column_name NOT IN ( 'PASSWORD', 'VERSION', 'ID' )"

Note that with this approach, you risk SQL injection.

EDIT: Uppercased the table- and column names as these are typically uppercase in Oracle; they are only lower- or mixed case if created with double quotes around them.

6/6/2018 2:46:53 PM

in oracle you can use

desc users

to display all columns containing in users table


You may try this : ( It works on 11g and it returns all column name from a table , here test_tbl is the table name and user_tab_columns are user permitted table's columns )

select  COLUMN_NAME  from user_tab_columns
where table_name='test_tbl'; 

The query to use with Oracle is:

String sqlStr="select COLUMN_NAME from ALL_TAB_COLUMNS where TABLE_NAME='"+_db+".users' and COLUMN_NAME not in ('password','version','id')"

Never heard of HQL for such queries. I assume it doesn't make sense for ORM implementations to deal with it. ORM is an Object Relational Mapping, and what you're looking for is metadata mapping... You wouldn't use HQL, rather use API methods for this purpose, or direct SQL. For instance, you can use JDBC DatabaseMetaData.

I think tablespace has nothing to do with schema. AFAIK tablespaces are mainly used for logical internal technical purposes which should bother DBAs. For more information regarding tablespaces, see Oracle doc.


The only way that I was able to get the column names was using the following query:

FROM all_tab_columns atc
WHERE table_name like 'USERS'

the point is that in toad u have to write table name capital, like this:

select *
FROM all_tab_columns
where table_name like 'IDECLARATION';