Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 








String Parsing, Table Functions and OWB10gR1
March 1, 2006
Mark Rittman

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;

---------- ------------------------------
         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;

---------- --------------------
         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);
 12  begin
 14    loop
 16    fetch staff_teams_cur into v_staff_id, v_team_members;
 17    exit when staff_teams_cur%notfound;
 19      dbms_utility.comma_to_table(v_team_members, cnt, my_table);
 21   for i in 1 .. my_table.count loop
 23   if my_table(i) is not null then
 25    pipe row (staff_member_breakdown_record(
 26     to_number(v_staff_id), my_table(i)));
 28   end if;
 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)));

---------- --------------------
         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;

---------- --------------------
         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!


Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational