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