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

9 Comments

Leave a Reply

Required fields are marked *.


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