lunedì 1 ottobre 2012

Tip: Prompting Variables in ODI 11g

Sometimes you may need to ask the user to input a value for your variable.
You can do it in ODI 11g by first declaring your variable and then placing it in a package using the option Declare.

Then create a scenario of the package and set the variable as input parameter.

When you execute the scenario, you will  be prompted for the variables you selected.

Tip: Weird TO_NUMBER() behaviour in ODI

Today I have come into a very weird behaviour of TO_NUMBER function in ODI which almost drove me mad.
I had a .csv file semi-colon separated to be loaded into an Oracle table with the following structure:

Source file:


Target Table:

I have created a very simple interface for moving data from file to the table. I tried using TO_NUMBER(REPLACE(UNIT_COST,'.',',')), but I was getting the following error:

ORA-01722: invalid number

I then tried using the NLS_NUMERIC_CHARACTERS for telling to the TO_NUMBER function to interpret the point as the decimal separator. 

 to_number(UNIT_COST,'999999999999999999999999999999999999D9999999999999999' ,'NLS_NUMERIC_CHARACTERS =''.,''')

And I got it working!!! 

For more info, see: