ODIExperts.com

The blog for Oracle Data Integrator ( ODI )

How to refresh ODI variables from file – Part 1 – Just one value

Dear all,

Get value or values from file is a common question in ODI then, in the following lines and examples, I will try to show how simple is to achieve this!

First of all, what is the problem description?

1) A flat file with just one value that need to be upload into a ODI variable

2) The flat file can or can’t have Header name (I used the case where there is a column header).

Example:

File: my_test_file.txt

Content:

Country_number
123456789

 

OK, now to explain how this work in ODI I will use a small trick:

1) Create a datastore to the file with the following characterists:

  • Read to the file  (c:temp my_test_file.txt in my case)
  •  one column (C1 in my case) and datatype String. I know that the file has a number content but let the column as String is intentional.
  • 1 line as header

2) Import the LKM File to SQL

3) Create an interface that maps the datastore, as source, to any  target, doesn’t matter if the interface works or not, it just need to be started in the Operator. At true, we’re “hunting” some code generation… Make sure that the imported LKM (step 2) was used in the interface

4) Go to operator, open the interface execution, go to “Load Data” step, Description tab and copy all content of the superior box. You should get something like:  

Copy the selected texto to your variable

Copy the selected texto to your variable

All of this was done to get that generated code, the file JDBC. It looks a little confuse but I will explain each one of its parameters in a new post.

 5) Now all that is necessary is copy the code into a refresh variable and “Voila”! Now you can refresh your variable from a file! Just drag the variable into a package…

This is how the variable should looks like:

Now just use the generated code at your variable!

Now just use the generated code at your variable!

Best Regards to all!

Cezar Santos

