Posted in Analytics

OdiSqlUnload and the Possibilities

   Published Date: October 1, 2016  Leave a Comment on OdiSqlUnload and the Possibilities
woman working at home and making video call on laptop

The Oracle Data Integrator is one of the best futuristic data transformation and loading tools with a variety of features for the end-user. ODI as an ELT tool provides a lot of inbuilt features for data processing using different technologies.

One of the most useful I find is the ODI tool OdiSqlUnload, which can be used in multiple ways in different scenarios for different purposes. To use this feature, create a new ODI procedure and in the Source tab select technology as "ODI Tools". Then using the below syntax we can create files with the listed customization options:

  • Dynamic output file path
  • Dynamically fetched schema username
  • Dynamically fetched schema password
  • File content is decided by the SQL query (the logical schema for the SQL to be executed in needs to be set in the Target tab of the ODI procedure)

OdiSqlUnload "-FILE=/Output.txt" "-DRIVER=" "-URL=" "-USER=" "-PASS=" "-FILE_FORMAT=VARIABLE" "-FIELD_SEP=" "-ROW_SEP=" "-DATE_FORMAT=yyyy/MM/dd HH:mm:ss" "-CHARSET_ENCODING=ISO8859_1" "-XML_CHARSET_ENCODING=ISO-8859-1"
select <>, <>, <> from <>.<>

OdiSqlUnload and the Possibilities
OdiSqlUnload and the Possibilities

Now that I am able to successfully generate the ODI generated files, let's look at the different ways this can be leveraged to cater to various scenarios:

  • Create and attach files to emails via the OdiSendMail tool
  • Generate error logs for ODI Load Plans and notify administrators
  • Create multiple files with different delimiter formats and data content in a single procedure by adding multiple steps
  • Create files in a single path using data from different physical sources using different logical schemas in different steps in a single procedure

How do you utilize this feature in your environment? Do you need help implement a new design? Comment or message me to get started.


× Kindly subscribe

Thanks for reading
Insight Crunch.

Create your account or
log in to continue reading.

  • Join thousands of subscribers
  • Get instant access to this article
  • Silicon Valley latest events coverage
  • Unlimited access to Premium Articles
  • Wide coverage of industry news
  • Mock Tests for Strategic Preparation
  • 3000+ problems on variety of topics
  • Real-time feedback in every question

Support independent journalism.
Already a member? Login.

Author: Rahul Bhattacharya

Rahul is a journalist with expertise in researching a variety of topics and writing engaging contents. He is also a data analyst and an expert in visualizing business scenarios using data science. Rahul is skilled in a number of programming languages and data analysis tools. When he is not busy writing, Rahul can be found somewhere in the Appalachian trails or in an ethnic restaurant in Chicago.

Leave a Reply