oracle - PL/SQL Extract Column Names and use in select statment -

not sure if possible @ im trying little manual work possible.

i have table 150 columns based on different combinations of factors. wish extract column names certain string inside column name.

i have done following this. basic example of have

  --create table      create table temp     (id number,     fac1_fac2_fac_3_fac4_fac5 number,     fac1_fac6_fac_3_fac4_fac5 number,     fac1_fac6_fac_7_fac4_fac5 number,     fac1_fac9_fac_3_fac4_fac5 number,     fac1_fac10_fac_3_fac4_fac5 number,     fac1_fac2_fac_3_fac11_fac5 number,     fac1_fac2_fac_3_fac4_fac12 number,     fac13_fac2_fac_3_fac4_fac5 number);      insert temp values (1,35634,3243,343,564,56,4635,3,334);     insert temp values (2,3434234,3243,343,564,56,435,3,34234);     insert temp values (3,5555,3243,33,564,56,435,3,3434);     insert temp values (4,34234,343,343,564,56,4335,3,34);     commit;      --extract column names      select * (                   select column_name                    user_tab_cols                    lower(table_name) ='temp'                   )     column_name '%fac13%'      --this want automate.     select id, fac13_fac2_fac_3_fac4_fac5      temp  --i want column name come fron select statment above there may lots of names.  

basically, want select rows table have fac13 in column name in 1 query if possible.

thanks

i not think can in 1 query. first, extract column names query can simplified 1 query cursor, , use dynamic select statement follows:

create or replace proc_dyn_select cursor c1                    select column_name                    user_tab_cols                    lower(table_name) ='temp' , column_name '%fac13%';  cols  c1%rowtype; sqlstmt  varchar2(2000);  begin    open c1;      loop          fetch c1 cols;          exit when c1%notfound;          sqlstmt := sqlstmt ||cols.column_name||',';       end loop;    close c1;    sqlstmt := 'select '||substr(sqlstmt, 1, length(sqlstmt)-1)||' temp';    execute immediate sqlstmt;  exception when others dbms_output.put_line('error '||sqlerrm); end; / 

explanation

first, cursor store columns meet conditions (to table temp , column names have sub string fac13. in execution section (after begin), build query dynamically using columns names stored in cursor c1. each round of loop, column name added string , concatenated comma. string of columns built 'col1, col2, col3, ... coln,'. string stored in sqlstmt variable.

after loop end, amend string build sql statement, adding keywords select, from , table name. however, remove last character of sqlstmt variable, comma.

execute immediate statement, run query stored in sqlstmt.

by using procedure, can pass parameters, such procedure can perform dynamic sql statement want.