Can you SELECT everything, but 1 or 2 fields, with

Question

Is it possible, in PLSQL, to select all of the fields in a table except for 1 or 2, without having to specify the fields you want?

Example, the employee table has the fields:

• id
• firstname
• lastname
• hobbies

Is it still possible to write a query similar to

select * from employee

while leaving the field hobbies without with having to write something like this?

select id, firstname, lastname from employee

Answer 1

to create view :-

create view view_name as select id,first_name,last_name from employee where id in ('','','')

note:- this is like virtual table in your database but , it can effect values in actual table.

Answer 2

Are you running on Oracle 12c?

If so, consider whether this meets your needs:

alter table mytable modify column undesired_col_name INVISIBLE;

In that case column undesired_col_name will be completely usable, but it will be excluded from any SELECT * statements and the like (e.g., %ROWTYPE) as though it didn't exist.

Answer 3

WITH O AS
(
SELECT 'SELECT ' || rtrim('NULL AS "Dummy",' || LISTAGG('"'||column_name || '"', ',' ) within group (ORDER BY COLUMN_NAME),',')|| ' FROM "'||TABLE_NAME||'"' AS SQL, TABLE_NAME  FROM USER_TAB_COLUMNS  GROUP BY (TABLE_NAME)
)
SELECT DBMS_XMLGEN.GETXMLTYPE ((SELECT REPLACE(SQL,',COLUMNNAME','') FROM O WHERE TABLE_NAME = 'TABLENAME')) FROM DUAL

Answer 4

An old thread but, yes... there is a way to do it in Oracle:

with

employee(id, firstname, lastname, hobbies) as
(
  select 1, 'a', 'b', '1' from dual union 
  select 2, 'a', 'b', '2' from dual union 
  select 3, 'a', 'b', '3' from dual union 
  select 4, 'c', 'd', '3' from dual union 
  select 5, 'e', 'f', '2' from dual  
)

select * 
from employee 
pivot
( 
  max(1) -- fake  
  for (hobbies) -- put the undesired columns here
  IN () -- no values here...
) 
where 1=1 -- and your filters here...
order by id

To understand how the PIVOT works and why it solves the question, lets take a better example for our employee sample table:

select * 
from employee 
pivot
(
  max(id) foo,
  max(1)  bar
  for (hobbies) 
  IN ('2' as two, '3' as three)
)

The result here is:

FIRSTNAME | LASTNAME | TWO_FOO | TWO_BAR | THREE_FOO | THREE_BAR
    c          d         null      null        4           1
    e          f           5        1         null        null
    a          b           2        1          3           1

The exact same output can be achieved using this easier to understand query:

select 
  firstname,
  lastname,
  max(case when hobbies = '2' then id end) two_foo,
  max(case when hobbies = '2' then 1  end) two_bar,
  max(case when hobbies = '3' then id end) three_foo,
  max(case when hobbies = '3' then 1  end) three_bar
from employee 
group by
  firstname,
  lastname

So, the column hobbies is never selected, just as the column id, both specified inside the PIVOT clause. All other columns are grouped and selected.

Well, returning to the first query, it works for two reasons:
1- you will not lose any row in the grouping process because the id column is unique and no columns were specified for aggregations;
2- as the pivot generates N * M new columns, where N = number of values of the IN clause and M = number of aggregations specified, so having no filters and that single harmless aggregation will produce 0 * 1 = 0 new columns and will remove the ones specified in the PIVOT clause, which is just the hobbies.

ANSWER TO COMMENT 1

The first line of this question says: "... without having to specify the fields you want". In all other answers the proposed queries specifies the desired fields in the SELECT clause, except in mine, actually.

Also, in the question title says "... without writer's cramp". Well, what's the correct measure to identify a writer's cramp? My best effort would be to foresee a good SQL standard to this problem and compare with my answer. Actually, I think this "standard" could be something like SELECT * NOT IN ([col1], [col2], ...).

Now, I can see in both queries:

• a list of undesired columns;
• an IN clause;
• a three characters clause - FOR and NOT;

It means that you need to write a bit more in my approach as you need a fake aggregation and the PIVOT clause... but it's really few characters more...