Welcome to All Things Warehouse Builder

  • ETL
    November 16, 2010

ODI 11g – Simple, Flexible, Powerful

David Allan

SQL as a source, inline SQL, SQL override...want to know more ....? A couple of specific enhancements in the code generation capabilities of ODI 11g, opens the door to an area of code generation which provides great benefit from the ability to have SQL as a source to the construction of even better performing code to better interface designs and accelerators.

So what are they? The two key changes are;

  • support for sub-select to chain, when possible, multiple interfaces into one. With ODI 11g the code generation capabilities of ODI were extended in order to build larger more complex interface designs without the necessity of staging via temporary interfaces. There have already been blog posts on this subject, for example see Uli Bethke's post here.
  • a property in the KM definition to define current command for sub-select. A new property defined on a command in a KM to indicate to the ODI code generator that this command should be used for generating the SQL statement for a sub-select. This one is the key change in addition to the support for sub-select that opens the door. Is the penny dropping yet?

These two together open the door to building operator(or transformation)-like KMs within the bounds of the information that can be defined by a temporary interface and the options on the KM.

Let's see...

Simple Example - The SQL as Source IKM

Let's build a very simple example that is similar to Informatica's SQL override capability or inline views - SQL as source. So to the new breed of code template an IKM with the SQL code generator to be used for sub-query. We define an IKM and have the target technology just now set to Oracle;


Now comes the interesting part, we can define a command to fulfill the SQL generation. This can be as simple as actually just being the SQL code (SQL as source) or something a little more complex such as fulfilling a transformation such as a pivot or table function.

Let's check out the command definition, its very simple - the SQL representing the subquery will be an option on the IKM. Don't worry you won't have to build this KM its on the code samples already, you can download the KM here. (I built it with Oracle as a target technology at first then changed for all using undefined technology). Also it will become blatantly obvious how its used when you see a simple example.


From the above you see 2 things; 'Use current command for Derived-Table sub-select statement' is checked, a bit of a mouthful, basically ODI will use THIS command in the IKM for generating the SQL when a temporary interface is used in an interface and the sub-select check box is selected.

The IKM has an option VIEWQUERY. When the IKM is used in an interface design this is where you will define the SQL as a source.


That's a peek inside the IKM, we can now build temporary interfaces that are essentially inline view designs that will define the view columns (the target datastore columns in the temporary interface) and the SQL query which is to be used to realize the interface. With ODI we can go the extra mile and use the ODI reference APIs to ensure the SQL is portable also across systems so that we don't hard-wire access, schemas etc.

Building the inline view definition

Like any view (in database terms) we define a signature - the columns to be projected are defined on the temporary target datastore on an ODI temporary interface, below you see I have defined a comment (--) for the mapping expression and the indicator is for the target;


The execution unit is assigned the SQL_as_Source IKM, where the SQL query is then defined on the VIEWQUERY option.


Above I have hard-wired the query, as mentioned we can use ODI reference APIs to get the physical name from the datastore in a flexible manner. So the query could be defined using the odiRef.getObjectName which makes the statement much more flexible and provides flexibility via the context to use different physical schemas for example.


That is the SQL as a source or inline view defined. We can reuse this definition many times in different interfaces. Let's see how it is used.

Using the inline view in an interface

The temporary interface (our inline view definition) can be dropped into other interfaces and used like any other interface in ODI, now in 11g as we have seen from other posts we can include the interface as a sub-select. The INLINE_VIEW can be joined  with other tables etc and used like any other interface artifact.


Above I am using the INLINE_VIEW temporary interface and have selected the Use Temporary Interface as Derived Table (Sub-Select), since the tables used by the view are from the same server as the BONUS table.

So what code gets generated?

