Oracle DB: any function to select all the database columns except for one (which is known)?

Question

I am using Oracle Database and I need to realize a query which retrieves all the values of a table record (for a specific WHERE condition), except for one which is known.

Imagine to have the following table: Sample table

Where you do not want to retrieve the "Age" column, but where - in next releases of the software - the table could have more columns respect to the ones actually present.

Is there any command in Oracle which excludes a specific column (always known, as in the example "Age") and allows me to retrieve all the other values?

Thanks in advance!

you can create a dynamic query using `all_tab_columns` – Sudipta Mondal Jul 14 '17 at 13:56 — Sudipta Mondal, Jul 14 '17 at 13:56

Shailesh Yadav · Accepted Answer · 2017-07-14T14:29:59.637

0

You can make that particular column Invisible using following query:

alter table TABLE_NAME modify COLUMN_NAME INVISIBLE;

This will exclude that column from select * statement unless and until you specify that particular column in select clause like below:

select COLUMN_NAME from TABLE_NAME;

From Your sample data:

alter table SAMPLE_TABLE modify Age INVISIBLE;

select * FROM SAMPLE_TABLE will produce

select FirstName, LastName, Address, City, Age from SAMPLE_TABLE will produce:

edited Jul 14 '17 at 14:29

answered Jul 14 '17 at 14:16

Shailesh Yadav

1,061
1
15
30

Hi and thanks first of all. Is the column invisible to all the users? – GGG Jul 14 '17 at 14:40
@GGG Invisible to all sessions (all users). It is defined in table definition. – fg78nc Jul 14 '17 at 19:43

fg78nc · Answer 2 · 2017-07-14T14:39:36.830

There are several approaches

1)You can set column UNUSED.It won't be retrieved (and it wont be used) with the queries. This would be permanent. You can't get then column back, the only allowed op would be DROP UNUSED COLUMNS.

   ALTER TABLE sample_table SET UNUSED(age);

2)You can set column INVISIBLE, this is temporary. It won't be retrieved, unless you explicitly reference it in SELECT query.

   ALTER TABLE sample_table MODIFY age INVISIBLE; 
   // to change it back to VISIBLE
   ALTER TABLE sample_table MODIFY age VISIBLE;

3)Create VIEW without age column and then query view instead of querying TABLE.

 CREATE VIEW sample_table_view AS 
 SELECT first_name, last_name, address, city FROM sample_table;

Oracle DB: any function to select all the database columns except for one (which is known)?

2 Answers2