String Parsing, Table Functions and OWB10gR1

I worked with a client recently who needed to parse a comma-delimited string as part of an OWB mapping. The mapping contained a source table, which we'll call STAFF_TEAMS

SQL> create table staff_teams (
  2  staff_id number,
  3  team_members varchar2(255));
Table created.

which contained a field, TEAM_MEMBERS, which contained a comma-delimited list of team members who reported to a particular staff ID.

SQL> insert into staff_teams values (1,'Mark,Sonia,Jon,Pete');
1 row created.
SQL> insert into staff_teams values (2,'Andrew,Julian,Keith,Mick,Dave');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from staff_teams;
STAFF_ID TEAM_MEMBERS

     1 Mark,Sonia,Jon,Pete
     2 Andrew,Julian,Keith,Mick,Dave

2 rows selected.

What the client wanted to do was to process this delimited list of team members into separate team member records in a target table called STAFF_MEMBER_BREAKDOWN, like this:

SQL> create table staff_member_breakdown (
  2  staff_id number,
  3  team_member_name varchar2(20));
Table created.

getting a result that looked like this:

SQL> select * from staff_member_breakdown;
STAFF_ID TEAM_MEMBER_NAME

     1 Mark
     1 Sonia
     1 Jon
     1 Pete
     2 Andrew
     2 Julian
     2 Keith
     2 Mick
     2 Dave

9 rows selected.

Now to me, this looked like the classic use of a table function - a special kind of Oracle function that generates a table of data as its output. Table functions are particularly useful for ETL work as they can optionally take a REF CURSOR as input, meaning that you can pass the results of a SELECT statement to it as input, it then processes it and spits out another table of data as output. In our case, we would use a SELECT statement to select against the STAFF_TEAMS table, pass the results on to a table function which would parse the comma delimited list of people, then pass out as output a table of staff IDs and individual team members.

OWB10gR1 supports table functions as mapping operators, although the support is fairly basic in this version. You actually have to define the table function, and any supporting TYPEs, outside of OWB and then reference them within the mapping. So, the first thing to do then is to define a record type, then a table type based on this record type, for my function to use as it's output datatype.

SQL> create or replace type staff_member_breakdown_record is object
  2  ( staff_id number,
  3    team_member_name varchar2(20));
  4  /
Type created.
SQL> create or replace type staff_member_breakdown_table

2  is table of staff_member_breakdown_record;

3  /
Type created.

Now, I need to define a little package that will just hold a REF CURSOR type that I'll use in the function.

SQL> create or replace package refcur_pkg
  2  as
  3  type staff_teams_cur_type is ref cursor;
  4  end refcur_pkg;
  5  /
Package created.

Now I can create the table function to do the parsing. Note the use of the DBMS_UTILITY.COMMA_TO_TABLE function - thanks to Prasad from work for pointing this one out.

(UPDATE 20.10.05 : A couple of people have pointed out that this built in function only works when the values you're parsing are valid Oracle object names, i.e. don't contain spaces, don't start with a number and so on. So in reality you're going to have to roll your own string parser if you need to use this in anger. For the purposes of this article though, which was more about using table functions with OWB, it still illustrates the point. Just watch out though.)

SQL> create or replace function parse_teams
  2   (staff_teams_cur refcur_pkg.staff_teams_cur_type)
  3  return staff_member_breakdown_table
  4  pipelined
  5  is
  6  c_string   VARCHAR2(250);
  7  cnt        BINARY_INTEGER;
  8  my_table   dbms_utility.uncl_array;
  9  v_staff_id   NUMBER;
 10  v_team_members  VARCHAR2(255);
 11
 12  begin
 13
 14    loop
 15
 16    fetch staff_teams_cur into v_staff_id, v_team_members;
 17    exit when staff_teams_cur%notfound;
 18
 19      dbms_utility.comma_to_table(v_team_members, cnt, my_table);
 20
 21   for i in 1 .. my_table.count loop
 22
 23   if my_table(i) is not null then
 24
 25    pipe row (staff_member_breakdown_record(
 26     to_number(v_staff_id), my_table(i)));
 27
 28   end if;
 29
 30      end loop;
 31    end loop;
 32  end parse_teams;
 33  /
Function created.

Now, to test it out, I can just run a SELECT statement against the source table and pipe the results through the table function.

SQL> select * from table(parse_teams(cursor(select * from staff_teams)));
STAFF_ID TEAM_MEMBER_NAME

     1 Mark
     1 Sonia
     1 Jon
     1 Pete
     2 Andrew
     2 Julian
     2 Keith
     2 Mick
     2 Dave

9 rows selected.

Good stuff. The next step is to create an OWB target module, import in the two tables (but not the function we've just created), then create a mapping. Drop on the source and target tables, and then a table function operator from the mapping toolbox. Right-click on the table function's INGRP1 group ...

and then make sure REF CURSOR is specified as the input parameter.

Next, drag the INOUTGRP1 group from the source table to the INGRP1 input group for the table function, and then define the two output attributes which you then map to the corresponding target table columns.

Finally, right-click on the table function, select Operator Properties, and enter the name of the table function

Next, deploy the mapping as normal using the Deployment Manager, and then execute the package to run the mapping. If you've done it all correctly, you should get something like this:

Then, just to double-check:

SQL> select * from staff_member_breakdown;
STAFF_ID TEAM_MEMBER_NAME

     1 Mark
     1 Sonia
     1 Jon
     1 Pete
     2 Andrew
     2 Julian
     2 Keith
     2 Mick
     2 Dave

9 rows selected.

So there you go. We've used a table function to parse some comma delimited strings, and embedded it in an OWB mapping to make it a bit more manageable. OWB "Paris" will make the handling of table functions a bit easier, as you'll be able to define them within the tool itself rather than using TOAD or SQL*Plus.

There you go Jonathan!