Advertisement
Guest User

Untitled

a guest
May 2nd, 2012
24
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.54 KB | None | 0 0
  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')))
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement