Static Control in ODI 12c

Scenario:
While loading 10000 records if there is an error in one/more records then I need to make a note that record into E$ (error table) and all records into target table.

Example:
Source

As per requirement, whose MID values are null consider to be invalid records. Though those are not valid records we are accepting it and providing to option to end-user to correct by themselves by providing error message

(i.e., SRC <= TRG+E$)

Static Control:

Data quality validation is done after loading the data into target tables.

CKM will validate data on target table and if any error is detected it will be inserted to E$ table and SNP_CHECK_TAB.

Remember that the incorrect entry will not be deleted as in Flow control. 

Approach:

Step 1:

Create Model -- Reverse Engineer source datastore and target datastore 

Step 2:

Expand Target data store -- Right click on constraint --Select  "New Condition"

Message is nothing error message

Actually this is condition is not there at data server level. Type indicates it is ODI condition.

Step 3:

Go to Control tab and enable check box for both flow control and static control

Step 4:

Create mapping -- Drag and drop Source and target data store then connect source output connector to target input connector -- go to physical tab and then select target datastore -- go to Property Inspector -- Integration Knowledge Module --  Enable Static control as true 

Step 5:

Execute it

Output:

Flow Control in ODI12c

Scenario:
While loading 10000 records if there is an error in one record then I need to load that record into E$ (error table) and rest of records into target table.

Example:
Source

As per requirement, whose MID values are null consider to be invalid records

(i.e., SRC = TRG+E$)

Flow Control:

Data quality validation is done before loading the data into target tables.

Check Control Knowledge Module (CKM) will create E$ table and SNP_CHECK_TAB table for data quality check.

It will validate data in I$ table before inserting data into target table.

If it has any errors then it will delete from I$ table and insert into E$ table and common error message and interface name into SNP_CHECK_TAB

Approach:

Step 1:

Create Model -- Reverse Engineer source datastore and target datastore 

Step 2:

Expand Target data store -- Right click on constraint --Select  "New Condition"

Message is nothing error message

Actually this is condition is not there at data server level. Type indicates it is ODI condition.

Step 3:

Go to Control tab and enable check box for both flow control and static control

Step 4:

Create mapping -- Drag and drop Source and target data store then connect source output connector to target input connector -- go to physical tab and then select target datastore -- go to Property Inspector -- Integration Knowledge Module --  Enable Flow control as true 

[*Note: There should be key if we enable flow control. In my example EMPID is key column]

Step 5:

Execute it

Output:

Unable to Open SQL Developer

Unable to find Java Virtual Machine. To point to a location of a Java Virtual Machine

Solution
The Oracle SQL developer is NOT supported on 64 bits JDK.
To solve it, install a 32 bits/x86 JDK and update your SQL developer config file,so that it points to the 32 bits JDK.

Edit the “sqldeveloper.conf“, which can be found under “{ORACLE_HOME}\sqldeveloper\sqldeveloper\bin\sqldeveloper.conf“, make sure “SetJavaHome” is pointing to your 32 bits JDK.
For example, “SetJavaHome C:\Program Files (x86) \Java\jdk1.6.0_13“ or
“SetJavaHome C:\Program Files (x86)\Java\jdk1.7.0"

If you update with JDK1.7.* version then again you may face following issue
msvcr100.dll is missing from your computer

Solution:
Go to jdk1.7.0 installed path jdk1.7.0\jre\bin copy msvcr100.dll and paste it into ORACLE_HOME\sqldeveloper\sqldeveloper\bin
Now it will start SQL Developer

Invoking Webservice Using ODI

Scenario:
We need to invoke web service and store response file in a specific location

Pre-Requisites:

WSDL URL : http://www.webservicex.com/globalweather.asmx?WSDL

XML Request : <env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns1="http://www.webserviceX.NET">
<env:Header/>
<env:Body>
<ns1:GetCitiesByCountry>
<ns1:CountryName>India</ns1:CountryName>
</ns1:GetCitiesByCountry>
</env:Body>
</env:Envelope>

