MattsBits
MattsBits

MattsBits

Converting Delimited Lists To Tables In Oracle PLSQL  

by Matt Hawkins, 04/11/2010
Categories : Oracle

If you need to break up a delimited string into parts then you can use DBMS_UTILITY.comma_to_table. This function
breaks up a comma delimited string but if your string is delimited by another character you can simply replace it with
commas before passing to the function.

In the example shown below you will see the string (l_list) have its delimiter replaced with commas and the items wrapped in double speech marks. If you don't wrap the items they can not include numbers, spaces or reserved words.


DECLARE

  -- Declare variables
  l_list      VARCHAR2(100);
  s_delimiter VARCHAR2(1);
  l_tablen    BINARY_INTEGER;
  l_tab       DBMS_UTILITY.uncl_array;

BEGIN

  -- Define your delimiter and your list
  s_delimiter := ':';
  l_list := 'test1:test2';
 
  -- Swap delimiter for commas
  l_list := replace(l_list,s_delimiter,',');
  
  -- Enclose list items in double speech marks
  -- This allows the passing of number, reserved words
  -- and items with spaces in them
  l_list := '"' || replace(strValues,',','","') || '"';

  -- Convert comma separated list to table
  DBMS_UTILITY.comma_to_table (
    list   => l_list,
    tablen => l_tablen,
    tab    => l_tab);
    
  -- Items now in l_tab
  -- Number of items equal to l_tablen  
    
  -- Loop through each list item 
  FOR i IN 1 .. l_tablen LOOP
    
    -- Strip double speech marks as
    -- not required now
    l_tab(i) := replace(l_tab(i),'"','');
    
    -- Output each list item
    DBMS_OUTPUT.put_line('Item ' || i || ' : ' || l_tab(i));
    
  END LOOP;      
    
END;

Author : Matt Hawkins  Last Edit By : Matt Hawkins
PHP Powered  MySQL Powered  Valid XHTML 1.0  Valid CSS  Firefox - Take Back The Web  EUKHost - Recommended Webhosting Solutions

MattHawkins CMS v3.0 - Copyright 2009-2017