Inthe Type 2 SCD approach ,where you need to look for the existence of data in the target before inserting, if you use set-based, and run the package you will get an ORA-04091 error stating "Table ‘XXX’ is mutating, trigger/function may not see it." This error occurs because you are inserting into, and the function is selecting from, the target table in the same set-based statement. (fig1)
Though ,the mapping will function properly when deployed as a row-based package, the performance of the row-based implementation may be unacceptable when considering the volume of rows being processed and the size of the ETL window.
So, an alternative approach is available that will allow you to deploy the mapping using a set-based implementation. First, you will need to create a staging table that looks exactly like the target table; then, substitute the staging table for the target table in the mapping. The Type 2 SCD function will still do the lookup on the target table, but the inserts will be redirected to the staging table. A second mapping will then be required that inserts the new rows in the staging table into the target table.