1203271 - How to assign an Oracle date or timestamp value to a Data Integrator datetime variable using the SQL() function

In BusinessObjects Data Integrator XI Release 2, mapping the output of the request to a SQL() function that selects a date with time or from a timestamp column directly to a datetime variable will assign NULL to the variable.

For example, when selecting a date value from Oracle as follows ($y is defined in Data Integrator as a datetime variable)

$y = sql('datastore_name', 'select sysdate from dual' );
print($y);
print('datetime y from dual is ' || $y);

the following output is produced:

8056 7120 PRINTFN 12/27/2007 9:59:33 AM NULL
8056 7120 PRINTFN 12/27/2007 9:59:33 AM datetime y from dual is

Selecting a datetime value from Oracle as follows ($t is defined in Data Integrator as a datetime variable and foo.col2 is defined as timestamp in Oracle)

$t = sql('datastore_name', 'select to_char(col2, \'DD-MON-YY HH24.MI.SS.FF\') from foo ');
print('timestamp t from foo is ' || $t);

produces the following output:

8056 7120 PRINTFN 12/27/2007 9:59:33 AM timestamp t from foo is

variable datetime timestamp , 6785760 , KBA , EIM-DI , Data Integrator, Composer , How To