Oracle Data Integrator Tools:OdiSqlUnload, OdiFileCopy ,OdiZip

Hi friends!

Oracle Data Integrator includes a number of tools that have their own specific function. The OdiSqlUnload tool is mainly used for unloading as well as generating a data file based on the SQL query provided for a specific data server connection. The file is written to the path defined by parameter -FILE.

Through this post, I will explain how to configure and use the OdiSqlUnload tool inside a Procedure.

Pre-requisites: Oracle Data Integrator 11g (build version 11.1.1.7.0)

Step 1: Create a new Procedure

Create a new procedure inside any of your existing Projects namedtest_sqlunload. Click on Details and then add a new entry as follows!

Select Target Technology as ODI Tools. The -PASS field contains the encoded password for your schema. You can encode the password using the encode tool present in the [ODI_HOME]\oracledi\agent\bin folder.

Usage: encode your-schema-password

Command

OdiSqlUnload "-FILE=D:\programs\sample2.csv" "-DRIVER=oracle.jdbc.OracleDriver" "-URL=jdbc:oracle:thin:@localhost:1521:orcl" "-USER=system" "-PASS=aIya5uUlQXGMLmIzPj.f" "-FIELD_SEP=;" "-DATE_FORMAT=dd/MM/yyyy hh:mm:ss" "-QUERY=select empid, empname, city from system.karan_employee"

Step 2: Run the Procedure

Once you run the procedure, navigate to the file path that you have mentioned as a part of your command. You should see a .csv file containing the results of the query.

Output

---------------------------------------------------------------------------------------------------

I will talk about yet another simple and user friendly ODI tool calledOdiFileCopy.

The OdiFileCopy tool copies files or a folder into files or a folder on the machine of the execution agent. One can also overwrite and detect case sensitive files while copying.

Usage of OdiFileCopy tool

OdiFileCopy -DIR=<dir> -TODIR=<dest_dir> [-OVERWRITE=<yes|no>] [-RECURSE=<yes|no>] [-CASESENS=<yes|no>] OdiFileCopy -FILE=<file> -TOFILE=<dest_file>|-TODIR=<dest_dir> [-OVERWRITE=<yes|no>] [-RECURSE=<yes|no>] [-CASESENS=<yes|no>]

Pre-requisites: Oracle Data Integrator 11g (build version 11.1.1.7.0)

Through this post, I will copy a text file from one location to another. Create a new text file named transfer1.txt and add the following content!

D:\programs\transfer1.txt

"I have been transferred using OdiFileCopy tool"

Now,using OdiFileCopy tool I will copy this text file to location D:\odi_workand name the file as transferred.txt.

Now, let’s put this tool to practice with a short demo. Open ODI Studio. Create a new package named odifilecopy_demo and drag the OdiFileCopytool in the vacant area. Connect it to the OdiBeep tool using an arrow.

Command

OdiFileCopy "-FILE=D:\programs\transfer1.txt" "-TOFILE=D:\odi_work\transferred.txt" "-RECURSE=YES" "-OVERWRITE=YES" "-CASESENS=NO"

Note: In order to transfer multiple files of same type (for example: .csv), you can use the following command.

Copy all *.csv files from source directory to target and overwrite.

OdiFileCopy -FILE=D:\your_source_directory\*.csv -TODIR=D:\your_target_directory -OVERWRITE=yes

Run the package. Check the status of the operation under the Operatortab. If the operation was successful, you will hear a sound due to theOdiBeep tool and your file will be copied to the specified location!

--------------------------------------------------------------------------------------------

As the name suggests, the OdiZip tool is used to create ZIP files from a directory or several files.

Usage of OdiZip

OdiZip -DIR=<dir> -FILE=<file> -TOFILE=<dest_file> [-OVERWRITE=<yes|no>] [-RECURSE=<yes|no>] [-CASESENS=<yes|no>] [-ENCODING=<file_name_encoding>]

OdiZip is useful when you have files of smaller size because the zipping operation is done by an Agent and the traffic between the agent and the target directory can cause a lot of problems.

Pre-requisites: Oracle Data Integrator 11g (build version 11.1.1.7.0)

Now, let’s put this tool into practice using ODI. Open ODI Studio and create a new Package named odizip_demo in any of your existingProjects.

Drag the OdiZip tool in the vacant area. Connect it to the OdiBeep tool using an arrow.

Command

OdiZip "-DIR=D:\odi_work" "-FILE=*.*" "-TOFILE=D:\odi_work\odi_work.zip" "-OVERWRITE=YES" "-RECURSE=YES" "-CASESENS=NO"

Finally, run the package. Check the status of the operation under theOperator tab. If the operation was successful, you should see your zip file in the target directory!

-----------------------------------------------------------