Procedure:
Step 1:
Create Package "PKG_WEBSERVICE"

Step 2:
Drag and drop the ODIInvokeWebservice tool from Tool box into package

Step 3:
Provide properties as mention below

Output:
It will create appropriate response file in a specified location.
Completed Successfully!!!!

Create Oracle Data store on fly using File Data store without writing DDL script

Scenario:
We need to create a table with same structure as file Structure without writing DDL scripts in ODI 12c

Steps:

Step 1:
Go to Designer -- Go to Model Accordion -- Create new File Model -- right click and create new Datastore -- Go to Definition tab and select File -- Go to File and select File format type as "Delimited" , Heading as "1" , Field Separator -- Other as "," -- Go to Attributes and click Reverse Engineering button at left side top

Step 2:
Go to Designer -- Go to Model Accordion -- Create new Oracle Model by providing appropriate technology and Logical Schema and save it. Now COPY File Datastore from file model and paste that into Oracle Model.

Now double click on Oracle Datastore (CompanyHierarchy) --  Go to definition tab and change resource name as COMPANY_HIERARCHY (i.e., we are giving table name as COMPANY_HIERARCHY)

Step 3:

Create a new mapping -- Drag and drop FileDatastore as source and Oracle Datastore as Target

Go to Physical Tab and select appropriate KM's (LKM File to SQL and IKM Oracle Simple Insert -- Select option for Create Target Table as "true")

Step 4:

Execute mapping

Completed Successfully!!!

Java Beanshell and SQL Embedded Example in ODI

Scenario:
We need to get all the filenames inside a folder.
For Example:
Sample_Files (Folder)
      Sample_File.txt(File)
      Sample_File.csv(File)
      Sample_File.xls(File)

Output should be 'Sample_File.txt,Sample_File.csv,Sample_file.xls'

Steps:

Step1:
Create a procedure and select target technology as Oracle

Step2:
Copy below code and paste in target side and select technology as  Oracle and Logical Schema accordingly

<?
import java.io.File;
public class FileLists{
   public static String FileNames() {
     
      File f = null;
      String[] FileList;
   String fileNames="";
           
      try{     
        
         f = new File("<%=odiRef.getOption( "Dir" )%>");
         FileList = f.list();
         for(String FileName:FileList)
         {
           if(FileName.contains("."))
     {
   if (!fileNames.isEmpty())
    fileNames  = fileNames+','+ FileName ;
   else
    fileNames = FileName;
     }
         }
  
      }catch(Exception e){
         e.printStackTrace();
      }
    return fileNames;
   }
}
?>
select '<?=FileLists.FileNames()?>' from dual

Step 3:
Go to Option and create new option whose name is "Dir" , Data type as "Text" and Direct Value "<DIRECTORY_LOCATION>" (eg : C:/Sample_files)

Step 4:
Execute the procedure.
Completed Successfully!!!!!

      

Unable to Save Or Create Mapping in ODI (Strange Issue)

Recently I come cross one strange issue in ODI i.e., Our team and myself unable to save or create new mapping in ODI. After research a little I found the issue.

Solution:

We are unable to save or create mapping in ODI - For that we need to analyze, first we need to find out is there problem in Work Repository or Master Repository?

For that we checked when we are getting that issue - while we are creating mapping and saving mapping that means it belongs to work repository.

So we connected to work repository and execute below query:

select

   c.owner,

   c.object_name,

   c.object_type,

   b.sid,

   b.serial#,

   b.status,

   b.osuser,

   b.machine

from

   v$locked_object a ,

   v$session b,

   dba_objects c

where

   b.sid = a.session_id

and

   a.object_id = c.object_id

and c.object_name = 'SNP_MAPPING';

   and osuser  in ('root');

Output of the above query are locked Objects. So we need to kill those sessions. Then only ODI will support you for creating and saving mapping