Adding Datatypes to Oracle Data Integrator (ODI)

As I’ve mentioned before ODI is a tool that can learn. And here is one of the proof. 🙂
There are many datatypes in various databases and each version may have new datatype, but your ETL tool should learn while talking with database with that datatype. I’ll try to explain how to add “bigint” datatype to Sybase Adaptive Server Enterprise and “SDO_GEOMETRY” to Oracle.

Adding BIGINT to Sybase ASE (Adaptive Server Enterprise);

As you can see from the image below, out-of-the box of Oracle Data Integrator with version 10.1.3.5 doesn’t contain bigint datatype for Sybase ASE, however Sybase added this datatype later than ODI 10.1.3.5 released.

First right click Datatypes and click “Insert Datatype.
Type appropriate values (shown below).

For my environment, I have filled out the appropriate datatype conversions for “Converted To” and “Converted From” tabs (shown below accordingly).

And it is over. This is it. You may start using bigint datatype with “B” shown in your model.

Adding SDO_GEOMETRY to Oracle;

As you can see from the image below, out-of-the box of Oracle Data Integrator with version 10.1.3.5 doesn’t contain sdo_geometry datatype for Oracle, however when you install geographical stuff in your Oracle, you will have this datatype.

First right click Datatypes and click “Insert Datatype.
Type appropriate values (shown below). “G” stands for my name’s initial but also Geographic datatype. 🙂

For my environment, I have filled out the appropriate datatype conversions for “Converted To” and “Converted From” tabs (shown below accordingly). Since I’ll use this datatype for only Oracle2Oracle and File2Oracle, I’ve only implemented those conversion rules.

And it is over. This is it. You may start using SDO_GEOMETRY datatype with “G” shown in your model.

Restrictions about custom added datatypes;

  • Reversing custom datatypes will not work. Custom added datatypes will shown blank on table in the model. You need to select manually for the columns which are custom added datatype.
  • To determine this occurrence, check the interface which you drag and drop those tables.
  • Viewing data for some datatypes are not necessary (such as SDO_GEOMETRY), but SQL codes (SELECT, INSERT or UPDATE) will work without error on the database.
Advertisements

11 thoughts on “Adding Datatypes to Oracle Data Integrator (ODI)

  1. Thanks you for sharing. What happens when you add a datatype that Oracle does not support and then they support it? Does that break your work?

    • Since there won’t be a datatype for source system tables, it won’t be a problem, nor won’t break your work. But you must be careful about custom added datatypes that you have implemented in Oracle. 🙂

  2. I need to create a data type DATETIMEOFFSET in ODI11g under SQL server Technology. But when i tery to execute the interface it is giving the error Invalid column!

    • If you follow steps of creating a datatype in order, you’ll see your datatype when creating integration table (I$) related column should have DATETIMEOFFSET datatype.

  3. I tried but not working. The source is SQL server view and target is oracle table. When I create the datatype datetimeoffset in SQL server technology and execute the interface it is giving invalid column error.

    • As mentioned before, do you see DATETIMEOFFSET datatype in your create table (IKM – I$) statement? Which KM are you using, which step do you get error?

  4. It is giving error on the loading and inserting the data in c $ table…. So not able to see the I$ structure. I am using lkm SQL to oracle at source and ikm oracle incremental at target.

    • Then do your newly added datatype exists in create table statement? Doues C$ table creating in Oracle or MSSQL? Oracle do not have that datatype, you need to edit datatype and set “converted to” and “converted from” to convert your data type into Oracle’s appropriate datatype.

  5. C$ table is created in Oracle and the datetimeoffset column is coming as NVARCHAR2 in the C$, doesn’r matter whether i edit datatype and set “converted to” and “converted from” to convert your data type into Oracle’s appropriate datatype. It always get converted into NVARCHA2.
    Don’t have any idea why it is giving issue only with this DATETIMEOFFSET datatype. When we convert it to datetime it is working fine but we need only DATETIMEOFFSET for timezone difference.

  6. As I’ve said, if you can execute everything in a SQL editor in your agent’s operating system, everything should be fine. Try something different (custom or user data type) first and then apply the same thing for the DATATIMEOFFSET. I have successfully implemented many custom and/or not applied datatypes to ODI 10G and 11G, faced no issue upt to now.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s