Oracle query to fetch column names
oracle, sql
Solution
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.
Problem
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?)