Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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')))
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement