Want more features on Pastebin? Sign Up, it's FREE!
Guest

Untitled

By: a guest on May 2nd, 2012  |  syntax: None  |  size: 1.54 KB  |  views: 10  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  1. Can you SELECT everything, but 1 or 2 fields, without writer's cramp?
  2. select * from employee
  3.        
  4. select id, firstname, lastname from employee
  5.        
  6. select column_name||','
  7. from all_tab_columns
  8. where table_name = 'YourTableName'
  9.        
  10. create or replace function quote_list(p_list in stringlist)
  11. return stringlist as
  12.   v_list stringlist := stringlist();
  13. begin
  14.   v_list.extend(p_list.last);
  15.   for i in p_list.first .. p_list.last loop
  16.     v_list(i) := '''' || p_list(i) || '''';
  17.   end loop;
  18.  
  19.   return v_list;
  20. end;
  21. /
  22. show errors
  23.  
  24. create or replace function query_generator(
  25.   p_table in varchar2,
  26.   p_exclude in stringlist
  27. ) return varchar2 as
  28.   v_table constant varchar2(31) := upper(p_table);
  29.   v_exclude constant varchar2(32676) :=
  30.     upper(putil.join(quote_list(p_exclude), ','));
  31.   v_stmt_str constant varchar2(32676) :=
  32.     'select column_name from all_tab_columns where table_name = ''' ||
  33.     v_table || ''' and column_name not in (' || v_exclude ||
  34.     ') order by column_id';
  35.   type stmt_cur_t is ref cursor;
  36.   v_stmt_cur stmt_cur_t;
  37.   v_column_name varchar2(31);
  38.   v_query varchar2(32676) := 'select ';
  39. begin
  40.   open v_stmt_cur for v_stmt_str;
  41.  
  42.   loop
  43.     fetch v_stmt_cur into v_column_name;
  44.     exit when v_stmt_cur%notfound;
  45.     v_query := v_query || lower(v_column_name) || ', ';
  46.   end loop;
  47.  
  48.   close v_stmt_cur;
  49.  
  50.   select rtrim(v_query, ', ') into v_query from dual;
  51.  
  52.   v_query := v_query || ' from ' || p_table || ';';
  53.  
  54.   return v_query;
  55. end;
  56. /
  57. show errors
  58.        
  59. exec dbms_output.put_line(query_generator('all_tables', stringlist('segment_created', 'result_cache')))
clone this paste RAW Paste Data