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 )

Calling OdiSqlUnload using ODI Procedure with no hardcoded password

Hello all,

OdiSqlUnload is a great utility for unloading  but then we need to hardcoded and provide the parameters of the username and password which can change in the Other Environment in ( Testing , UAT or Prod ) . Here is a small trick to handle that situation.

In the Command on Source, Select the Required Technology and Schema name .

Copy the Syntax of the OdiSqlUnload into an ODI procedure into Command on Target  with Sunopsis API as the Technology

OdiSqlUnload “-FILE=” “-DRIVER=<%=odiRef.getInfo(“SRC_JAVA_DRIVER”)%>” “-URL=<%=odiRef.getInfo(“SRC_JAVA_URL”)%>” “-USER=<%=odiRef.getInfo(“SRC_USER_NAME”)%>” “-PASS=<%=odiRef.getInfo(“SRC_ENCODED_PASS”)%>” “-FILE_FORMAT=VARIABLE” “-FIELD_SEP=,” “-ROW_SEP=rn” “-DATE_FORMAT=yyyy/MM/dd HH:mm:ss” “-CHARSET_ENCODING=ISO8859_1” “-XML_CHARSET_ENCODING=ISO-8859-1” “-FETCH_SIZE=5000” “-QUERY=select * from QUERY”

This way ODI will automatically get the parameters from the topology and also use the Fetch_Size 5000 for good performance .

Also look into this post for generating header in OdiSqlUnload – http://odiexperts.com/?p=1845

