Selecting many columns but not all on Oracle

I have written PLSQL program that generates table having usually more than 200 columns. It's number of columns is not fixed and it changes every time plsql procedure is executed. I always need to SELECT not all the columns but it is cumbersome to add them into SELECT query by hand. The columns that I don't need to select are column1, column2 and column3 every time. IF I have 200 columns I need to write

Select column4, column5, ..., column200 from plsqltable

My solution is to use another PLSQL program to look for column names from user_tab_colums (system view) for that table and compose that sql query. Is there some simpler w开发者_高级运维ay to achieve the same sql? Usally I write SELECT table.* FROM table; if I want to see all columns. Is there a way to use similar syntax to say that SELECT all but not table.col1, table.col2, table.col3 FROM table;?

There's no way to select some of the columns without listing the ones you want.

The whole plans sounds a bit strange - maybe there's a better way to do whatever it is you are doing. It's unusual in Oracle to be making tables on the fly like that.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:766825833740

Pivot table oracle with unknown number of rows to be pivot

...

Followup October 31, 2006 - 6pm Central time zone:

you would have to

a) run a query to determine the number of columns

b) use that information to build a dynamic sql query that does that.

so this is what I did, and you shouldn't need a second procedure to do this; you can do it right in your main procedure:

DROP TABLE dynamic_built_table;

CREATE TABLE dynamic_built_table
  (
    column1 VARCHAR2(1),
    column2 VARCHAR2(1),
    column3 VARCHAR2(1),
    column4 VARCHAR2(1),
    column5 VARCHAR2(1),
    column6 VARCHAR2(1)
  );

DECLARE
  l_sql VARCHAR2(5000);
BEGIN
  l_sql         := 'CREATE OR REPLACE VIEW DYNAMIC_COLUMN_VIEW AS SELECT ';
  FOR l_columns IN
  (SELECT column_name,
      column_id,
      MAX(column_id) over (partition BY table_name) max_column
    FROM user_tab_cols
    WHERE table_name = 'DYNAMIC_BUILT_TABLE'
    AND column_id    > 3
  )
  LOOP
    l_sql                  := l_sql||l_columns.column_name;
    IF l_columns.column_id != l_columns.max_column THEN
      l_sql                := l_sql||',';
    END IF;
  END LOOP;
  l_sql := l_sql||' from dynamic_built_table';
  EXECUTE immediate l_sql;
END;
/

SELECT * FROM DYNAMIC_COLUMN_VIEW;

With your assumption:

 The columns that I don't need to select are column1, column2 and column3 every time

I can suggest to create a VIEW as follows:

CREATE VIEW GOOFY AS SELECT COLUMN4, COLUMN5, ....., COLUMN200 FROM YOUR_ORIGINAL_TABLE;

... and then

SELECT * from GOOFY;

:-)

As said in a comment you can create a table and then drop the three columns, which is acceptable is you do not want to have things get complicated and if you accept to do non-optimized things.

Where are you selecting them? If it's in a PL/SQL block you can just select * (not something generally recommended!) in your cursor or into something and just ignore the bits you don't want. If you're doing this in SQL*Plus to check what's in there you could just hide the columns from the output:

column column1 noprint
column column2 noprint
column column3 noprint

select * from plsqltable;

Otherwise, why not create a view at the same time you create the table, in the same procedure, since you presumably know the column names at that point? Or if you can't modify that proc then use something like @Joel Slowik's approach in a separate procedure to recreate a view based on the current table definition.