ODI - How to read fixed length file and insert to db

Steps:

• Under Topology, 
• Choose file technology
• Create new data server or use existing one
•  Under data server, create new physical server. Provide file directory 
• Add context to logical schema.
• Under Design, create a file model or use existing one
• Create a new data store
• Provide name, choose sample resource fixed length file
• Under files, privide format as fixed and record separator as unix or ms dos as required.
• Under attributes, reverse engineer. It will open Atribute setup wizard.
• Now we can select the length of each field here just by clicking on the above and also see the field details in the side view.
• Once done. Press ok and it will show all the columns and slect the data type.
• Under projects, create a new map 
• Drag and drop file and db data stores and wire or map all the fields
• Go to physical and select LKM as LKM file to SQL and IKM as IKM Oracle control append or insert.
• Save and run with global context. Go to operator tab and check the session 
• Also verify from db table.

Steps with screenshots:

OIC - How to create fixed length file

Here We will show you how to create a fixed length file in OIC implementation.

Used fixed length schema:

You can go following my blog to know how to create fixed length file:

https://soalicious.blogspot.com/2022/02/xsd-how-to-create-fixed-length-schema.html

Steps with screenshots:

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):