Dataflow in Oracle Warehouse Builder

By | November 22, 2016

I know that Oracle Warehouse Builder (OWB) is at end of life. On the other hand, I encounter OWB quite often and it is interesting to see how it works.
So investigate how it works, I created a dataflow. It it a trivial one: it consists of a file that must be read into Oracle.
The first step is to create a file and assign it to OWB. The file is quite simple. I created this flow:


The metadata on this file is added to OWB. One must have a so-called location that provides data where the file can be found. From that location, the metadata on the file can be imported. In that process, the screen looks like:
A file is imported via the construct of an external file. This is an object in the database that allows to read a flat file from within the Oracle database.
The external file is created from within Oracle Warehouse builder. This must be stored within the metadata on the database. After that code can be generated and deployed on the database.
If it is deployed on the database, one may verify its existence of that table with, say, sql developer. I realise the code and its deployment is steered from OWB.
Another important element is the target file. Let us assume, we have a target file in the database. The metadata on that table can be imported. If that is done, one may see in OWB:
The sources and the targets are now known. These are the key elements of a mapping. The mapping in this case is almost trivial.
If the mapping is created, it can be generated as an PLSQL script. This can be deployed on Oracle. This package can be run from any client tool (SQL Developer, SQL Plus, OWB). This package is like any other package that you created yourself.