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

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 WM_CONCAT(column_name)
FROM all_tab_columns
WHERE table_name = 'table_name'
GROUP BY table_name;

Answer 2

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

Answer 3

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 4

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 5

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 6

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'