Kavyas Tutorials: September 2020

Wednesday, September 09, 2020

load data from table to file using free hand sql (OdisqlUnload) in odi 11g\odi 12c

Advantage:

Ø OdiSqlUnload Tool is built in tool in odi 11g, used in procedures only.

Ø using OdiSqlUnload Tool, Load the data from Oracle table to Flat file , Excel, Xml , Pdf

Ø Generates a data file by executing the SQL query <sql_query> on the data server whose connection parameters are provided by <driver>, <url>, <user> and <encoded_pass>. The file is written to the path defined by <filename> (relative to the agent).

Command:

OdiSqlUnload -FILE=<file_name> -DRIVER=<driver> -URL=<url> -USER=<user> -PASS=<password> [-FILE_FORMAT=<file_format>] [-FIELD_SEP=<field_sep> | -XFIELD_SEP=<field_sep>] [-ROW_SEP=<row_sep> | -XROW_SEP=<row_sep>] [-DATE_FORMAT=<date_format>] [-ABS=<yes|no>] [-CHARSET_ENCODING=<encoding>] [-XML_CHARSET_ENCODING=<encoding>] [-FETCH_SIZE=<array_fetch_size>] [CR/LF <sql_query> | -QUERY=<sql_query> | -QUERY_FILE=<sql_query_file> ]

Create a Project to create procedure:

Go to Designer Navigator. Select new project & enter the name for project

Create a Procedure for load the data from Oracle DB to Flat File:

Step: 1) Go to Designer, click the Projects tab. Expand your project WORK_ON_LOAD ORCL DB TO FLAT FILE, and then expand First Folder. Right-click procedure and selects new procedure.

Step: 2) Select the F details tab. Click the + add symbol for command to write a SQL Query.

Step: 3) I have enter command in following screen shot. Here,

Source: File

FILE=D:\TEXT\Test data of procedure.txt

Target: Oracle

JDBC DRIVER: oracle.jdbc.OracleDriver

JDBC URL=jdbc:oracle:thin:@190.160.0.10:1521:DEVDWH

USER=DEVDWH

QUERY: select * from DEVDWH.DWT_SECTOR,DEVDWH.DWT_INDUSTRY

Note: Source file is not only text file (.txt) but also .PDF, .CSV, .doc, .xls extenstions.

OdiSqlUnload "-FILE=D:\TEXT\Test data of procedure.txt" "-DRIVER=oracle.jdbc.OracleDriver" "-URL=jdbc:oracle:thin:@190.160.0.10:1521:DEVDWH" "-USER=DEVDWH" "-PASS=hpfHiT7Ql0Hd79KUseSWYAVIA" "-FILE_FORMAT=VARIABLE" "-FIELD_SEP=," "-ROW_SEP=\r\n" "-DATE_FORMAT=yyyy/MM/dd HH:mm:ss" "-CHARSET_ENCODING=ISO8859_1" "-XML_CHARSET_ENCODING=ISO-8859-1"

select * from DEVDWH.DWT_SECTOR,DEVDWH.DWT_INDUSTRY

Step: 4) check the TEXT folder (source file), this is empty folder.

Step: 5) To test your project click Execute icon (Green play button

). Then following screen appears.

Step: 6) To verify that your interface was executed successfully, you need to open ODI Operator Navigator. Click the ODI Operator tab. In ODI Operator, click Session List. Expand Sessions > FLAT TO Oracle, and view the execution results for each step. Double-click any step and click the Description and Execution tabs to see the execution results. Double-click step 1. View the number of rows that are inserted into the target flat file. Click OK. Close the tabs.