same source and target Oracle table

vinaymanchinila · Post by **vinaymanchinila** » Mon Feb 12, 2007 9:54 am

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,

DSguru2B · Post by **DSguru2B** » Mon Feb 12, 2007 9:58 am

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.

kcbland · Post by **kcbland** » Mon Feb 12, 2007 10:05 am

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.

vinaymanchinila · Post by **vinaymanchinila** » Mon Feb 12, 2007 10:44 am

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

pbatchu · Post by **pbatchu** » Wed Feb 14, 2007 4:06 pm

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.