Load text file into oracle table
To create Interface,right click on Interface---->Insert Interface.Give name to it as shown below.
Click on Diagram tab. On diagram tab you have to design/implement your logic.
In SYSTEM schma create HSP_TEXT_CELL_VALUE table.
Give name to Model and select schema in which you have created HSP_TEXT_CELL_VALUE table.
On Reverse tab,select Context as a Global.
Similarly,create model for textvalues.txt. Here is a help,
Give name to model,Select File technology, select File logical schema.click OK
Double click on interface that you have created. Go to Diagram tab.
Congratulation,your text file is loaded into table.
To load data from text to oracle table we have to create new project. Inside project ,we have to design program which will do actual text to table loading. This all activity will be done inside Designer
To open designer,
Start menu-----> All Programme---->Oracle------>Oracle data integrator---->Designer
Create new project as shown below,
Give appropriate name to project, and click OK.
Now,if you will observe,Project tree will be having First Folder which have Package,Interface,Procedure.
Click on Diagram tab. On diagram tab you have to design/implement your logic.
Defination 1: Load text data to table,Lets say that Oracle table has a 2 column.
Following is textvalues.txt files at oracledi\demo\file.
In SYSTEM schma create HSP_TEXT_CELL_VALUE table.
CREATE TABLE HSP_TEXT_CELL_VALUE (TEXT_ID NUMBER(3),VALUE VARCHAR2(20));
To represent Table and Text file in ODI,We need to create Model in ODI.
So,Model is a Meta data(All information about table/Text,column name,length,....etc).
We need to create model for HSP_TEXT_CELL_VALUE table and Textvalues.txt file.
Before we create model,We have to create physical and logical architecture for oracle and file technology.
Lets say for SYSTEM schema, we created Oracle_10G_XE as a physical and logical architecture in oracle and FILE_DEMO_SRC for file.
Lets say for SYSTEM schema, we created Oracle_10G_XE as a physical and logical architecture in oracle and FILE_DEMO_SRC for file.
To create Model, click on Model on in designer.
Give name to Model and select schema in which you have created HSP_TEXT_CELL_VALUE table.
On Reverse tab,select Context as a Global.
Now,click on Selective Reverse tab,It will disply all table that are exist in SYSTEM schema.
select HSP_TEXT_CELL_VALUE table and click Reverse.
select HSP_TEXT_CELL_VALUE table and click Reverse.
Then it should shown in Model tree as shown below,
Similarly,create model for textvalues.txt. Here is a help,
Give name to model,Select File technology, select File logical schema.click OK
Now,follow step as shown below.
On Defination tab,
On Files tab,
On Columns tab,
Click OK.
Now,we have model for both table as well as text file.
Double click on interface that you have created. Go to Diagram tab.
Drag HSP_TEXT_CELL_VALUE table model on Target Data source and,
textvalues.txt files model on Source as shown below.
If,it will ask for automatic mapping click yes otherwise map it manually.
This mappings describe that on target column what should be insert.
Manual mapping is also drag and drop process.
Click on Flow tab,for source select LKM File to SQL lkm.
For Target data source select IKM Oracle Incremental Update, as shown below.
On Diagram tab,click on Execute button and check it in Operator to know success/failure of your interface.
Congratulation,your text file is loaded into table.
