ODIExperts.com

The blog for Oracle Data Integrator ( ODI )

How to refresh ODI variables from file – Part 2 – Getting all lines, once at time

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:

vLineNumber ODI variable

vLineNumber ODI variable

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

new vReadFile code. Remember to let a single space after the variable nome

new vReadFile code. Remember to let a single space after the variable name

 

 

 

 

 

 

 

 

  • Now is just create a package and put the variables together like
    1. Drag and drop the vLineNumber in set mode setting the value to 0 (zero) – First Step
    2. Drag and drop the variable vLineNumber once again in refreshing mode
    3. Drag and drop vReadFile in refreshing mode
    4. Drag and drop any ODI object that uses the variable like Procedure, Interface, etc.
    5. The loop will end with an error when the file reachs the end, then put a KO line from the vReadFile step like:
That is the package to read a file line by line and uses it in any code

That is the package to read a file line by line and uses it in any code

Well, it’s done. Now is just use the technique!
 
Any comment, just ask me!
 
 
Cezar Santos

8 Comments

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

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

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

  4. Hi Ranjet,

    I just tested and still work on 11.1.1.7…

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

  5. 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)

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

    • 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

Leave a Reply

Required fields are marked *.