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,
same source and target Oracle table
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 353
- Joined: Wed Apr 06, 2005 8:45 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.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
-
- Premium Member
- Posts: 353
- Joined: Wed Apr 06, 2005 8:45 am
same source and target Oracle table
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.vinaymanchinila wrote:Thank you guys, will stage the data before I load.
You will not get into locking situation.