The blog for Oracle Data Integrator ( ODI )

LKM OdiSqlUnload(File) to Oracle ( SQLLDR )

There are lots of time , we have issues in dealing with huge load  and we break the process in to multiple steps like First unloading using OdiSqlunload or some native Unloader and then using the SQLLDR to load the file . The process can be time consuming and some time we don’t get the exact result.

To solve these part we have integrated the complete OdiSqlUnload with SQLLDR into one LKM with support to load directly into Target table or into the Work table as required. We have tried to make these as smart and easy it can be  like single KM for both unix and windows environment , Sqlldr log in the operator so you dont have to log into the server to check what is the error due to , Filter or join multiple tables yet getting the performance of odisqlunload .

This KM is created to move huge data from source to Target using ( odisqlunload to unload table into File ) and Loading this file using SQLLDR to oracle target , and is based on the integration of OdiSqlUnload and LKM File to Oracle ( SQLLDR ) .

The LKM can be used to load into C$ or directly into the Target Table.

The Options  are as follows


This option enables you to create appropriate Control File ,so that SQLLDR can load directly into the Target Table else into Work Table ( C$_). By Default  WORK_TABLES  value is  NO (ie. No C$ will be created will be loaded directly into Target table using Sqlldr )
IKM Dummy is required if  WORK_TABLES=NO else select the appropriate IKM.

Please  select  the appropriate Directory where the OdiSqlUnload can unload(File)  the data from Source Tables.

This options enables you to delete the SQLLDR  and OdiSqlUnload Files .

Please enter the valid Date format used by SQLLDR to convert the date into appropriate Format.
The default Format is YYYY-MM-DD HH24:MI:SS


Please enter the Valid Timestamp format used by SQLLDR to convert the Timestamp into appropriate Format.
The default Format is YYYY-MM-DD HH24:MI:SS.FF

Please select the  Environment  where the OdiSqlUnload will unload  ( Unix / Windows ) used into CTL file.
This options enables to have for Unix  (ODISQLUNLOAD) ROW_SEP =n and in(CTL file) =str X’0A’
and for Windows  (ODISQLUNLOAD) ROW_SEP =nr and in(CTL file) =str X’0D0A’ .

Also for deleting files according to the environment( rm / del )

The commands of the LKM are as follows –


Unloading the source table into file ,delimited by (,) comma using OdiSqlUnload

OdiSqlUnload "-FILE=<%=odiRef.getOption("DIR_PATH")%>/
"-ROW_SEP=<% if (odiRef.getOption("UNIX_ENVIRONMENT").equals("1"))
{ %>n<%} else { %>rn<% } %>"
"-QUERY=select <%=odiRef.getColList("", "[EXPRESSION]", ", ", "", "")%>
<%=odiRef.getColList("", "[EXPRESSION]", "||','||", "", "")%> from <%=snpRef.getFrom()%>
where (1=1) <%=snpRef.getJoin()%>
<%=snpRef.getFilter()%> <%=snpRef.getGrpBy()%> <%=snpRef.getHaving()%>"


If the work tables option – yes or No , then accordingly the CTL is created ie. whether the table to be loaded is C$ or Target table.

SnpsOutFile "-File=<%=odiRef.getOption("DIR_PATH")%>/
<%=odiRef.getSrcTablesList("", "[TABLE_NAME].ctl", "", "")%>"
	SKIP=<%=odiRef.getSrcTablesList("", "[FILE_FIRST_ROW]", "", "")%>,
