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
November 13, 2018 at 7:24 PM
My ODI 12.2.1.6 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
November 13, 2019 at 9:35 PM
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”
SELECT ERROR_MESSAGE
FROM SNP_LPI_STEP_LOG
WHERE (I_LP_STEP,I_LP_INST) IN
(SELECT I_LP_STEP,I_LP_INST
FROM SNP_LPI_EXC_LOG
WHERE SESS_NO=)
August 22, 2018 at 8:25 AM
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.
November 22, 2017 at 12:25 PM
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?
October 30, 2017 at 6:44 AM
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 ?
October 30, 2017 at 12:17 PM
Hi,
just add the ODI variable like “-FILE=#PROJECT_CODE.VARIABLE”
September 1, 2017 at 8:33 PM
Is there a matching thing for Oracle 12C?
January 27, 2015 at 12:03 PM
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
December 1, 2014 at 11:34 AM
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)
September 13, 2013 at 6:15 AM
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
September 13, 2013 at 6:44 AM
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.
August 3, 2011 at 1:38 PM
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!
March 30, 2011 at 3:40 AM
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
March 29, 2011 at 8:05 PM
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
March 29, 2011 at 8:02 PM
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
March 29, 2011 at 9:00 AM
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
March 29, 2011 at 9:43 AM
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 .
March 29, 2011 at 8:55 AM
Why my comments does not display on the webpage?
This code is working well:
This code does not work
<?com.oracle.odi.TestClass.testMethod("”);?>
March 29, 2011 at 8:57 AM
we need to approve the comments before they get display. sorry its just our security protection to filter out spams .
March 29, 2011 at 8:54 AM
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
March 29, 2011 at 8:51 AM
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
March 29, 2011 at 8:09 AM
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
March 29, 2011 at 8:28 AM
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.
November 11, 2010 at 12:06 PM
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.
November 11, 2010 at 1:34 PM
“-FILE=test_file.txt” please provide the drive name and try again.
September 29, 2010 at 3:45 PM
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!