Just a small trick . Look for more ideas at odiexperts.com


  1. My ODI does not have “Sunopsis API” as the technology to choose. I choose “ODI Toos” instead.

    However, when I run the scenario with the procedure, I get an error:
    java.lang.IllegalArgumentException: Driver class name must not be empty

    and looking at the code, I see ODI did not do the substitutions:
    OdiSqlUnload “-FILE=/usr/tmp/odi_publish_process_statuses.rpt” “-DRIVER=” “-URL=” “-USER=” “-PASS=” “-FILE_FORMAT=VARIABLE” “-ROW_SEP=\r\n” “-DATE_FORMAT=yyyy/MM/dd HH:mm:ss” “-CHARSET_ENCODING=ISO8859_1” “-XML_CHARSET_ENCODING=ISO-8859-1″…

    Any help would be appreciated.

    Thank you

    • First of all thank you Cezar for this code, I tired implementing in ODI 12x and had to tweak the code little bit. We can still use it in procedure and technology is ODI TOOLS rest all are default. The query returns the error message it failed with. This code worked fine for me. let me know if you have any further issues.

      OdiSqlUnload “-FILE=/apps/logs/LoadplanFailurelog.txt” “-DRIVER=” “-URL=” “-USER=” “-PASS=” “-FILE_FORMAT=VARIABLE” “-FIELD_SEP=,” “-ROW_SEP=rn” “-DATE_FORMAT=yyyy/MM/dd HH:mm:ss” “-CHARSET_ENCODING=ISO8859_1” “-XML_CHARSET_ENCODING=ISO-8859-1” “-FETCH_SIZE=5000”

  2. Hi,

    i implemented the OdiSqlUnload and its works fine on development. But how can i pass this to production? When i create an deployment archive without export key ODI will remove the -PASS parameter. And when ik add an export key i get the following error during creation of the archive:
    ODI-11010: Error occurred while Creating the Deployment Archive file
    oracle.odi.core.security.cryptography.CipherTextMigrationException: ODI-14147: An error occurred while migrating cipher text in object SNP_SCENTASK – ‘null’ (with Internal ID: 4775 | 1 | 2 and Global ID: 11799fd1-7f05-4010-a9cc-0cd02147c3e8)
    Input length must be multiple of 16 when decrypting with padded cipher
    ODI cannot decrypt the password cipher text which was not encrypted previously by the current master repository cipher.

  3. Hi,
    The OdiSqlUnload works fine to me, but I need protect the data in the CSV files using quotation marks.
    And I cant’t find this option in the OdiSqlUnload command.
    Is there any way to add quotation marks to all fields?

  4. My Concern is about OdiSqlUnload “-FILE=test_file.txt”
    I want Use variable instead of the fixed File Name Like “-FILE=Parameter1.txt”
    would you please help ?

  5. Is there a matching thing for Oracle 12C?

  6. I am getting the following error:

    java.lang.NoClassDefFoundError: com/sap/conn/jco/JCoException
    at java.lang.Class.forName0(Native Method)
    at java.lang.Class.forName(Class.java:247)
    at com.sunopsis.dwg.function.SnpsFunctionBase.getOpenToolApi(SnpsFunctionBase.java:916)
    at com.sunopsis.dwg.function.SnpsFunctionBase.getSunopsisApi(SnpsFunctionBase.java:491)
    at com.sunopsis.dwg.dbobj.SnpSessTaskSql.executeOdiCommand(SnpSessTaskSql.java:1431)
    at oracle.odi.runtime.agent.execution.cmd.OdiCommandExecutor.execute(OdiCommandExecutor.java:44)
    at oracle.odi.runtime.agent.execution.cmd.OdiCommandExecutor.execute(OdiCommandExecutor.java:1)
    at oracle.odi.runtime.agent.execution.TaskExecutionHandler.handleTask(TaskExecutionHandler.java:50)
    at com.sunopsis.dwg.dbobj.SnpSessTaskSql.processTask(SnpSessTaskSql.java:2913)
    at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java:2625)
    at com.sunopsis.dwg.dbobj.SnpSessStep.treatAttachedTasks(SnpSessStep.java:558)
    at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:464)
    at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:2093)
    at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$2.doAction(StartSessRequestProcessor.java:366)
    at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:216)
    at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.doProcessStartSessTask(StartSessRequestProcessor.java:300)
    at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.access$0(StartSessRequestProcessor.java:292)
    at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$StartSessTask.doExecute(StartSessRequestProcessor.java:855)
    at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:126)
    at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:82)
    at java.lang.Thread.run(Thread.java:662)
    Caused by: java.lang.ClassNotFoundException: com.sap.conn.jco.JCoException
    at java.net.URLClassLoader$1.run(URLClassLoader.java:202)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.net.URLClassLoader.findClass(URLClassLoader.java:190)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:307)
    at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:301)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:248)
    … 21 more

  7. Hi,

    Does it work with ODI_11., the password coming as Null

    java.sql.SQLException: ORA-01005: null password given; logon denied

    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:462)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:397)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:389)
    at oracle.jdbc.driver.T4CTTIfun.processError(T4CTTIfun.java:689)
    at oracle.jdbc.driver.T4CTTIoauthenticate.processError(T4CTTIoauthenticate.java:452)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:481)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:205)
    at oracle.jdbc.driver.T4CTTIoauthenticate.doOAUTH(T4CTTIoauthenticate.java:384)
    at oracle.jdbc.driver.T4CTTIoauthenticate.doOAUTH(T4CTTIoauthenticate.java:811)
    at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:411)
    at oracle.jdbc.driver.PhysicalConnection.(PhysicalConnection.java:678)
    at oracle.jdbc.driver.T4CConnection.(T4CConnection.java:238)
    at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:34)
    at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:567)

  8. Hi All,

    I tried the below query:

    OdiSqlUnload “-FILE=/home/devo/c1144045/error_report.xls” “-DRIVER=oracle.jdbc.OracleDriver” “-URL=jdbc:oracle:thin:@//dbcsfusn01t.corp.apple.com:1700/crmtxnt” “-USER=crmwh_owner” “-PASS=b9yaNYh65vJHlVYtbFESITGOp” “-FILE_FORMAT=VARIABLE” “-FIELD_SEP=,” “-ROW_SEP=\r\n” “-DATE_FORMAT=yyyy/MM/dd HH:mm:ss” “-CHARSET_ENCODING=ISO8859_1” “-XML_CHARSET_ENCODING=ISO-8859-1” “-FETCH_SIZE=5000”

    I am getting the below error:
    java.io.FileNotFoundException: /home/devo/c1144045/error_report.xls (Permission denied)

    How can I actually resolve this

    • You need to write csv or txt file. You cannot create an xls file.
      Make sure u r using the right agent and you have permission in the directory.

  9. Hi,

    Can Record Separator be anything else than ‘rn’ or ‘n’. When I try to give a different value in Record Separatoe (Hexa) like ‘03030D0A’ by default it puts in ODOA as record separator and does not include hex ‘0303’

    Any thoughts on why this would be happening? Also, is there a way to specify end of record marker?


  10. I am able to call the java class with the parameters properly by writing it as Open Tools instead of normal java class. Call in KM works well. So no need to find out how to use java and parameters in KM anymore

    However, still do not know how to use OdiSqlUnload in this java Open Tools, as OdiSqlUnload is not public its methods


  11. Not sure why my comments displayed incorrectly with some special characters.

    I am using java method like this <? java method (closing tag)
    it works, but whenever I put the getFrom with <%, ODI stops processing

    Sorry for spam some missing comments

  12. I supposed the issue is syntax of how to key in the getFrom… into java coding method as ODI stops in that step in Operator.

    I am using
    But when I use getFrom, getSrcTableList with , I do not know how to key in the method into

    Indeed, I do not know when we should use <% and <? and how to combine 2 things together


  13. Not know why my code lines do not display correctly.

    The code displayed above works well with constant parameter (like “TESTTABLE”)

    but I want to pass the value of odiRef.getSrcTablesList, it does not work in KM


    • I am not extactly sure , i would need to look into this and get back to you .
      can’t you have the list with getFrom() or gettsrctablelist() and pass into string and then read the tables one by one .

  14. Why my comments does not display on the webpage?

    This code is working well:

    This code does not work

  15. I am writing the java class to do that

    however, I face the issue to call the java method in my KM (using ODI Tools ODI11g)

    This is working well

    but I am trying pass the parameter instead of constant, like this. But it does not work,


    how can I do this?


  16. That is a good idea, I am writing the java class to do that

    however, I face the issue to call the java method in my KM (using ODI Tools ODI11g)

    This is working well

    but I am trying pass the parameter instead of constant, like this. But it does not work,


    how can I do this?


  17. For some reason, I want to use the odisqlunload in the java class, then calls this class in KM, so that I can hide all above parameters

    Is there any way to write the java class that can use odisqlunload?


    • I am not exactly sure if this way would work , but i believe if you can extract the sunopsis.zip under odi101360oracledilib and then find the odisqlunload.class and call this class in your java code and provide the parameters you are looking for and then call the new java class into your KM. See if this makes any sense to your implementation.

  18. Hi,

    While trying the below –

    OdiSqlUnload “-FILE=test_file.txt” “-DRIVER=” “-URL=” “-USER=” “-PASS=” “-FILE_FORMAT=VARIABLE” “-FIELD_SEP=,” “-ROW_SEP=rn” “-DATE_FORMAT=yyyy/MM/dd HH:mm:ss” “-CHARSET_ENCODING=ISO8859_1” “-XML_CHARSET_ENCODING=ISO-8859-1” “-FETCH_SIZE=5000” “-QUERY=select today_date = getdate()”

    I am getting below error –

    at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTaskPreTrt(Unknown Source)
    at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(Unknown Source)
    at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(Unknown Source)
    at com.sunopsis.dwg.dbobj.SnpSession.treatSession(Unknown Source)
    at com.sunopsis.dwg.cmd.DwgCommandSession.treatCommand(Unknown Source)
    at com.sunopsis.dwg.cmd.DwgCommandBase.execute(Unknown Source)
    at com.sunopsis.dwg.cmd.e.i(Unknown Source)
    at com.sunopsis.dwg.cmd.h.y(Unknown Source)
    at com.sunopsis.dwg.cmd.e.run(Unknown Source)
    at java.lang.Thread.run(Thread.java:595)

    Please help.

  19. Wooooow! How did I don’t ever think about that??? I have some requirements that uses ODISQLUnload… and for each enviroment I pass a lot of variables… I’ll try it right now.

    Best regards!

Leave a Reply

Required fields are marked *.

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