Warning: Illegal string offset 'ssb_og_tags' in /home/content/10/4632510/html/wp-content/plugins/simple-social-buttons/simple-social-buttons.php on line 1466


The blog for Oracle Data Integrator ( ODI )

Using one of ODI’s lesser-known secrets: the Common Format Designer (CFD) – by Craig Stewart

Hi Reader,

It’s a great pleasure starts a new “sector” from ODI Experts. It is our “Invited Experts” and we will start with one of the main ODI Guru in the world. Craig Stewart!!!

Thank you Craig!

Please, enjoy his post!


As I’m essentially in pre-sales, I like tools which I can use to rapidly do tasks which might otherwise take a lot of grind. Things like the Knowledge Modules in Oracle Data Integrator (ODI) are well known to take complex tasks and to make them simple – like integrating slowly changing dimensions: Simple with the IKM. One of the things which was introduced into ODI (actually into Sunopsis, before the acquisition by Oracle, was the Common Format Designer, In the latest release of ODI (11g) it is actually much better documented (even if it has not evolved much!) than in earlier versions – but what I’d like to show you briefly here is how it can be used to rapidly implement a new schema and to generate the interfaces to populate it.

To start with, let’s say I have a set of tables in my source database, in my case I’ll use MySQL. If I want to move that set of tables, with data across to Oracle, I’ll need to have a schema in Oracle where I can put the tables. In ODI’s Topology Navigator (see, I’m already using the new 11g terminology!) I need to define the Data Servers, the physical schemas and associate them with the relevant logical schemas. This is the point at which I’ll begin, I have a data model in ODI which has my “source” data defined in it:


I want to put this into my Oracle schema which I’ll call ORCL_TMP, so in Desgner, I create a new, empty data model in the Oracle technology for this schema:



Now I create a diagram, by using the Right-Mouse-Button on the Diagrams Node:


I must simply give this a name and any description, and naturally, the documentation…


Then, we go to the Diagram tab so we can start doing the business:


Now we drag & drop the datastores from our MySQL model, onto the new ORCL_TMP diagram, ODI will prompt us with a dialog box:


Which of course we know, and accept. The table is then added to the model, automatically using the metadata about datatype translations to construct the target table.


This will then show in the diagram:


We can then drag across additional datastores to make our model:


Note that in 11g, you are able to select multiple datastores and drag them across to the diagram. One drawback to this is that when referential integrity is defined, using this technique won’t get you the referential integrity, whereas if you drag one at a time, you will. It will give you all keys, indexes foreign keys as defined in the source system, and applicable in the target.

Now for some magic: if you right-mouse-button the Data Model, one of the optrions is to “Generate DDL”:


If we select this option, ODI will ask if we want to process tables not in the moidel (i.e. generate deletes to synchronize the two, or just to generate the delta code for the tables we have in the model. I’ll select “No” for this:


This will bring up a dialog where I can choose which of the differences I want synchronized:


If I select all, and press the OK, it will generate an ODI procedure for me in the Default Folder I entered when I defined the model:


I can press the “Play”/”Execute” button to get ODI to execute the generated code, which I can then follow in the Operator Navigator:


Often, you will get errors with the tables which contain DATE/DATETIME/TIMESTAMP fields as ODI is not always clever about translating them. In this instance I just edited the generated code to take out the TIMESTAMP(23) and changed it to TIMESTAMP. So now I have a fully created data model. The next step is to get some data moved in, so I need interfaces. ODI will of course remember where the data stores were dragged from, and to, so will generate for me an interface which just does a one-to-one mapping using the default knowledge modules:


I select the “Generate Interfaces In” and ODI brings up a dialog for the tables:


The required interfaces are then generated in that default directory:


I need to go into each one in order to execute it (If you try to execute without going in and saving it, you will get an error) You have to force a change, so that ODI can go through the process of saving it, which in turn makes it do some internal stuff, enabling execution. Then execute it.

Now you have data in your newly created data structure!

Craig Stewart


  1. Hi guys
    I am a beginner in ODI and DW field.
    Got very basic question:
    If i have in mapping origin table coming as time datatype, and target table as timestamp, its showing ‘default date hh:mm:ss’
    Now we dont want the date part that’s showing a default one
    So i changed the data type to Interval and in expression I converted to_dsinterval. But thats giving an error
    I thought of using Substring in expression but i guesss that also wont work, as timestamp will always need the date part, i am going to try this though

    Is there any advice someone can give me on this? How to get timestamp without date in ODI expression for mapping

  2. i am new to the odi while am creating a new model,in the model when selecting to the technology tha logical schema is showing undefined whats the probel plzzz help me

  3. Pingback: ODI 11g – Interface Builder Accelerator | Annoying things on search engines

  4. Hi Craig,

    I have an issue with one of my customer. The Issue in detail is as below :

    ODI Version :
    OS : Linux x86

    The Customer says :

    We are using ODI to generate a SOAP web service over a MySQL database, but are experiencing problems when testing published services containing MySQL datetime or timestamp fields.

    Step by Step Description of the problem :

    We are testing an ODI generated web service (generated by the service knowledge module) which exposes data in a MySQL database via Axis2. The database connection is working ok, and using SOAP UI or other client we can retrieve data from tables that have no datetime or timestamp columns, but where there is a datetime or timestamp column the service returns either: a) A SOAP Fault if the datetime elements are empty or omitted or b) no results

    Steps to Reproduce :

    1. Use ODI to generate and deploy an Axis2 web service exposing data in a MySQL database table which contains at least 1 datetime or timestamp column.
    2. Build a web services client and try calling the get****Filter method, to retrieve some records. Omit all optional elements in QueryFilter, and leave an empty entity element.
    3. Observe returned SOAP fault:


    end_datetime can’t be null

    java.lang.IllegalArgumentException: end_datetime can’t be null
    at oracle.odi.dataservices.fwk.axis2.OMSerializer.serializeManagedEntity(OMSerializer.java)
    at oracle.odi.dataservices.fwk.axis2.OMSerializer.serializeManagedEntityList(OMSerializer.java)
    at oracle.odi.dataservices.fwk.axis2.OMSerializer.serializeOMElement(OMSerializer.java)
    at oracle.odi.dataservices.fwk.axis2.DataServicesMessageReceiver.invokeBusinessLogic(DataServicesMessageReceiver.java)
    at org.apache.axis2.receivers.AbstractInOutSyncMessageReceiver.receive(AbstractInOutSyncMessageReceiver.java:39)
    at org.apache.axis2.engine.AxisEngine.receive(AxisEngine.java:144)
    at org.apache.axis2.transport.http.HTTPTransportUtils.processHTTPPostRequest(HTTPTransportUtils.java:279)
    at org.apache.axis2.transport.http.AxisServlet.doPost(AxisServlet.java:116)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:763)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
    at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:711)
    at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:368)
    at com.evermind.server.http.HttpRequestHandler.doProcessRequest(HttpRequestHandler.java:866)
    at com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:448)
    at com.evermind.server.http.AJPRequestHandler.run(AJPRequestHandler.java:302)
    at com.evermind.server.http.AJPRequestHandler.run(AJPRequestHandler.java:190)
    at oracle.oc4j.network.ServerSocketReadHandler$SafeRunnable.run(ServerSocketReadHandler.java:260)
    at com.evermind.util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.java:303)
    at java.lang.Thread.run(Thread.java:595)

    4. enter a valid XSD datetime value element, e.g:
    Observe that no results are returned.

    Please suggest me with your points on how to go further with this issue. This is very crucial for me.


  5. Thanks Craig,

    I did try the multiple table drag and drop and it did come with the reference links in tact, perhaps this is fixed now. As you stated it is relatively easy to use, which is great.

  6. Great Stuff Craig. Thanks for Sharing

  7. Craig,

    Thanks. Few days back I tried to do DDL generation thing and ODI hung for a long time (2 hrs). Later I killed it and did not have the opportunity since then.

    Do you know if 11g provides a way to script the generation of interfaces? Even for the ones that requires join between multiple surce data stores or requires filter?

Leave a Reply

Required fields are marked *.

This site uses Akismet to reduce spam. Learn how your comment data is processed.