Online Contents

SQL Scripts‎ > ‎A-I‎ > ‎

init_convert

----------------------------------------------
-- @name: init_convert
-- @author: dion cho
-- @description: convert command-separated list to pl/sql table
-----------------------------------------------

create or replace type var_tab_type as table of varchar2(4000);
/


create or replace function f_convert(p_list in varchar2)
return var_tab_type
as
    l_string       varchar2(32767) := p_list || ',';
    l_comma_index  pls_integer;
    l_index        pls_integer := 1;
    l_tab          var_tab_type := var_tab_type();
begin
  loop
      l_comma_index := instr(l_string, ',', l_index);
      exit when l_comma_index = 0;
      l_tab.extend;
      l_tab(l_tab.count) := substr(l_string, l_index, l_comma_index - l_index);
      l_index := l_comma_index + 1;
  end loop;
  return l_tab;
end f_convert;
/

Comments