XSD - How to create fixed length schema file

About Fixed Length file:

• Fixed length files have a fixed length for each field and record.
• Use Jdeveloper tool to define an adapter (file, ftp, aq) then you get to click on the 'define native format' button. Then choose 'Fixed Length' and follow the wizard.
• The actual field data may be less than the length specified. In this case, you can specify paddedBy and padStyle as head or tail or none. when the data is read, the pads are trimmed accordingly.

Created sample fixed length file:

<?xml version="1.0" encoding="UTF-8"?> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:nxsd="http://xmlns.oracle.com/pcbpel/nxsd" xmlns:tns="http://www.oracle.com/FTPAdapter" targetNamespace="http://www.oracle.com/FTPAdapter" elementFormDefault="qualified" attributeFormDefault="unqualified" nxsd:version="NXSD" nxsd:stream="chars" nxsd:encoding="US-ASCII">

<xsd:element name="Employees"> <xsd:complexType> 

<xsd:sequence>

<xsd:element name="Employee" nxsd:style="array" nxsd:cellSeparatedBy="${eol}" minOccurs="1" maxOccurs="unbounded"> <xsd:complexType> 

<xsd:sequence> 

<xsd:element name="FName" type="xsd:string" nxsd:style="fixedLength" nxsd:length="8" nxsd:padStyle="tail"/> 

<xsd:element name="LName" type="xsd:string" nxsd:style="fixedLength" nxsd:length="4"nxsd:padStyle="tail"/>

 <xsd:element name="Dept" type="xsd:string" nxsd:style="fixedLength" nxsd:length="4" nxsd:padStyle="tail"/> 

</xsd:sequence> 

</xsd:complexType> 

</xsd:element> 

</xsd:sequence>

</xsd:complexType>

</xsd:element> 

</xsd:schema>

Steps with Screenshots(Created in Jdeveloper):

ODI - Read local Flat csv file and insert to Oracle database

Steps:

• Go to Topology and under Physical Architecture, choose File technology and create new data server. 
• Provide dataserver name.
• Under created dataserver , create new physical schema 
• provide the name, directory path as directory(schema) and directory(work schema).
• Create a context and add the logical schema name
• Choose Technology as Oracle and create new data server.
• Provide data server name
• User and pwd 
• Choose jndi connection or use credential file. Here we chose use credential file and selected the path.
• Under created dataserver, create new physical schema
• Provide name
• Provide db user as schema,(schema and work schema)
• Context and add logical schema
• Go to Design tab and create a model folder 
• Create a model for file 
• Provide name, technology as File, choose created file logical schema and save
• Under the file model, create a datastore 
• Provide name, choose a sample test.csv file as resource name.
• Go to files and provide the format like delimited, no of headings, unix or ms dos os, field separator as ","  etc
• Go to attributes and reverse engineer, you can see all the file fields here.
• Under the create file datastore, view data
• Create a model for oracle db
• Provide name, choose technology as Oracle , logical schema
• Reverse engineer and provide mask , here %POC%
• Selective Reverse engineering and select all the required options and it will show your table and click on reverse engineer.
• Projects and create a new project and ceate a new folder and go to mappings and create a map
• Drag and drop created data stores and map all the fields
• Go to physical and choose LKM as LKM file to SQL and IKM as IKM Oracle insert or IKM Oracle control append. And flow control as false as we dont have any PK on datastore.
• Run the map and go to operator tab
• Open session list and date check all steps and also verify in table where rows inserted or not.

POC Screenshots:

ODI - IKM Oracle Incremental Update Vs IKM SQL Control Append

Yes for bulk/large load ODI recommends to use Control append. The basic functionally of Control append is the do a Insert into the target table by selecting from the source table/file. You can't do row by row checking in this KM.

Incremental Update : Based upon the PK defined in the target data store, this KM will check row by row and do insert/update (will capture changed data).

Control Append : Blindly do a bulk insert in to target table and no changed data will be captured. It wont do truncate and insert whereas to aviod duplicates in data, have a PK defined in target table so whenever duplicate data comes it can be prevented or go for CKMs. And temp table will be Created and Dropped after every load in both the KMs.