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

Automatically trigger the process when we place the file in specific location

Scenario:
Integration team decided to place a file in particular location and they asked ETL team to pick that file and load into the Target data store.
They are not sure when they are going to place that file. But ETL team need to pick that file immediately once they place the file in that location and process it.

File Name: Sample.txt
File Type : Fixed Length File

Assumptions:
Aware of loading data from File (Fixed length) to Table using ODI

Step 1:
Follow my previous post (Fixed length File Load)

Step 2:
Create a package -- Drag and Drop ODIWaitFile (tool) from toolbox.

Step 3:
Select ODIWaitFile and go to properties.
Directory : Specified intergation team provided file Location (example : C:\Sample_files)
Filename Mask : Sample.txt
For remaining properties leave it as it is

Step 4:
Drag and Drop Mapping into your package. Select "Ok" and connect to ODIWaitFile and Mapping with that connector

Step 5:
Drag and Drop ODIFileMove (tool) from toolbox.
Filename : (C:\Sample_files\Sample.txt) Specify complete file location with file name)
Target File : Specify where you want to move that file (for Example : C:\Sample_files\ZIP)
For remaining properties leave it as it is

Step 5:
Select "Ok" and connect to ODIFileMove from Mapping and again connect ODIFileMove to ODIWaitFile as below:

It seems to be cyclic that means it is infinity loop. Whenever we place a file it pick it up and load into target data store and move into another folder and wait for next file

Step 6:
We need to execute only for one time
Completed Successfully !!!!!

How to load Fixed length file to Target Data store (Table) ?

File Name: Sample.txt
File Type : Fixed Length

File Data:

Assumption:

Aware of Topology Configuration for Oracle Technology and File Technology.

If your not aware of it then follow this link (step 1 to step 3)

Step 1:
Go to Designer Navigator -- Expand the Model Accordion -- Create New Folder -- Right click and create new model for Oracle and File
(Assumption Your aware of creating Oracle Model and File Model)

Step 2:
Right Click File Model and select New Data store. Select Resource name by clicking on Mirror image. (Sample.txt)

Step 3:
Go to File Tab.
File Format : FIXED
Select Heading : 1 (Because in our file first line is heading)

Go to Attributes Tab
Click on Reverse Engineering

Divide the column as mention above.

Step 4:
Go Designer Navigator -- Go to Project Accordion
Create new Mapping -- Drag and Drop Source Data store (File Data store) and Target Data store (Oracle Data Store) -- Connect output connector of Source to into input connector of Target and select automap true

Select Appropriate KM's (LKM File to SQL and IKM Oracle Simple insert)

Step 5:
Execute the Mapping (Completed Successfully)

Output: