Concatenate a List of Strings in PL/SQL

I ran into a requirement today where I had to combine a number of strings (varchars) into a delimited list. Some strings would be NULL and should be ignored.

I played around and found a bunch of different approaches which I’d like to share.

Let’s assume we have a package info_util with the function person which will combine a number of information about a person to a delimited list (to simplify the example all information is passed by parameter):

  i_name => 'Luke Skywalker',
  i_alignment => 'light',
  i_comment => 'Most powerful jedi of all times'));
-- Output: Luke Skywalker, light, Most powerful jedi of all times

  i_name => 'Vader',
  i_title => 'Darth',
  i_alignment => 'dark',
  i_comment => 'Pretty evil'));
-- Output: Vader, Darth, dark, Pretty evil
Continue reading

PL/SQL Object Types: putting the Structure into the Force Powers Table

Earlier this week, I introduced a way to extract a chunk of functionality from a view into an Object Type, using the object-oriented capabilities of the Oracle database.

Today I want to go one step further and make the Object Type not part of the view logic, but part of the underlying table. The goal is to get rid of the still rather complicated view and replace the VARCHAR2 STRUCTURE column with a t_numeric_structure object.

Continue reading