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 )

Handling the “ORDER BY” clause in an interface

Hello Everyone…

We always get a lot of emails asking about how to “Order By” in ODI because of that, here there is a simple way to do it…

Stays as a suggestion to Oracle implement as default feature in forward versions!

A simple example how to handle Order By in ODI.

Solution 1

Step 1.  Create an Option


Step 2. Add the code into KM

<% if (odiRef.getOption("ORDER_BY").equals("1")) { %>
<%=odiRef.getColList("", "[COL_NAME]", ",nt", "", "(UD1)")%>
<%=odiRef.getColList(",", "[COL_NAME]", ",nt", "", "(UD2)")%>
<%=odiRef.getColList(",", "[COL_NAME]", ",nt", "", "(UD3)")%>
<%=odiRef.getColList(",", "[COL_NAME]", ",nt", "", "(UD4)")%>
<%=odiRef.getColList(",", "[COL_NAME]", ",nt", "", "(UD5)")%>
<%=odiRef.getColList(",", "[COL_NAME]", ",nt", "", "(UD6)")%>
<%=odiRef.getColList("", "[COL_NAME]", ",nt", "", "(UD7)")%>
<%=odiRef.getColList("", "[COL_NAME]", ",nt", "", "(UD8)")%>
<%=odiRef.getColList("", "[COL_NAME]", ",nt", "", "(UD9)")%>
<%=odiRef.getColList("", "[COL_NAME]", ",nt", "", "(UD10)")%>
<%} %>

This way you can have the KM to handle with and without Order By option .

Step 3.  Mark the Columns accordingly with UD1


Solution 2

Step 1.  Create an Option


Step 2 . Add the option into KM


Step 3.  Add the Order By Clause accordingly

Provide the Order By clause accordingly for N number of columns  in the interface


Sample Output


Good to see you around…

Keep visiting us at www.odiexperts.com


  1. Can we perform ORDER BY for target flat file.

  2. I’m new to ODI. Can you please specify where to write this code.

  3. This works well when the order by field is one of the fields in the target, but what if you want to export 5 fields into a file, but sort them by a different field in the source?

  4. Thanks a lot man 🙂

  5. It is a very helpfull example but i need a help form you.

    I am not able to find the navigation and steps to create the order by.


Leave a Reply

Required fields are marked *.

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