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.

About these ads