Can you SELECT everything, but 1 or 2 fields, without writer's cramp?

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

No - you either get all fields (*) OR specify the fields you want.

Answer 2

If you want to avoid the writer's cramp, you can use SQL Developer and have it generate the column list for you:

select column_name||','
from all_tab_columns
where table_name = 'YourTableName'

And then just take out the one or two columns that you don't want.

You can also use

SELECT listagg(column_name, ',') within group (order by column_name) columns
FROM all_tab_columns
WHERE table_name = 'TABLE_NAME'
GROUP BY table_name;

Answer 3

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...

Answer 4

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 5

Oracle 18c polymorphic table functions make it possible to select everything from a table and exclude a list of columns:

select * from everything_but(employee, columns(hobbies));

ID   FIRSTNAME   LASTNAME
--   ---------   --------
1    John        Smith

Creating that function requires the below package, which is copied from Tim Hall's website https://oracle-base.com/articles/18c/polymorphic-table-functions-18c. Note that the package does not contain anything specific to the table - this solution will work for any Oracle table.

CREATE OR REPLACE PACKAGE poly_pkg AS

  FUNCTION everything_but(tab IN TABLE,
                          col IN COLUMNS)
  RETURN TABLE PIPELINED
  ROW POLYMORPHIC USING poly_pkg;

  FUNCTION describe (tab IN OUT DBMS_TF.table_t,
                     col IN     dbms_tf.columns_t)
    RETURN DBMS_TF.describe_t;

END poly_pkg;
/


CREATE OR REPLACE PACKAGE BODY poly_pkg AS

  FUNCTION describe (tab IN OUT DBMS_TF.table_t,
                     col IN     dbms_tf.columns_t)
    RETURN DBMS_TF.describe_t
  AS
  BEGIN
    -- Loop through all the table columns.
    FOR i IN 1 .. tab.column.count() LOOP
      -- Loop through all the columns listed in the second parameter.
      FOR j IN 1 .. col.count() LOOP
        -- Set pass_through to true for any columns not in the exclude list.
        tab.column(i).pass_through := (tab.column(i).description.name != col(j));
        -- Exit inner loop if you find a column that shouldn't be included.
        EXIT WHEN NOT tab.column(i).pass_through;
      END LOOP;
    END LOOP;

    RETURN NULL;
  END;

END poly_pkg;
/

I also created this simple wrapper function to give it a better name. And created a simple example table.

CREATE OR REPLACE FUNCTION everything_but(tab IN TABLE, col in COLUMNS)
  RETURN TABLE PIPELINED
  ROW POLYMORPHIC USING poly_pkg;
/

create table employee as
select 1 id, 'John' firstname, 'Smith' lastname, 'fishing' hobbies from dual;

Answer 6

query_generator is a PL/SQL function that returns a select string for a table (1st parameter) but excluding some columns (2nd parameter).

stringlist and putil.join are from PL/SQL Commons.

stringlist is a simple list of strings: create type StringList as table of varchar2(32767); and putil.join is just a normal join function.

create or replace function quote_list(p_list in stringlist)
return stringlist as
  v_list stringlist := stringlist();
begin
  v_list.extend(p_list.last);
  for i in p_list.first .. p_list.last loop
    v_list(i) := '''' || p_list(i) || '''';
  end loop;

  return v_list;
end;
/
show errors

create or replace function query_generator(
  p_table in varchar2,
  p_exclude in stringlist
) return varchar2 as
  v_table constant varchar2(31) := upper(p_table);
  v_exclude constant varchar2(32676) :=
    upper(putil.join(quote_list(p_exclude), ','));
  v_stmt_str constant varchar2(32676) :=
    'select column_name from all_tab_columns where table_name = ''' ||
    v_table || ''' and column_name not in (' || v_exclude ||
    ') order by column_id';
  type stmt_cur_t is ref cursor;
  v_stmt_cur stmt_cur_t;
  v_column_name varchar2(31);
  v_query varchar2(32676) := 'select ';
begin
  open v_stmt_cur for v_stmt_str;

  loop
    fetch v_stmt_cur into v_column_name;
    exit when v_stmt_cur%notfound;
    v_query := v_query || lower(v_column_name) || ', ';
  end loop;

  close v_stmt_cur;

  select rtrim(v_query, ', ') into v_query from dual;

  v_query := v_query || ' from ' || p_table || ';';

  return v_query;
end;
/
show errors

Usage example:

exec dbms_output.put_line(query_generator('all_tables', stringlist('segment_created', 'result_cache')))

Answer 7

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 8

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 9

What the OP was looking for was something like:

SELECT * MINUS hobbies from...

The best thing to do to avoid a lot of typing (and get all the column names correct) is to open the table description and cut and paste all the column names and delete the ones you don't want, comma separate the remaining ones and put them on a singe line or two.

It is easy, fast, accurate and you won't confuse the next person who has to work on your code.

Answer 10

Here is another option to get a list of the fields that allows you to specify the delimiter:

select listagg(column_name, ', ') WITHIN GROUP (ORDER BY rownum)
from all_tab_columns
where table_name='table'

Answer 11

here is the solutions... i need all columns except password

(select column_name ||',' from user_tab_columns where table_name ='USERS' and column_name <>'PASSWORD')

Answer 12

This is why SAS has provided a DROP clause in its Implicit SQL and DATA STEP for decades.

select t.* from myDB.mytable(drop=hobbies) t

or

create table /* or view / mytable(drop=hobbies) as select t. from myDB.mytable t

no matter how many columns there are, and, no matter what RDMBs that "myDB" points to i.e. ORACLE, Teradata, SAS, DB2, Netezza, etc. Prior to submitting the explicit database SQL to the RDMBs, SAS gathers all of the column names and removes the ones you specified, creating a SELECT statement with only the desired columns programmatically.