Saturday, October 01, 2016

OdiSqlUnload and the Possibilities

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=<%=odiRef.getSchemaName("DIRECTORY_FILEOUT", "D")%>/Output.txt" "-DRIVER=<%=odiRef.getInfo("SRC_JAVA_DRIVER")%>" "-URL=<%=odiRef.getInfo("SRC_JAVA_URL")%>" "-USER=<%=odiRef.getInfo("SRC_USER_NAME")%>" "-PASS=<%=odiRef.getInfo("SRC_ENCODED_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 <<column1>>, <<column2>>, <<column3>> from <<schema_name>>.<<table_name>>

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
Can you think of any more use of this cool tool?


Post a Comment

“Don't part with your illusions. When they are gone you may still exist, but you have ceased to live.” ~ Mark Twain