Simulating the code in ODI 11g we see the SQL statement we defined in our temporary interface nested inside the overall code (this was using the had-wired SQL rather than the odiRef'd version).


This KM lets you then use a temporary interface as your SQL Query or inline view and provides a great mechanism to balance fully fledged design using interfaces and integrating custom arbitrary SQL.

You can download the SQL as Source KM from the code samples here on java.net. The example illustrates a capability that we can exploit for other cases - more to come!

Join the discussion

Comments ( 35 )
  • CRaig Wednesday, November 17, 2010
    This is cool to see the new capabilities being exploited in this way. The other one which we often get asked about is a stored procedure as a source. I was thinking of some way of introspecting the sp result so I could automatically define the columns....
  • David Allan Thursday, November 18, 2010
    Hi Craig
    The best option is to SQL-ize the stored procedure. This can go from;
    * the extreme of rewriting in SQL/built-in functions if possible when performance is critical to
    * wrapping the stored procedure in a SQL construct such as a function or table function.
    Once wrapped as a function then reading the function as a data source could be a simple template that ends up something like 'select fx(params) from dual' for Oracle.
    Likewise for table functions creating a KM for example that treated the temporary target datastore as a table function is easy stuff. The target datastore name is the function name and the inputs and outputs of the table function get identified via the UD* flags which lets you go some way.
    Lots of these kinds of cases are doable including stuff like Oracle SQL pivot. They rely on utilizing a best practice of using the UD* properties and the user to set the flags correctly. You do start to hit some bounds of the restrictions of when the 'use temporary interface as derived table' can be enabled - for that button to be enable-able for the usage of the temporary interface everything has to be in the same logical schema within that temporary interface - so couldn't have a file as source and then SQL pivot within the same interface (since a single SQL statement is generated).
    All good stuff, I'll see about posting some illustrations on the table function/functions, if there are other ideas that get sparked off let us know.
  • Commenter Thursday, November 18, 2010
    Hey ! Thx for that !
  • Amir Riaz Thursday, November 18, 2010
    really wonderful.
  • Sid Friday, November 19, 2010
    Well, three points:
    1. What is in this how-to which can be done in 11g but can not be in 10g? I can get around the option "Use current command ..." by specifying a dummy source datastore (to access the source schema) and mapping all target columns to a constant expression.
    2. I have a deep seated complain against inline views (both in informatica and your implementation in ODI). When you use inline views in a large project, you simply loose a clear view of transfer from source and transformation in target. I thought that ODI should not allow it by design. Each interface encompasses a single SQL like construct and after taking all temporary interfaces together they give nice transformation flow to the managed services stuff. Inline view completely eliminates it. I would rather appreciate if ODI can come up with a way to purse a SQL and create a part of the interface based on the pursing. That way we do not loose the transformation chain.
    3. In informatica, each column is linked with a source column. One can not specify 30 column in SQL and mention 29 columns in the source qualifier. In this case, there is no such enforcement of relationship until the run-time.
  • David Allan Friday, November 19, 2010
    Hi Sid
    Thanks for the comments.
    1. This generic approach is only possible in 11g and lets you override the default SQL generator for a temporary interface that is nested inside another.
    2. Totally. Its both friend and foe. Its great for letting you access the vast amounts of SQL grammar (table functions, XML SQL, XQuery, pivot/unpivot etc. etc.) that are in systems without having KM/component/operator support for them - its always been a high priority item on the wish list of all the tools I have seen. Yes, its also foe:-) One thing I didn't show is that in the temporary interface you could also add in the datastores used by the view into the mapping canvas then you'd still get ODI's dependency services which at least lets you see this datastore X is used in temporary interface Y. I've seen the request for generating interfaces/mappings from SQL over the years too, with OWB we did an expert which was a useful way for folk to both learn the tool (who knew SQL) and to build the mappings.
    3. True. The post was an illustration of overriding the SQL generation and happened to use SQL as source as an illustration. It doesn't have rich design time validation, the emphasis was on flexibility.
  • Sid Tuesday, November 23, 2010
    Thanks for the clarification. Do you know if ODI project team has any plans to support SQL to interface (even with limited set of keywords in ANSI 92 SQL) feature in any of the next few releases?
    In my view, the expectation with inline SQL started with informatica and only way this "inline-SQL-is-a-friend" disease (a huge headache in large projects if you are coming in the middle) can be removed is by providing a feature that can translate SQL into interfaces. At least Oracle already has a SQL parser that can conveniently parse ANSI SQL, then why not go for it?
    Imagine that you are fetching planning data from PSFT HR DB and you write a query on production database and the next day the data in planning is ready. Now, that would be a feature.
  • Narasimharao GAL Monday, February 21, 2011
    Thanks, It's worderful explation.
    I am working on Source as ERP system like SAP. inline view IKM are working fine for source system SQL or Oracle. the same Inline view IKM are not working for source system as SAP.
    When i am trying select check box for Use Temporary Interface as Derived Table (Sub-Select) it not allowing.
    Can any one please give some guidance about on the same.....
  • David Allan Monday, February 21, 2011
    Hi Narsimha
    Thanks I am glad it has helped you. The ability to click on the 'Use as Temporary Interface as Derived Table(Sub-Select)' comes from the technology definition - there is a technology property named Support Derived Table. For Oracle this is enabled, for SAP this is disabled that's why you have been unable to use it with SAP. I don't know if simply enabling this will let you do what you want, you could try - I will check and get back to you, if you make progress let me know.
  • David Allan Tuesday, February 22, 2011
    Hi Narsimha
    Some more update....the ODI SAP technology does not support inline SQL for now, it is much more than just enabling this checkbox since the KMs code a lot of the interface design (its not just SQL).
    What scenarios would you like to use it for? Interested to hear what you need it for.
  • Chary Monday, April 25, 2011
    Hi David,
    Can you please help me with the below point :
    Need to know if ODI 11g supports IDOC for SAP Integration via Oracle Open Connector..?
    Appreciate your response.
  • David Allan Monday, April 25, 2011
    Hi Chary
    This note summarizes the options;
  • guest Monday, June 13, 2011

    Hi David, I'm using as the SQL Source KM to carry data from a query (oracle) to a table (oracle), 'Use current Derived-Table command for sub-select statement 'not checked. Command on target (

    <% = odiRef.getUserExit (VIEWQUERY ")%>


    The execution of the interface ends OK, processed 1 record but not inserted.

    What could happen? or missing?

  • David Monday, June 13, 2011


    I would suggest getting the executed DML from the ODI operator and ensuring the correct SQL is generated and debugging from there.



  • David Monday, June 13, 2011


    I would suggest getting the DML executed from the ODI operator and debugging to figure out what is wrong.



  • Fernando Parra Tuesday, June 14, 2011

    It's working!!

    Was wrong my definition of the temporal tables using the inline view in an interface.

    Thanks David

  • guest Sunday, October 16, 2011

    com.sunopsis.tools.core.exception.SnpsSimpleMessageException: ODI-17517: Error during task interpretation.

    Task: 3

    java.lang.Exception: BeanShell script error: Sourced file: inline evaluation of: ``out.print("BeanShell script error: Sourced file: inline evaluation of: ``out.pri . . . '' Token Parsing Error: Lexical error at line 2, column 32. Encountered: "

    " (92), after : "": <at unknown location>

    BSF info: Insert flow into I$ table at line: 0 column: columnNo

    at com.sunopsis.dwg.codeinterpretor.SnpCodeInterpretor.transform(SnpCodeInterpretor.java:485)

    at com.sunopsis.dwg.dbobj.SnpSessStep.createTaskLogs(SnpSessStep.java:685)

    at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:450)

    at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:1740)

    How to rectify this error ? solve this...

    Thank you in advance

  • David Tuesday, October 18, 2011

    Not much context here in this error, have to divide and conquer to figure out what is going on.



  • guest Friday, January 6, 2012

    A fundamental question. How is this approach different from creating a View in the source database (if possible).

    Why to go through the extra hassle of maintaing the new KM ?

  • David Friday, January 6, 2012


    For the exact reason you mention 'if possible'. If you cannot then you will have to design the transformations within the tool. This is a way of pushing the envelope and describing whatever you want.



  • David Monday, August 27, 2012

    You have to then click on Data Integration and download the IKM from that page.



  • guest Friday, December 7, 2012

    MessageException: ODI-17517: Error during task interpretation

    You are getting this error because u the syntax error , i was getting this error but when i fix the syntax i got the result.

    <%=odiRef.getObjectName( "L" , "EMP" , "D" )%>

  • guest Thursday, January 23, 2014

    Hi David, I realize this is an old thread but was hoping you could elaborate on your response of using a stored proc as a data source. I have a Stored Proc in SQL server that returns a bunch of rows that I need to then insert into a Oracle table. I am on ODI Just wanted to check in to see if you had a sample blog post on how this could be done.

    So on the source, I have something like:

    Execute usp_GetProfile @DateLastUpdate = '01/21/14'

    and then on the target

    loop over the result set and insert into a Oracle Table.

    The ODI docs indicate that a variable can only be a scalar type (date, string,numeric)

  • David Thursday, January 23, 2014

    Does the function return a table? If it did then its easy invoke, see the following entry on how the table is called from SQL.




  • guest Friday, January 24, 2014

    Thanks David! I asked the SQL server team to change this to a function returning a table. So now, I can execute the following query from a sql editor:

    select * from dbo.ufn_GetProfile('01/21/14')

    I wasn't sure what you meant by a simple invoke. Do I create a ODI procedure with the above SQL (with date input param) in the 'Source' and then insert into the target oracle table? However, how would I get a handle on the result of the above query.

  • David Friday, January 24, 2014

    For this you can try using ufn_GetProfile('01/21/14') as a resource name for a datastore you would create describing the table returned from the function.



  • guest Friday, January 24, 2014

    Hi David, I tried putting the function name in the Resource Name but that doesn't seem to work. Data store type is Table.

    a)ufn_GetOffenderProfile() OR ufn_GetProfile('01/21/14')

    Chained exception:

    java.sql.SQLSyntaxErrorException: [FMWGEN][SQLServer JDBC Driver][SQLServer]Invalid object name 'HCODS.dbo.ufn_GetProfile()'.

    at weblogic.jdbc.sqlserverbase.ddb_.b(Unknown Source)

    b)Just the function name: ufn_GetProfile

    Chained exception:

    java.sql.SQLException: [FMWGEN][SQLServer JDBC Driver][SQLServer]Parameters were not supplied for the function 'HCODS.dbo.ufn_GetProfile'.

    Besides these issues, the other problem I see here is how to make the function take a dynamic date input parameter instead of a hard -coded value.

  • martin Wednesday, July 22, 2015


    I downloaded and installed KM used in article, it works fine. However, if there are any licenses to use this KM?

    Thanks for reply.

  • guest Wednesday, July 22, 2015

    thanks, it's working fine

  • David Wednesday, July 22, 2015

    Hi Martin

    No, ODI license is fine.



  • Santhosh Sreshta Thursday, July 23, 2015


    I have done the above process, but cant get the values to be copied to target table. the interface gets executed without errors. I am unable to sort out the problem. please help me.

  • David Thursday, July 23, 2015

    Can you inspect the generated SQL in the interface and check it is what you think? Then you can try debugging it - by breaking it down.



  • Santhosh Sreshta Monday, July 27, 2015

    Hi David,

    I have done till the above step of expression editor. The values are not loading into target table. Once i had tried using datastore as target , failed. again tried giving temporary target, again failed to get the data.

    it is showing unable to build execution plan.

    here is the error i got when tried to see the data in the target table.

    See com.borland.dx.dataset.DataSetException error code: BASE+62

    com.borland.dx.dataset.DataSetException: Execution of query failed.

    at com.borland.dx.dataset.DataSetException.a(Unknown Source)

    at com.borland.dx.dataset.DataSetException.queryFailed(Unknown Source)

    at com.borland.dx.sql.dataset.QueryProvider.a(Unknown Source)

    at com.borland.dx.sql.dataset.JdbcProvider.provideData(Unknown Source)

    at com.borland.dx.dataset.StorageDataSet.refresh(Unknown Source)

    at com.borland.dx.sql.dataset.QueryDataSet.refresh(Unknown Source)

    at com.sunopsis.graphical.frame.DwgDataFrame.initialize(DwgDataFrame.java:368)

    at com.sunopsis.graphical.frame.DwgDataFrame.<init>(DwgDataFrame.java:77)

    at oracle.odi.ui.etlmodeler.diag.inspector.pane.popup.DiagramActionDispayDataTarget.actionPerformed(DiagramActionDispayDataTarget.java:91)

    at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:2018)

    at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2341)

    at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:402)

    at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:259)

  • David Monday, July 27, 2015

    Not sure what your target database is, but I'd check with whatever its native tool is to see if there is any data. The error you show is in ODI's dataviewer. Check that your interface executed ok, check that the target table exists in the database, see if ODI reports any inserts in the ODI operator.

  • Santhosh Sreshta Tuesday, July 28, 2015

    I have taken temporary table as target table. the interface is been executed without any errors. after execution. I have opened Code form operator. there when i tried to run "Query/Execution plan" then it is giving values below. but when it is running by itself it is not able to execute the query.

    Any ideas please.

Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.