INFILE "<%=odiRef.getOption("DIR_PATH")%>/
<%=odiRef.getSrcTablesList("", "[RES_NAME]", "", "")%>" "str X'
<% if (odiRef.getOption("UNIX_ENVIRONMENT").equals("1")) { %>0A<%} else {%>0D0A<% } %>'"
BADFILE "<%=odiRef.getOption("DIR_PATH")%>/
<%=odiRef.getSrcTablesList("", "[TABLE_NAME].bad", "", "")%>"
DISCARDFILE "<%=odiRef.getOption("DIR_PATH")%>/
<%=odiRef.getSrcTablesList("", "[TABLE_NAME].dsc", "", "")%>"
DISCARDMAX <%=odiRef.getUserExit("LOA_DISCARDMAX")%>
<% if (odiRef.getOption("CREATE_WORK_TABLES").equals("1")) { %>
INTO TABLE <%=odiRef.getTable("L", "COLL_NAME", "W")%>
	<%=odiRef.getColList("", "[CX_COL_NAME]
<? if (u0022[DEST_DT]u0022.equals(u0022DATEu0022))
{?> DATE '"+snpRef.getOption("DATE_FORMAT")+ "' <?}?>
<? if (u0022[DEST_DT]u0022.equals(u0022TIMESTAMPu0022))
{?> TIMESTAMP '"+snpRef.getOption("TIMESTAMP")+ "' <?}?>" , " ,nt", "","")%>

<% } else {%>
INTO TABLE <%=odiRef.getTargetTable("SCHEMA")%>.<%=odiRef.getTargetTable("TABLE_NAME")%>
	<%=odiRef.getColList("", "[COL_NAME]
<? if (u0022[DEST_DT]u0022.equals(u0022DATEu0022))
{?> DATE '"+snpRef.getOption("DATE_FORMAT")+ "' <?}?>
<? if (u0022[DEST_DT]u0022.equals(u0022TIMESTAMPu0022))
{?> TIMESTAMP '"+snpRef.getOption("TIMESTAMP")+ "' <?}?>" , " ,nt", "","")%>

<% } %>


Changing the file permission so that SQLLDR and ODI can read the files

import os
<% if (odiRef.getOption("UNIX_ENVIRONMENT").equals("1")) { %>
if os.system(r"chmod 770 <%=odiRef.getOption("DIR_PATH")%>/*.* ") <> 0:
	raise 'File Permission Issues'
<%} else { %>
raise ' File Permission Not required for Windows environment '
<% } %>


import os
if os.system(r"sqlldr userid=<%=odiRef.getInfo("DEST_USER_NAME")%>/
<%=odiRef.getSrcTablesList("[TABLE_NAME].log", "")%> ") <> 0:
	<%=odiRef.getSrcTablesList("[TABLE_NAME].log", "")%>','r')
	raise 'OS command has signalled errors.Please see the log for details ',

Here if the process fails and the log will be shows into Operator , enabling to see the log without logging into Unix Box.

Although the display is not perfect still it shows great


import os
<% if (odiRef.getOption("UNIX_ENVIRONMENT").equals("1")) { %>
if os.system(r"rm -f <%=odiRef.getOption("DIR_PATH")%>/
<%=odiRef.getSrcTablesList("[TABLE_NAME].dat", "")%>
<%=odiRef.getSrcTablesList("[TABLE_NAME].ctl", "")%>
<%=odiRef.getSrcTablesList("[TABLE_NAME].bad", "")%>
<%=odiRef.getSrcTablesList("[TABLE_NAME].log", "")%> ") <> 0:
	raise ' ODI has failed to delete files Please remove them manually if required '
<%} else { %>
if os.system(r"cmd /c del <%=odiRef.getOption("DIR_PATH")%>
<%=odiRef.getSrcTablesList("[TABLE_NAME].dat", "")%>;
<%=odiRef.getSrcTablesList("[TABLE_NAME].ctl", "")%>;
<%=odiRef.getSrcTablesList("[TABLE_NAME].bad", "")%>;
<%=odiRef.getSrcTablesList("[TABLE_NAME].log", "")%>") <> 0:
          raise 'ODI has failed to delete files Please remove them manually if required '
<% } %>

Lets look at an example ,


First run is No Work Tables , Delete Sqlldr files – No  and I am running in the Windows environment  so Unix _environment = No .


IKM Dummy have to selected if  Create Work Tables = No ( as we are loading directly into the Target table )





Control File


Files are not deleted


Now lets run the KM with the following option

Create Work Tables – Yes ,Delete SQLLDR Files – Yes , IKM SQL Control Append


Being the Create Work table option as yes the c$ is create and so the CTL is created with Flow table options and columns . Also the SQLLDR loads into the C$ table  and then finally using the selected IKM ( for my example IKM Sql control Append ) data is loaded from I$ to Target table.


Control File


Lets see the code in Unix Environment



With  Unix environment as Yes the row_sep is n and Infile ‘0A’



Being an Unix environment , there are file permission issue so change the permission for SQLLDR to read the files.


Here since the delete SQLLDR Files are yes , rm command is used to remove them


Also in case the SQLLDR encounters errors , i have provide the code to display the log files into the Operator, this way you don’t need to login into Unix to view the log file for error. Although the display is not proper but still it can give you enough information on what is the error due to ?

If there is no error the log file wont be displayed.


I have included the codes to support Joins and Filter , as you can see  in the OdiSqlUnload codes where condition, have the join and the filter.



Download the LKM below.

LKM OdiSqlUnload(File) to Oracle (SQLLDR)

Download XML

IKM Dummy

Download KM_IKM_DUMMY.xml


  1. Hello, I’m landing pretty late on this topic but this is exactly what I’m looking for, the link probably had a long life but is not anymore, is there someone that can reupload this KM file ? Thanks alot !

  2. Hi Kshitiz,
    I just landed on this page because this KM is exactly what I’m looking to do,
    I feel like I missed the action on this because your last post is more than 5 years ago, and of course the links for the KM are dead,
    but If someone still have this file and hear me here, could you provide me a link for it ? It would be very thankful !!

  3. Hi,
    Download links for KM’s are not working, does anyone have these KM’s?


  4. Could you please let me know, where OdiSqlUnloader wants to unload the file(s)? Is it the odi-server, the agent-server or target-server?

    • Karlo ,

      Thanks for visiting us. By default everything will be carried out depending on Agent you selected. If you selected you Local(No Agent) then it wil lbe your local machine else the server where the respective Agent is located.

  5. Hi Devendra,

    I am sorry. I think you are referring to the LKM OdiSqlUnload(File) to Oracle ( SQLLDR ) post..My issue is about a CDC process under ODI by using a logical PK or UK. you can scroll up and find from my previous comments on this post.
    I had commented on a wrong post previously due to some urgency.


  6. Hi Cezar,

    I need to suggest this to a customer. can you please provide some detailed on this.


    • Chary,

      I beleive most of the detail have been mentioned in the post itself . This KM is planned to use where the data movement is high between source and target of different database and so using odisqlunload we can extract into File and later using SQLLDR move the data into Target. Please let us know if you need any other help.

  7. Hi Cezar,

    Thanks for your reply.
    I am a bit confused on the process you defined. Can you please provide me a detailed information/steps on this.

    Thanks in advance.

  8. Hi Guys,

    Can you please help me with the below requirement.

    I have a table in AS400 that don’t have a physical PK or UK defined.
    I have defined a logical UK for ODI and used CDC logic to capture the changes on the table. I have to replicate the changes of these table on Oracle 11g Database.
    when there is a change for this column in AS400 which i have defined as part of UK, ODI does an INSERT instead of an UPDATE. The result of this in Oracle we have some records that we don’t have in AS400.

    Is there any possibility to identify these records ?

    Also I would like ODI to recognize the OLD record (from the old UK) and delete it, and than INSERT the new record with the changed UK.

    How do i achieve this.? Any pointers.


    • Hi Chary,

      how are you?

      As the UK is updated and then you loose the “link” between source and origin, the only way to delete these records at target is creating a interface to a temp table all UK from source (just the UK you don’t need other columns) and then use a ODI procedure to perform a “delete… not exists..” on this temp table.

      In this way you will achieve what you need….

      Does it make any sense to you?

      Thank you for visiting us!!!!

  9. Hi,

    I am using ODI to load data from an ascii file ( pipe separated) to an Oracle Table. So I created a Interface with LKM as “File to Oracle (SQlLDR)” and IKM as “SQL Control Append”. CKM is “CKM Oracle”.

    I am using ODI Studio 11g with jdk 1.6 and am trying this on my windows XP machine. I also have sqlldr installed on my local machine. But still the interface fails with an error. The interface works if i use “LKM File to SQL” but i want to use SQLLDR because i will have high volumes coming in.

    Can you please help.

    Interface Error on Step Call sqlldr :

    org.apache.bsf.BSFException: exception from Jython:
    Traceback (innermost last):
    File “”, line 34, in ?
    Load Error: See K:Project GalaxyTech TeamODIDataFiles/NIN_GL_BALANCES.log for details

    at org.apache.bsf.engines.jython.JythonEngine.exec(JythonEngine.java:146)
    at com.sunopsis.dwg.codeinterpretor.SnpScriptingInterpretor.execInBSFEngine(SnpScriptingInterpretor.java:345)
    at com.sunopsis.dwg.codeinterpretor.SnpScriptingInterpretor.exec(SnpScriptingInterpretor.java:169)
    at com.sunopsis.dwg.dbobj.SnpSessTaskSql.scripting(SnpSessTaskSql.java:2374)
    at com.sunopsis.dwg.dbobj.SnpSessTaskSql.execScriptingOrders(SnpSessTaskSql.java:1615)
    at com.sunopsis.dwg.dbobj.SnpSessTaskSql.execScriptingOrders(SnpSessTaskSql.java:1580)
    at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTaskTrt(SnpSessTaskSql.java:2755)
    at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java:2515)
    at com.sunopsis.dwg.dbobj.SnpSessStep.treatAttachedTasks(SnpSessStep.java:534)
    at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:449)
    at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:1954)
    at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:1818)
    at oracle.odi.runtime.agent.processor.impl.StartScenRequestProcessor$2.doAction(StartScenRequestProcessor.java:559)
    at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:224)
    at oracle.odi.runtime.agent.processor.impl.StartScenRequestProcessor.doProcessStartScenTask(StartScenRequestProcessor.java:481)
    at oracle.odi.runtime.agent.processor.impl.StartScenRequestProcessor$StartScenTask.doExecute(StartScenRequestProcessor.java:1040)
    at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:114)
    at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:82)
    at java.lang.Thread.run(Thread.java:619)

    Jython Code :

    import java.lang.String
    import java.lang.Runtime as Runtime
    from jarray import array
    import java.io.File
    import os
    import re
    import javaos

    def reportnbrows():

    f = open(r”K:Project GalaxyTech TeamODIDataFiles/NIN_GL_BALANCES.log”;, ‘r’)
    for line in f.readlines():
    if line.find(“MAXIMUM ERROR COUNT EXCEEDED”)>=0 :
    raise line

    ctlfile = r”””K:Project GalaxyTech TeamODIDataFiles/NIN_GL_BALANCES.ctl”;””
    logfile = r”””K:Project GalaxyTech TeamODIDataFiles/NIN_GL_BALANCES.log”;””
    outfile = r”””K:Project GalaxyTech TeamODIDataFiles/NIN_GL_BALANCES.out”;””
    if len(”)>0: oracle_sid = ‘@’+”

    loadcmd = r”””sqlldr apps/%s control=”%s” log=”%s” > “%s” “”” % (oracle_sid,ctlfile, logfile, outfile)

    rc = os.system(loadcmd)

    if rc 0 and rc 2:
    raise “Load Error”, “See %s for details” % logfile

    if rc==2:

    • Rajesh,

      Please look into the log file to see the error . Also make sure you are able to trigger the sqlldr outside and see if that works and try to call the sqlldr file and check to see its functions very well.
      Please reach us back if you still have issues.

    • Hi
      I am also getting the same issue while using this particular KM(LKM FILE-SQLLDR).
      Could ypu please tell is there any specific configuration required to use this KM.I want to use it to handle Bad file or bad record.
      Its urgent,could you please provide me some solution

  10. Dear All,
    I have problems in loading text files using ODI. There are spaces that I need to trim because each row the spaces are differrent in size. The other thing it removes the first 3-4 arabic charachters from each field.


    • Nancy ,
      Are you using this particular KM and is your source Oracle ?
      you can use Trim to remove the spaces while loading into the Target.
      You mean to say why unloading into file the first 3-4 characters are remove from each field or is it happening while loading into Target. Please confirm ?

  11. Hi, iam using odisqlunload to unload data from as400 to flat file.
    first, i don’t know how to apply the concat operator || because this is specific from oracle, have you any ideas why proceed with as400?

    second, is not possible run odisqlunload in source server, iam running in target server odisqlunload reduce the execution time in 1 hour. if i try to put the odisqlunload execution in source server , can i reduce my execution time?


    • Hi Raul,
      Although i have not tried odisqlunload with AS400 , i think it should work as behing the screen there are some java code to unload the data . The only think check for Date , Decimal formatting. Also yes the select query expression was modified so that concatenation of column make the fetch faster . Correct me if iam wrong i think there is come ‘concat’ function which you implement (or) else
      just use the query expression like this without pipes and use it .
      “-QUERY=select ”
      Let me know if you need any help.

  12. Hi,

    I don’t see any links to download this file!

  13. How would this work if the source table and target table are in two different database ?
    Will the OdiSqlUnload pull data over the network ?

    • Hi Mark,

      Yes , odiSqlUnload will pull over the network and unload , so please specify the path where the Agent is located.

      If in case you are not able to see optimal performance using OdiSqlunload , use the native database unloader and unload at the source side and do an FTP at the target server and load into the target.

      Please let us know if you require any more help.

  14. Guys
    The one comment which jumps out the page at me is that you are not using the [-FETCH_SIZE=] option on SQLUnload – when dealing with bulk data this is highly recommended – it will improve extract performance significantly. By default ODI will use the fetch size defined in topology – (normally 30 rows) but when doing this kind of processing I suggest 5,000-30,000 – depending on your data, only testing will tell where is best.
    In other performance tweaks, we have generated the extract sql to concatenate the fields together and pass a single string to ODI (e.g. rather than SELECT FIELD1, FIELD2, FIELD3… we generate SELECT FIELD1||’,’||FIELD2||’,’||TO_STRING(FILED3)..). It appears that the databases tend to be faster at doing that, and leaving ODI to just write a single string to the file. There are some examples of this KM available If you want I’ll dig you one out so you can compare performance.

Leave a Reply

Required fields are marked *.