ODI 12c OdiSqlUnload Called Via LKM Step or in a Procedure Using Unicode -XFIELD_SEP as Option Loses Its Syntax (Doc ID 2374783.1)

Last updated on JUNE 16, 2023

Applies to:

Symptoms

In Oracle Data Integrator (ODI) 12c, when attempting to use the OdiSqlUnload tool in a Procedure with the field separator unicode value (\u0001) for <Ctrl+A> defined as an option...

... the resulting file does not have the \u0001 delimiter.

The unicode syntax for the value is not maintained, which results in no field separator being present in the resultant text file.

The behavior does not produce if the value is "hardcoded" in the task.

The issue can be reproduced with the following steps:

1. Set up the ODI Topology to use an Oracle Data Server, and any table from the corresponding database schema.
2. Create Model (reverse engineer the source table created or identified in step 1).
3. Set up the ODI Topology to use a flat File Data Server. Note the destination path should be defined to a location which can be written by the ODI user.
4. In ODI Studio > Designer, create an one step Procedure to generate the file using an option substitution for -XFIELD_SEP to bring in the value ("-XFIELD_SEP=<%=odiRef.getOption("DELIMITER")%>")
5. Create the DELIMITER option with value \u0001
6. Save and run the Procedure.
7. Observe the file created is not delimited with the CTRL+A symbol.

Note the same behavior also occurs when OdiSqlUnload is used in a Loading Knowledge Module (LKM) step. (Please note: LKM modifications are considered customizations and as such are not supported)

For this case, even **hard-coding** the \u0001 value as part of the OdiSqlUnload command (rather than as an option) fails:

1. Copy a SQL LKM which is to be used in the Mapping to load from source Oracle table to the text File.
2. Insert a step to run OdiSqlUnloadwith the -XFIELD_SEP defined as \u0001 ("-XFIELD_SEP=\u0001")
3. Run the Mapping to review the resulting file from the added step.
4. Observe the resulting file does not have <Ctrl+A> file separator.

Changes

Cause