DSXchange

Hi,
I need to desing a job which reads from Oracle source table and loads the same Oracle table with some transformations done on some columns, can we do that or will be locked or go into a cycle mode.

For example:

Source: Table A

Col1 , col2, code
st1, 10, a
st2, 20, a
st3, 40, a

Target: Table A (Append)

Col1 , col2, code
st1, 10, b
st2, 20, b
st3, 40, b

I need to do some transforamtions on couple of columns and insert new rows, the key columns would be col1 and col3. I would be replacing col3 to 'b'.

I know this can be done in many ways, can I do it like read, have a transformer to do transformations and load the target?

Thanks,

Oracle has row level locking so you should be fine if you proceed. But a better design would be to unload the table into a flat file/dataset and proceed from there.

You must not use DIRECT path loading, only DML. DIRECT path will lock the table. Consider staging to disk somewhere between Extraction and Loading, as you won't have a restartable process otherwise. In addition, consider that a Server solution using OCI-->XFM-->OCI has the same issues with restartability.

Thank you guys, will stage the data before I load.

vinaymanchinila wrote:Thank you guys, will stage the data before I load.

You can do this. When you use Oracle Enterprise stage, Oracle reads the data at the point of time, in other words, you are reading the snapshot of the data, when you issued the SQL statement. You can update the same table with the data.

You will not get into locking situation.