ODIExperts.com

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

23 Comments

  1. 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?

  2. 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 ?

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

  4. 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

  5. Hi,

    Does it work with ODI_11.1.1.6.0, 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)

  6. 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”
    select * from CRMWH_OWNER.XXAPL_SA_ERROR_TABLE

    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.

  7. 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?

    Thanks!

  8. 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

    Thanks
    Tuan

  9. 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

  10. 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

    thanks
    Tuang

  11. 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

    thanks
    Tuan

    • 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 .

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

    This code is working well:

    This code does not work
    <?com.oracle.odi.TestClass.testMethod("”);?>

  13. 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,

    <?com.oracle.odi.TestClass.testMethod("”);?>

    how can I do this?

    thanks
    Tuan

  14. 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,

    <?com.oracle.odi.TestClass.testMethod("”);?>

    how can I do this?

    thanks
    Tuan

  15. 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?

    thanks
    Tuan

    • 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.

  16. 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 –

    java.lang.NullPointerException
    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.

  17. 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 *.