How to get data from a Oracle database table except specific columns?

Question

I want to write a query to fetch data from a table except some columns which start name like given in the wildcard criteria as bellow example pseudo code. Is it possible on oracle?

(this can be done as adding column names for the select clause. but assuming there will be new columns will add in future , i want to write a more generic code)

example

Employee(id , name , age, gender)

select *
from table_name
where column_name not like a%

after query it should display a table with

Employee(id , name . gender)

the age column is not there because we are not include in the result

Sample data and expected results would be helpful. `not like 'a%'` -- missing single quotes there... Or do you mean you want to dynamically select column names if they're like `a`? If so, um, why? — sgeddes, Mar 10 '16 at 04:23
I'm afraid this isn't possible. If you don't select all columns (`*`) from a particular table then you have to list them explicitly. — David Faber, Mar 10 '16 at 04:24
Hi . thank you for the comments. i want to vertically slice the table to exclude columns in the condition . so for example if i got table as Employee(id , name , age, gender) . what i want is to get the table excluding specific column given in the criteria and display rest of the result. — Nipun Alahakoon, Mar 10 '16 at 04:48
See my answer to have a programmatic way to do so... but it might not be what you are looking for. — J. Chomel, Mar 30 '16 at 13:56

score 2 · Answer 1 · answered Mar 10 '16 at 08:49

You can try with some dynamic SQL:

declare
    vSQL  varchar2(32767);
    vClob clob;
begin
    /* build the query */
    select distinct 'select ' || listagg(column_name, ',') within group (order by column_name) over (partition by table_name)|| ' from ' || table_name
    into vSQL
    from user_tab_columns
    where table_name = 'EMPLOYEE'
      and column_name not like 'A%';

    /* print the query */
    dbms_output.put_line(vSQL);

    /* build an XML */
    select DBMS_XMLGEN.getXML(vSQL)
    into vClob
    from dual;   
    dbms_output.put_line(vClob);

    /* build a CLOB with all the columns */
    vSQL := replace (vSQL, ',', ' || '' | '' || ' );
    execute immediate vSQL into vClob;    
    dbms_output.put_line(vClob);
end;

In this way you can dynamically build a query that extracts all the columns exept those matching a pattern.

After building the query, the question is how to fetch it, given that you don't know in advance what columns you are fetching. In the example I make an XML and a single row; you can use the query in different ways, depending on your needs.

score 1 · Answer 2 · answered Mar 10 '16 at 08:48

Duplicate, but I like writing PL, so here is how you could, creating a temp table, then select * from it:

declare
   your_table varchar2(40) := 'CHEMIN';
   select_to_tmp varchar2(4000) := 'create table ttmp as select ';
begin
    -- drop temporary table if exists
    begin
       execute immediate 'drop table ttmp';
  Exception 
   When others Then 
        dbms_output.put_line(SQLERRM);
    end;

    for x in (
      select column_name from all_tab_columns 
      where table_name=your_table
       and column_name not in (
       -- list columns you want to exclude
       'COL_A'
     , 'COL_B'
     )
   )
   loop
       select_to_tmp := select_to_tmp|| x.column_name ||',';
       dbms_output.put_line(x.column_name);
   end loop;
   -- remove last ','
   select_to_tmp := substr(select_to_tmp, 1, length(select_to_tmp) -1);
   -- from your table
   select_to_tmp := select_to_tmp||' from '||your_table;
   -- add conditions if necessary
   -- select_to_tmp := select_to_tmp|| ' where rownum < 1 '
   dbms_output.put_line(select_to_tmp);
   -- then create the temporary table using the query you generated:
   execute immediate select_to_tmp;
end;
/ 


SELECT * FROM ttmp;

How to get data from a Oracle database table except specific columns?

2 Answers2