Geek asks and answers

Selecting a large number of columns, but not all, in Oracle

I wrote a PLSQL program that generates a table that usually has more than 200 columns. The number of columns is not fixed and changes each time the plsql procedure is executed. I always need to select not all columns, but it is cumbersome to add them to the SELECT query manually. The columns that I don’t need to select are columns1, columns2 and columns3 each 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 search for column names from user_tab_colums (system view) for this table and create this SQL query. Is there an easier way to achieve the same sql? I usually write a SELECT table. * FROM table; if I want to see all the columns. Is there a way to use similar syntax to say that SELECT is all, but not table.col1, table.col2, table.col3 FROM table ;?

+3
source share
5 answers

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

Hinge support table with an unknown number of rows to be reduced to the axis

...

Follow-up report October 31, 2006 - 18:00 Central time zone:

you need

a) run a query to determine the number of columns

b) SQL-, .

, , ; :

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;
+1

, .

- , , . Oracle " ".

+2

:

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

VIEW :

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

...

SELECT * from GOOFY;

: -)

+1

, , , , , , .

+1

? PL/SQL-, select * ( - !) into - , . SQL * Plus, , , :

column column1 noprint
column column2 noprint
column column3 noprint

select * from plsqltable;

, , , , ? , , @Joel Slowik , , .

0

More articles:


All Articles