How Can I Define (New) Datatypes In ODI Topology? [ID 424012.1]

  Modified 17-NOV-2010Type HOWTO Status PUBLISHED 

In this Document
Goal
Solution
Steps to reverse engineer datatypes
Steps to manually define datatypes

---

Applies to:

Oracle Data Integrator - Version: 3.2.03.01 and later [Release: 3.2.03 and later ]
Information in this document applies to any platform.
***Checked for relevance on 17-NOV-2010***

Goal

Oracle Data Integrator (ODI) does not include a datatype which is supported by the JDBC driver that is being used.

How is it possible to define new datatypes in ODI Topology?

Solution

With Oracle Data Integrator (ODI) Topology, you may either:

• reverse engineer datatypes from the JDBC driver or
  
• manually define the desired datatypes
  

Steps to reverse engineer datatypes

1. In the Topology Manager, right-click the relevant Technology and select "Duplicate".

2. Define (and test) a Data Server using this new Technology.

3. Right-click the new Technology and select "Reverse Datatypes". ODI will retrieve all the datatypes recognized by the JDBC driver.

4. Open the datatype list and set the "Converted To" values for the other technologies that you will be working with.

5. For all other Technologies, it will be necessary to carry out the same operation for the new datatype.

Steps to manually define datatypes

1. In the Topology Manager, go to the relevant Technology, right-click on "Datatypes" and select "Insert Datatype" from the floating menu.
   
   
2. On the newly created datatype "Definition" tab, set:
   
   • the Code used by ODI to refer to this datatype
     
   • the Name of the datatype
     
   • the Reversed Code as returned by the driver and/or an SQL reverse query.
     
   • the syntax used to create a column of this type (Create Table Syntax)
     
   • the alternate syntax used to create a column storing data for a non writable datatype (Writable Datatype Syntax)
   • the letter used to represent the datatype (Letter used for the icon) and
     
   • check/uncheck the "Writable" box. Check this box if this datatype can be used in an INSERT or UPDATE command. A datatype that is not writable should have an alternate Writable Datatype Syntax defined, to enable ODI to create temporary tables storing data of this type
   
   For example, to manually define the Oracle NCLOB datatype, set:
   
   • the Code: NCLOB
   • the Name: NCLOB
   • the Reversed Code: NCLOB
   • the Create Table Syntax: NCLOB
   • the Writable Datatype Syntax: NCLOB
   • the Letter used in the Icon: C
   • and check the "Writable" box
     
     
3. Open the above created datatype and set the "Converted To" values for the other technologies that you will be working with.
   
   
4. For all other Technologies, it will be necessary to carry out the same operation for the new datatype.
   

@ ISSUE_PRODUCT:SNPS