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:

20120501;Brand_X;10.6

Target Table:
CREATE TABLE TB
(DATE_ID VARCHAR2(8),
 BRAND_ID VARCHAR2(5),
 UNIT_COST NUMBER);

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: 

http://docs.oracle.com/cd/B28359_01/olap.111/b28126/dml_functions_2117.htm