Can you SELECT everything, but 1 or 2 fields, without writer's cramp?
select * from employee
select id, firstname, lastname from employee
select column_name||','
from all_tab_columns
where table_name = 'YourTableName'
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
exec dbms_output.put_line(query_generator('all_tables', stringlist('segment_created', 'result_cache')))