I've been trying to apply a transformation in a data loader process that is essentially adding a new column to the loaded data. In other words, the data that I want to "transform" isn't in the source file. I've got the logic figured out to get the correct value into the Apex session/application item but it seems that the transformation rule never runs.
I've tried to use both the "SQL query (return one value)" option and the "PL/SQL Expression" options. For the SQL option I used:
select scan_load_log_seq.nextval from dual;
and for the PL/SQL expression option is used:
:G_SCAN_LOG_ID
where that is an application level item that has been verified to contain the proper value at the time (after the first page has been submitted).
There are several unanswered questions here on the forum on this same topic, one of which I replied to myself, apparently incorrectly I should add. :-/ The fundamental question seems to be this: can you create transformation rules and successfully apply them to columns in the target table when that column of data isn't in the source data itself?
Here's part of the rub I suppose. It appears that much of the data loader operations are predicated on the columns found in the source data. The loader is using an Apex collection, which has some 50 available varchar2 columns, and you only see those that have been populated from the original source file. But then when you get to the transformation rules Apex exposes all of the columns in the target table, making it appear that you can apply transformations to any of those columns, regardless of whether they were in the source file or not.
Apex version: 4.2.5.00.08
Oracle version: 11.2.0.3
I'd really like to get a response from someone on the Apex product team, but if anyone can help out with this situation that would be appreciated.
Earl