Before You Begin
This tutorial walks you through the steps that are needed to use Oracle Data Integrator Studio (ODI Studio) graphical tools to export an ASCII flat file of columnar data to an Oracle Database 12c relational table.
Time to Complete
Approximately 1 hour.
There are many ways to get a flat file of data into an Oracle Database. For example, SQL*Loader is one of the proprietary tools included in many versions of Oracle Database. The problem is that other versions of other databases (such as IBM DB2, or Microsoft SQL Server) use different proprietary tools. It would be attractive to have one standard way of importing files into many different databases. ODI can do just that. Furthermore, SQL*Loader is designed to take the data in pretty much unmodified, whereas ODI is designed to transform the data while it is being loaded. (Ignore for the moment exactly where and when the transformation is taking place.)
This is the second in a series of four OBEs in the Oracle Data Integrator 12c: Getting Started series.
Linda works as a database administrator for Example Enterprise Corp (http://www.example.com). In Example Enterprise, Linda is responsible for performing database management and integration tasks on the various resources within the organization. In particular, Linda is responsible for data loading, transformation, and validation.
To begin working on her projects (such as exporting a flat file to a relational table), she needs to create the new Master repository and Work repository. This was already done in a previous OBE. Following that, she will make a flat file to relational table project (this OBE). Following that, she will make an agent. These other tasks are covered in later OBEs.
Hardware and Software Requirements
The following is a list of software requirements:
- A supported operating system (the OS shown here is 64-bit Oracle Enterprise Linux release 6.7 (Santiago), other versions such as Windows are supported as well)
- Oracle Database (the version shown here is Enterprise Edition 220.127.116.11.0, other versions are supported as well)
- Oracle Data Integrator 12c (the version shown here is 12.2.1)
The following is a list of OPTIONAL software:
- Oracle WebLogic Server (WLS) 12c (other versions are supported as well)
- Oracle SQL Developer 12c (included with Database 12c install)
If WLS is present, it must be in the same
HOME directories as ODI.
Before starting this tutorial, you should have:
- Started Oracle Database services and components such as the Listener.
- A Database user with DBA role privileges such
- Installed the
HRschema included in the Oracle Database. The Sample Schemas Installation Guide is part of the Oracle Database documentation set, and is also available online at: http://otn.oracle.com.
- Already installed and configured ODI Master and Work repositories (instructions on how to do this are contained in the first of four OBEs in the Getting Started series).
1. Preparing the Source Flat File
The source and target definitions are very similar. You will use the ODI Studio GUI to create the models to represent these objects.
Many of the tasks for the GUI have directional terms, such as, "On the left..." or "On the right..." This presumes the default panel layout. You have the option to undock and relocate the panels, and also have the option to return the panels to their default layout by clicking Main Menu Bar > Window > Reset Windows to Factory Settings.
To prepare a flat file to be the source for an export to a relational table target, perform the following steps:
1.1 Starting ODI Studio
In a terminal session, navigate to
In this example, that is
Start ODI Studio by running script odi.sh.
[myuser@myhost ~]$ cd $ODI_HOME/studio [myuser@myhost studio]$ pwd /u01/app/oracle/Middleware/Oracle_Home/odi/studio [myuser@myhost studio]$ ./odi.sh Oracle Fusion Data Integrator Studio 12c Copyright (c) 1997, 2015, Oracle and/or its affiliates. All rights reserved.
This console window must remain open (but can be minimized) while Studio is running. The splash screen and progress bar displays.
In summary, you have sucessfully started ODI Studio.
1.2 Create Source Text File
Run the utility
on the file to make sure that the line
endings are DOS-style (carriage-return +
line-feed) as opposed to UNIX-style (just
[myuser@myhost ~]$ cd /u01/app/oracle/Middleware/Oracle_Home/odi/demo/file/ [myuser@myhost file]$ cp ~/continents.txt . [myuser@myhost file]$ ll total 16 -rw-r--r-- 1 oracle oinstall 247 Jul 29 11:00 continents.txt -rw-r----- 1 oracle oinstall 153 Jul 11 04:37 SRC_AGE_GROUP.txt -rw-r----- 1 oracle oinstall 2128 Jul 11 14:37 SRC_SALES_PERSON.txt [myuser@myhost file]$ unix2dos continents.txt unix2dos: converting file continents.txt to DOS format ... [myuser@myhost file]$
If you don't do this, the symptom is that the file will only import one line (the first row) and then stop.
In summary, you created the source text file.
1.3 Defining Source Topology Physical Architecture
In summary, you have configured an existing Physical Architecture File Technology to point to your host. You can have multiple physical hosts.
1.4 Defining Source Topology Logical Architecture
In summary, you have created a logical schema in the context of a physical schema.
1.5 Defining Source Designer Model
In summary, you created a Designer Model to represent the source text file.
2. Preparing the Target Table
This is very similar to the source definitions you just did.
To prepare a relational table in an Oracle Database to be the target of an export, perform the following steps:
2.1 Creating the SQL Table Definition
Does your database have the sample
schemas installed? Is the
schema unlocked? If so, SKIP THE REST
OF THIS STEP AND GO TO STEP 2.1.2.
If not, then create the
sample schema by signing on to
with DBA privileges using an account such
[myuser@myhost ~]$ sqlplus / as sysdba SQL*Plus: Release 18.104.22.168.0 Production on Mon Jul 29 13:27:03 2015 Copyright (c) 1982, 2014, Oracle. All rights reserved. Last Successful login time: Fri Jul 26 2015 15:02:23 -04:00 Connected to: Oracle Database 12c Enterprise Edition Release 22.214.171.124.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> CREATE USER hr IDENTIFIED BY hr ACCOUNT UNLOCK; User created. SQL> GRANT CONNECT, RESOURCE, CREATE TRIGGER, CREATE VIEW TO hr; Granted. SQL> exit [myuser@myhost ~]$
When you are done creating the sample
HR, exit out of the DBA
account (just to be safe).
Sign on to
sqlplus as user
HR and create the empty
[myuser@myhost ~]$ sqlplus hr/hr SQL*Plus: Release 126.96.36.199.0 Production on Mon Jul 29 13:27:03 2015 Copyright (c) 1982, 2014, Oracle. All rights reserved. Last Successful login time: Fri Jul 26 2015 15:02:23 -04:00 Connected to: Oracle Database 12c Enterprise Edition Release 188.8.131.52.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> DROP TABLE hr.continents; Table dropped. SQL> CREATE TABLE hr.continents ( abbr VARCHAR2(4) , name VARCHAR2(25) , land_km NUMBER(12) , CONSTRAINT tab_contin_pk PRIMARY KEY(abbr) ); SQL> DESC hr.continents; Name Null? Type -------------- -------- ------------ ABBR NOT NULL VARCHAR2(4) NAME VARCHAR2(25) LAND_KM NUMBER(12) SQL> SELECT * FROM hr.continents; no rows selected SQL>
If a table does not exist at the moment, attempting to drop a non-existent table will give an error. You can safely ignore the error.
In summary, you now have a sample user
and an empty sample target relational table
2.2 Defining Target Topology Physical Architecture
In summary, you created an ODI physical
schema associated with the Oracle Database
2.3 Defining Target Topology Logical Architecture
In summary, you created an ODI logical
schema to match the ODI physical schema
which corresponds to the actual Oracle
2.4 Defining Target Designer Model
In summary, you created an ODI Designer Model to represent the target Oracle Database relational table.
3. Preparing the Mapping
In previous versions of ODI, mappings were called interfaces. The Map Editor is a GUI drag-n-drop tool.
To map the flat file source to the relational table target, perform the following steps:
3.1 Creating the Designer Project
In summary, you created a project to hold the knowledge modules and maps.
3.2 Importing the Knowledge Modules (OPTIONAL)
There are global knowledge modules and local knowledge modules (KM, not to be confused with land area kilometers in the tables). Import the appropriate local KMs into this project. Some KMs are pre-seeded and do not need to be imported. This particular OBE does not need any additional KMs, but three are imported optionally to show you how it is done. "Extra" (unused) KMs take up disk space but don't cause any harm.
In summary, you have imported the Loading and Integration KMs that could be used for some more sophisticated project. This particular project uses the pre-seeded KMs, therefore this whole step was optional. You can see one of the built-in (pre-seeded) LKMs mentioned on the screenshot for 4.2.2.
3.3 Creating the Mapping
In summary, you mapped the source model to the target model and provided a column-by-column mapping, some of it automatically, and some of it manually.
4. Executing the Mapping
Mappings can be scheduled to run based on the calendar/clock, or can be run on demand.
To run the mapping now, perform the following steps:
4.1 Executing the Mapping Itself
In summary, you ran a mapping using one of three methods.
4.2 Checking the Operator Tab
In summary, the mapping ran successfully with one warning that you can ignore.
4.3 Checking the Target Table
Instead of viewing the data from inside of ODI, view it using SQL*Plus or SQL Developer.
SQL> l 1* SELECT * FROM hr.continents SQL> r 1* SELECT * FROM hr.continents ABBR NAME LAND_KM ---- ---------------------- ----------- AA Antartica 13209000 NA North America 24256000 SA South America 17819000 EU Europe 9938000 AS Asia 44579000 AU Australia 7687000 AF Africa 30065000 7 rows selected. SQL>
The window may have been left open from earlier task 2.1.2. Recall that the first time you did this it was blank.
In summary, you have successfully imported data from a flat file to an Oracle Database relational table and displayed that table both inside and outside of ODI Studio.
Want to Learn More?
In this tutorial, you should have learned how to:
- Prepare flat files to be the source of an export
- Prepare relational tables to be the target of an export
- Use ODI Studio Designer and Topology to make Physical and Logical Schemas, Models, and Projects
- Map source objects to target objects using the drag-and-drop tool
- Create and execute projects, and evaluate their status using the Operator tool
- View data in datastore objects using ODI tools and external tools such as SQL*Plus
The following are conceptual or procedural Help topics relevant to the topic of this tutorial:
- Online documentation, viewlets, samples, and OLN URLs on OTN:
- The following specific courses:
Oracle Data Integrator 12c: Integration
(replaces D64974GC20 Oracle Data Integrator 11g: Integration and Administration Ed2)
- D82171GC10 Oracle Data Integrator 12c: New Features
- External Web sites for related information:
- To learn more about Oracle Data Integrator 12c, refer to additional OBEs in the Oracle Learning Library, or on the ODI Studio Start Page.
- Lead Curriculum Developer: Elio Bonazzi
- Other Contributors: Viktor Tchemodanov, Alex Kotopoulis, Julien Testut