18 Comments

  1. Awesome Technique 🙂

  2. File in unix and need to pass file count to ODI variable,
    os command executing successfully: wc -l /data/test.csv, but it’s not writing any where please let me know how to pass os command results to ODI variable.

  3. Hi, file in unix and need to pass file count to ODI variable, os command : wc -l /data/test.csv, please let me know how to pass os command results to ODI variable or any other alternative way. I tried above one which was mentioned by Cezar but it’s gave an error: ORA-00906: missing left parenthesis

  4. Did this top working in ODI 11 and above….currently trying 12c and it returns an error.

  5. I have tried to create a variable refresh as stated above but when I execute the code it says missing left Parenthesis. The ODI load of the 1 record with 1 field into a table works fine but I cant use the select statement generated by ODi in a variable refresh or in SQLdeveloper.\\

    Any ideas what could be wrong?

    select FILENAME C1_FILENAME
    from TABLE
    /*$$SNPS_START_KEYSNP$CRDWG_TABLESNP$CRTABLE_NAME=File_inSNP$CRLOAD_FILE=d:\IntData/File_in.txtSNP$CRFILE_FORMAT=DSNP$CRFILE_SEP_FIELD=0x002cSNP$CRFILE_SEP_LINE=0x000D0x000ASNP$CRFILE_FIRST_ROW=0SNP$CRFILE_ENC_FIELD=”SNP$CRFILE_DEC_SEP=SNP$CRSNP$CRDWG_COLSNP$CRCOL_NAME=FILENAMESNP$CRTYPE_NAME=STRINGSNP$CRORDER=1SNP$CRLENGTH=100SNP$CRPRECISION=100SNP$CRSNP$CRDWG_COLSNP$CRCOL_NAME=Second_ColumnSNP$CRTYPE_NAME=STRINGSNP$CRORDER=2SNP$CRLENGTH=100SNP$CRPRECISION=100SNP$CR$$SNPS_END_KEY*/;

  6. Hello- I followed the steps described in this post to create a variable with the generated code. However, when I try to test or refresh the variable, I get the following error: Error while executing query: ORA-00906: missing left parenthesis
    Here is the generated code I am using:
    select C1 C1_C1
    from TABLE
    /*$$SNPS_START_KEYSNP$CRDWG_TABLESNP$CRTABLE_NAME=File_ServicesCheckSNP$CRLOAD_FILE=//server1/ODIFiles//ServicesHyperionCheck.logSNP$CRFILE_FORMAT=DSNP$CRFILE_SEP_FIELD=09SNP$CRFILE_SEP_LINE=0D0ASNP$CRFILE_FIRST_ROW=1SNP$CRFILE_ENC_FIELD=SNP$CRFILE_DEC_SEP=SNP$CRSNP$CRDWG_COLSNP$CRCOL_NAME=C1SNP$CRTYPE_NAME=STRINGSNP$CRORDER=1SNP$CRLINE_OFFSET=1SNP$CRLENGTH=75SNP$CRPRECISION=75SNP$CR$$SNPS_END_KEY*/

  7. Hi Kshitiz,

    The double quotes is a mistake. (although it returns this error when I change the code to CRLOAD_FILE=C:ODIWORKING_DIR/”#GET_INTERFACE”)

    The error generated using single quotes:
    CRLOAD_FILE=C:ODIWORKING_DIR/’#GET_INTERFACE’
    is
    File not found: C:ODIWORKING_DIR/’Mor12.txt’

    If I hardcode the filename, then it works perfectly e.g.
    CRLOAD_FILE=C:ODIWORKING_DIR/Mor12.txt

    However, because I am running several files through a single interface, I want to be able to dynamically get the current file.

  8. This works fine for one file. However i am processing several files using the same interface. In this case, the code generated by the LKM is slightly different as it refers to the name of the variable GET_INTERFACE as follows:
    CRLOAD_FILE=C:ODIWORKING_DIR/#GET_INTERFACE
    On execution, I get :

    File not found: C:ODIWORKING_DIR/#GET_INTERFACE.

    I have tried changing the code to
    CRLOAD_FILE=C:ODIWORKING_DIR/’#GET_INTERFACE’
    but i get this error:

    File not found: C:ODIWORKING_DIR/”Mor12.txt”

    It correctly identifies the current file, but cannot interprete the directory path.

    Any ideas?

    Cheers

    • does ODI have read permission to that folder and make sure you are reading the filer correctly. Here in your example its with double quote , hope ODI is not looking for double quotes file and raising error. Please check for that too.

  9. Hi,
    I am using a variable(Dataype: Text,Action: Latest Value) in a package(say pack1) to retrieve the value of a directory path from an oracle table and write a file in that path.I have generated a scenario of pack1 and used it in another package(say pack2).This pack2’s scenario is called from another master package.
    Now I am observing that whenever I am updating the content of that column (directory path )of the oracle table and executing the master package the variable’s value is changed to the new path value but the file is being written in the path referring to the previous value.If I execute this package once again only then the file is written in the new updated path.I am unable to find out the reason for this behaviour of the variable..Please help…

    Thanks in advance,
    Parama

  10. Hey i have an issue over here in ODI 10.1.3.6.4 version. The issue is, I have double clicked a scenario and from the execution tab i tried executing the scenario. Then I clicked OK button and try to write a string value for the parameter. but letters are not enable and only allows me to write numbers. where in when i looked into the appropriate variable , the datatype says Text and Action says Historize. Can you please help me why its not allowing the characters when the datatype shows text
    Please help me
    Thanks in advance… Venkat

  11. Hey cezar,
    how r u?
    i have one question, if file have more then 1 row, how get the row count (number of record) in file??

  12. I can´t see the code image, please put the text image thanks

  13. Hi Rathish,

    Yes, it is possible to create a loop and “refresh” the lines from flat file…

    That will be the “How to refresh ODI variables from file – Part 2 – More than one value” post…

    I intend to release it this week!

    Good to see you here!

  14. Hi Cezar,

    Good to see new Posts here.

    This is a very useful post which we can implement in our real time projects for the replacement of temperory tables with a single value column (like Lock status, Current User ) etc ….

    Also one question :-
    Can we use update command to update the value of this column (in file)…
    ( I know before asking a question like this, I need to try myself 🙂 , any way I will try this by 2mrw and will update this reply. )

    And Thanks for your posts ……

    Regards,
    Rathish

Leave a Reply

Required fields are marked *.