Hi everyone,
After what was showed in the “Part 1” now I will show how to loop thru a text file one line at once until the last.
At true, it is a simple trick, let start….
- Follow all information from “Part 1”
- Create a new variable (I named it as vLineNumber) with the following code at refresh tab (Oracle code):
select #vLineNumber + 1 from dual
Choose any Oracle schema for execute the query. It will look like:
- Go back to the variable created at “Part 1” and change the attribute SNP$CRFILE_FIRST_ROW to:
SNP$CRFILE_FIRST_ROW=#vLineNumber
The code will looks like:
- Now is just create a package and put the variables together like
- Drag and drop the vLineNumber in set mode setting the value to 0 (zero) – First Step
- Drag and drop the variable vLineNumber once again in refreshing mode
- Drag and drop vReadFile in refreshing mode
- Drag and drop any ODI object that uses the variable like Procedure, Interface, etc.
- The loop will end with an error when the file reachs the end, then put a KO line from the vReadFile step like:
Well, it’s done. Now is just use the technique!
Any comment, just ask me!
Cezar Santos
February 4, 2016 at 3:08 AM
The above code is working fine, I found another way to copy entire file in one go. Use different charectors for line separator and record separator.
ie., $CRFILE_SEP_FIELD=09SNP$CRFILE_SEP_LINE=0B
July 6, 2015 at 10:44 AM
hi Kshitiz,
When refreshing the variable :READ_VARIABLE , it throws the below error:-
org.apache.bsf.BSFException: The application script threw an exception: com.sunopsis.tools.core.exception.SnpsSimpleMessageException: Exception getTargetTable(“METADATA_DESC”) : Exception getTargetTable(“METADATA_DESC”) : ODI-15081: There is no Target Table BSF info: test_variable at line: 0 column: columnNo
at bsh.util.BeanShellBSFEngine.eval(Unknown Source)
at bsh.util.BeanShellBSFEngine.exec(Unknown Source)
at com.sunopsis.dwg.codeinterpretor.SnpCodeInterpretor.transform(SnpCodeInterpretor.java:483)
at com.sunopsis.dwg.codeinterpretor.SnpGenerator.treatSQLTxt(SnpGenerator.java:2387)
at com.sunopsis.dwg.codeinterpretor.SnpGenerator.treatVarTxt(SnpGenerator.java:1295)
at com.sunopsis.dwg.codeinterpretor.SnpGeneratorSQLCIT.treatGenVar(SnpGeneratorSQLCIT.java:8460)
at com.sunopsis.dwg.codeinterpretor.SnpGeneratorSQLCIT.treatJobVar(SnpGeneratorSQLCIT.java:11643)
at com.sunopsis.dwg.codeinterpretor.SnpGeneratorSQLCIT.mainGenVarInternal(SnpGeneratorSQLCIT.java:4411)
at com.sunopsis.dwg.codeinterpretor.SnpGeneratorSQLCIT.mainGenVar(SnpGeneratorSQLCIT.java:4303)
at com.sunopsis.graphical.dialog.SnpsDialogExecution.doVariableExecuter(SnpsDialogExecution.java:1356)
at com.sunopsis.graphical.frame.edit.EditFrameSnpVar.handleRefreshVariableAction(EditFrameSnpVar.java:1067)
at com.sunopsis.graphical.frame.edit.EditFrameSnpVar.access$200(EditFrameSnpVar.java:118)
at com.sunopsis.graphical.frame.edit.EditFrameSnpVar$4.actionPerformed(EditFrameSnpVar.java:491)
at com.sunopsis.graphical.component.toolbar.OdiTextAreaToolbarAction.executeAction(OdiTextAreaToolbarAction.java:125)
at com.sunopsis.graphical.component.toolbar.AbstractOdiToolbarAction.actionPerformed(AbstractOdiToolbarAction.java:204)
at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:2018)
at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2341)
at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:402)
at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:259)
at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:252)
at java.awt.AWTEventMulticaster.mouseReleased(AWTEventMulticaster.java:290)
at java.awt.Component.processMouseEvent(Component.java:6516)
at javax.swing.JComponent.processMouseEvent(JComponent.java:3320)
at java.awt.Component.processEvent(Component.java:6281)
at java.awt.Container.processEvent(Container.java:2229)
at java.awt.Component.dispatchEventImpl(Component.java:4872)
at java.awt.Container.dispatchEventImpl(Container.java:2287)
at java.awt.Component.dispatchEvent(Component.java:4698)
at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4832)
at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4492)
at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4422)
at java.awt.Container.dispatchEventImpl(Container.java:2273)
at java.awt.Window.dispatchEventImpl(Window.java:2719)
at java.awt.Component.dispatchEvent(Component.java:4698)
at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:735)
at java.awt.EventQueue.access$200(EventQueue.java:103)
at java.awt.EventQueue$3.run(EventQueue.java:694)
at java.awt.EventQueue$3.run(EventQueue.java:692)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:76)
at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:87)
at java.awt.EventQueue$4.run(EventQueue.java:708)
at java.awt.EventQueue$4.run(EventQueue.java:706)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:76)
at java.awt.EventQueue.dispatchEvent(EventQueue.java:705)
at oracle.javatools.internal.ui.EventQueueWrapper._dispatchEvent(EventQueueWrapper.java:169)
at oracle.javatools.internal.ui.EventQueueWrapper.dispatchEvent(EventQueueWrapper.java:151)
at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:242)
at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:161)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:150)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:146)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:138)
at java.awt.EventDispatchThread.run(EventDispatchThread.java:91)
Could you please let me know any solution for this
Thanks
January 23, 2015 at 4:56 AM
Hi Ranjeet,
I am facing the same error as you were:
java.lang.NumberFormatException: For input string: “‘#vLineNumber'”
at java.lang.NumberFormatException.forInputString(NumberFormatException.java:48)
at java.lang.Integer.parseInt(Integer.java:449)
at java.lang.Integer.parseInt(Integer.java:499)
at com.sunopsis.jdbc.driver.file.TableDesc.getNbHeadingRows(TableDesc.java:245)
at com.sunopsis.jdbc.driver.file.FileResultSet.(FileResultSet.java:140)
at com.sunopsis.jdbc.driver.file.impl.commands.CommandSelect.execute(CommandSelect.java:57)
at com.sunopsis.jdbc.driver.file.CommandExecutor.executeCommand(CommandExecutor.java:33)
at com.sunopsis.jdbc.driver.file.FilePreparedStatement.executeQuery(FilePreparedStatement.java:135)
at oracle.odi.query.JDBCTemplate.executeQuery(JDBCTemplate.java:189)
at oracle.odi.runtime.agent.execution.sql.RefreshVariableExecutor.execute(RefreshVariableExecutor.java:54)
at oracle.odi.runtime.agent.execution.sql.SQLExecutor.execute(SQLExecutor.java:1)
at oracle.odi.runtime.agent.execution.TaskExecutionHandler.handleTask(TaskExecutionHandler.java:50)
at com.sunopsis.dwg.dbobj.SnpSessTaskSqlV.processTask(SnpSessTaskSqlV.java:410)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java:2625)
at com.sunopsis.dwg.dbobj.SnpSessTaskSqlV.treatTask(SnpSessTaskSqlV.java:240)
at com.sunopsis.dwg.dbobj.SnpSessStep.treatAttachedTasks(SnpSessStep.java:577)
at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:468)
at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:2128)
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)
Did you find any solution for it.
Thanks
Sumit.
September 27, 2019 at 1:11 PM
Same issue occurred to me. I forgot the add space after #vLineNumber
November 5, 2013 at 2:05 PM
Hi Ranjet,
I just tested and still work on 11.1.1.7…
September 11, 2014 at 1:52 PM
Sorry Cezar & Kshitiz,
I’m using ODI 11.1.1 – Build ODI_11.1.1.7.0_GENERIC_130302.2156.
I put the inputfile in the work dir of the phisical schema, so ODI has all permissions for read and write, but however running this sample I have a java forma number error when currFileName accesses to vLineNumber.
October 14, 2013 at 7:40 AM
when variable script is replace (i.e SNP$CRFILE_FIRST_ROW=#vLineNumber) it gives error. this code is not useful any more
java.lang.NumberFormatException: For input string: “1 $CRFILE_ENC_FIELD=1 $CRFILE_DEC_SEP=”
at java.lang.NumberFormatException.forInputString(NumberFormatException.java:59)
at java.lang.Integer.parseInt(Integer.java:467)
at java.lang.Integer.parseInt(Integer.java:508)
at com.sunopsis.jdbc.driver.file.TableDesc.getNbHeadingRows(TableDesc.java:245)
at com.sunopsis.jdbc.driver.file.FileResultSet.(FileResultSet.java:140)
at com.sunopsis.jdbc.driver.file.impl.commands.CommandSelect.execute(CommandSelect.java:57)
at com.sunopsis.jdbc.driver.file.CommandExecutor.executeCommand(CommandExecutor.java:33)
at com.sunopsis.jdbc.driver.file.FilePreparedStatement.executeQuery(FilePreparedStatement.java:135)
at oracle.odi.query.JDBCTemplate.executeQuery(JDBCTemplate.java:189)
at oracle.odi.runtime.agent.execution.sql.RefreshVariableExecutor.execute(RefreshVariableExecutor.java:54)
at oracle.odi.runtime.agent.execution.sql.SQLExecutor.execute(SQLExecutor.java:1)
at oracle.odi.runtime.agent.execution.TaskExecutionHandler.handleTask(TaskExecutionHandler.java:50)
at com.sunopsis.dwg.dbobj.SnpSessTaskSqlV.processTask(SnpSessTaskSqlV.java:410)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java:2625)
at com.sunopsis.dwg.dbobj.SnpSessTaskSqlV.treatTask(SnpSessTaskSqlV.java:240)
at com.sunopsis.dwg.dbobj.SnpSessStep.treatAttachedTasks(SnpSessStep.java:577)
at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:468)
at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:2128)
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:735)
May 19, 2011 at 7:32 AM
Hi Cezar,
We have a situation where we have a file ,and it has first line as header , details and then trailer.
We need to have all the 3 records seperated in three different table. However we donot have and record identifier. Is it possible to process the file row b y row .S o that we can dump the data into table and identify that first row is header and last one as trailer. Or in case of subheader’s assign sequence through sequence generator and identify them
May 20, 2011 at 4:58 AM
Hi Prakash,
There are 2 ways i can think of
You can use a Jython or Java script and insert first line into one table and second into second and so on
(or)
Load the file into temp table with sequence and now you can load first sequence(namely first line ) into first table and so on.
Thanks
Kshitiz Devendra