String to table

[vitodcampanelli]

CREATE OR REPLACE PACKAGE apex_func
/**
  *  @PARAM   p_string colon delimited string to parse
  *  @RETURN  one coloum table
**/
AS
TYPE tbl_of_varchar
IS
  TABLE OF VARCHAR2 (32767);

  FUNCTION f_string_to_table(
      p_string IN VARCHAR2
      )
    RETURN tbl_of_varchar PIPELINED;
END apex_func ;

CREATE OR REPLACE PACKAGE BODY apex_func
AS
  FUNCTION f_string_to_table(
      p_string IN VARCHAR2
      )
    RETURN tbl_of_varchar PIPELINED
  IS
    v_temp apex_application_global.vc_arr2;
  BEGIN
    v_temp := apex_util.string_to_table(p_string);
    FOR I IN 1 .. v_temp.COUNT
    LOOP
      PIPE ROW (v_temp (I));
    END LOOP;
  END;
END apex_func ;

[martindsouza]

May also get around this by piping the following query (would avoid having to create the type)

select regexp_substr(:p1_x,'[^:]+', 1, level) my_id
from dual 
connect by regexp_substr(:p1_x, '[^:]+', 1, level) is not null

[gilcrest]

I've used Tony Andrews' parse package for a long time and it's great for this (2004 blog post): http://tonyandrews.blogspot.ie/2004/10/parsing-delimited-fields-in-character.html

[martindsouza]

@vitodcampanelli @gilcrest I have done this but slightly differently. Need ideas for new name (read below).

Side note: I've found apex_util.string_to_table very helpful however it is limited to vc2 as an input. More often than not I find that I'm having to parse clobs. I've created oos_util.string_to_table (#32) which is very similar but it takes in clobs as well. The limiting factor is that each entry between delimiters must be <= 32767

Example:

declare
  l_arr oos_util_string.tab_vc2_arr;
begin
  l_arr := oos_util_string.string_to_table(p_string => 'abc,def,ghi');

  for i in 1..l_arr.count loop
     dbms_output.put_line('val: ' || (l_arr(i)));
  end loop;
end;

The reason why I mentioned the new string_to_table is that I need help determining the name of the function for this ticket. In the initial example that @vitodcampanelli provided he called it string_to_table. As of the time of this comment I have renamed it to listunagg. Current usage:

select rownum, column_value
from table(oos_util_string.listunagg('abc,def'));

_Note: listunagg can take in both clobs and vc2s however the length between delimiters must be <= 4000. If the need comes up we can easily make a listunagg_clob._

What do you think the name of this function should be. I'm still a bit hesitant on calling it listunagg.

[vitodcampanelli]

I mainly use this to create a list of values from a colon separated string in APEX, so what about string_lov? Then again I am also fine with listunagg.

[martindsouza]

Will leave as listunagg for now. May rename or add a second name for the function to make it searchable for users looking for the "comma delimited string to table" etc. I think this will be a marketing/awareness issue to let users know about this funciton