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!