How to Export Data to a Flat File with ODI

1.    How to Export Data to a Flat File

Please read the reference first to know how to export data to flat file with interface.

There is another way to export data to file with OdiSqlunload.

1.1    Main Steps

• Create package with ODISqlunload
• Create ODI SP with execute ODISqlunload command

1.1.1    Create package with ODISqlunload

More details about parameters are in Developer’s Guide for Oracle Data Integrator 11g.pdf

P385 A.5.44 OdiSqlUnload

1.1.2    Create ODI SP with execute ODISqlunload command

Create ODI procedure.

 

Create command step.

 

Define Command on Source tab.

 

Define Command on Target tab.

 

OdiSqlUnload "-FILE=#sFileExportPath\\UserInfo.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=\r\n" "-DATE_FORMAT=yyyy/MM/dd HH:mm:ss" "-CHARSET_ENCODING=ISO8859_1" "-XML_CHARSET_ENCODING=UTF-8" "-FETCH_SIZE=5000"

select  'UserID',

        'UserName',

        'UserEmail'

from dual

union all

SELECT

    '"' || UserID || '"' as "UserID",

    '"' || UserName || '"'  as "UserName",

    '"' || UserEmail || '"'  as "UserEmail"

FROM User_Info

Where UserID >100

1.2    Reference

ODI11g: Creating an ODI Project and Interface: Exporting an RDBMS Table to a Flat File

http://www.oracle.com/webfolder/technetwork/tutorials/obe/fmw/odi/odi_11g/odi_project_table-to-flatfile/odi_project_table-to-flatfile.htm

ODI11g: Creating an ODI Project and Interface: Exporting a Flat File to a Flat File

http://www.oracle.com/webfolder/technetwork/tutorials/obe/fmw/odi/odi_11g/odi_project_ff-to-ff/odi_project_flatfile-to-flatfile.htm

 

How to Send Mail with ODI

1.    How to Send Mail

1.1    Main Steps

• Send Mail with ODI Tool: OdiSendMail
• Send Multiple Mails with ODI SP

1.1.1    Send Mail with ODI Tools

Parameters descriptions are in the document as below.

Developer’s Guide for Oracle Data Integrator 11g.pdf     P379 A.5.40 OdiSendMail

Send mail in ODI SP with below command.

1.1.2    Send Multiple Mails with ODI SP

1.1.2.1    Generate attachment

1. Define command on source

C1_USER_ID, C1_USER_NAME and C1_USER_EMAIL are the parameters which can be transferred to command on target.

2. Define command on target
   

OdiSqlUnload "-FILE=#sFileExportPath\\ProductList_#C1_USER_ID.csv" "-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=\r\n" "-DATE_FORMAT=yyyy/MM/dd HH:mm:ss" "-CHARSET_ENCODING=ISO8859_1" "-XML_CHARSET_ENCODING=UTF-8" "-FETCH_SIZE=5000"

Select 'Product Code', 'Product Name' from dual

Union all

select

  Product_Code,

  Product_Name

from Product_Info

Where Seller_ID = '#C1_USER_ID'

#sFileExportPath: ODI variable

#C1_USER_ID: variable defined in command on source

Below are the parameters which can be get from command on source tab.

"-DRIVER=<%=odiRef.getInfo( "SRC_JAVA_DRIVER" )%>" "-URL=<%=odiRef.getInfo( "SRC_JAVA_URL" )%>" "-USER=<%=odiRef.getInfo( "SRC_USER_NAME" )%>" "-PASS=<%=odiRef.getInfo( "SRC_ENCODED_PASS" )%>"

1.1.2.2    Send multiple mail

1. Define command on source
   

2. Define command on target
   

OdiSendMail "-MAILHOST=#sAdminMailHost" "-FROM=#sAdminFromEmail" "-SUBJECT=Slice data calculated OK!" "-TO=#C1_USER_EMAIL"  "-CC=#sAdminToEmail"  "-ATTACH=#sFileExportPath\\ProductList_#C1_USER_ID.csv"

Hi #C1_USER_NAME,

STG processes finished successfully. Some data was updated.

More details are in the attachment. The files can be opened with Microsoft Excel.

Have a nice day.

STG ODI

#C1_USER_ID and #C1_USER_NAME are the parameters defined in command on source.

#sAdminMailHost, #sAdminFromEmail and #sFileExportPath are ODI variables.

1.2    Reference

Developer’s Guide for Oracle Data Integrator 11g.pdf

P186 Example 12–2 Sending Multiple Emails

P379 A.5.40 OdiSendMail

Send mail in ODI using Gmail Credentials

http://dwteam.in/send-mail-in-odi-using-gmail-credentials/

How to Call Oracle Procedure with ODI

1.    How to Call Oracle Procedure

1.1    Main Steps

• Create procedure A in Oracle
• Create procedure in ODI to call A

1.1.1    Create Procedure A in Oracle

Create a procedure in Oracle with input and output parameters as below.

 

1.1.2    Create procedure in ODI to Call A

Create an ODI store procedure

 

Create a step to call Oracle store procedure

 

Can set input parameter with ODI variable as v_ProductCode := :sProductCode ( sProductCode is ODI variable)

1.2    Reference

How to use PL/SQL procedures and functions in ODI

http://odiexperts.com/how-to-use-plsql-procedures-and-functions-in-odi/

How to Load Data from Excel with ODI

1.    How to Load Data from Excel

1.1    Main Steps

• Define a name in Excel
• Define an ODBC data source
• Create a data server in Topology Manager
• Create a data model in Designer
• Create an interface

1.1.1    Define a Name in Excel

Select the data you are interested in and go to Formulas tab to define a name.

 

Rename the name and add comment as below.

Define the cell format before define a name. This cell format will affect the data type when creating data model with “Reverse Engineer”.

            Number->Number

            Text->Varchar(255)

 

 

1.1.2    Define an ODBC Data Source

1.1.3    Create Data Server in Topology Manager

 

Rename jdbc:odbc:<odbc_dsn_alias> to jdbc:odbc:ODIExcel

Click “Test Connection” to test

Create physical schema

Create logical schema

 

1.1.4    Create a data model in Designer

Click “Reverse Engineer” to reverse the model structure

 

Double click “PRODUCT_INFO” view the data model

 

Update excel cell format to Text

 

Delete the model and recreate again. The data type will be reversed as VARCHAR(255)

  

View data to confirm.

There are some data with null value from line 6 to 10. We can filter these data when we create interface.

1.1.5    Create an interface

 

Define the filter condition execute on “Staging Area”.

 

Run the interface and check log

 

1.2    Reference

Using Microsoft Excel as a Source and a Target in Oracle Data Integrator

https://blogs.oracle.com/dataintegration/entry/using_microsoft_excel_as_a_sou

Connecting to Microsoft Excel using Oracle Data Integrator

https://blogs.oracle.com/dataintegration/entry/connecting_to_microsoft_excel

Microsoft Excel as a Source and Target as Oracle in ODI 11.1.1.6

http://dwteam.in/microsoft-excel-as-a-source-and-a-target-as-oracle-in-odi-11-1-1-6/