Surrogate Key generation in Oracle 9I
Posted: Tue Sep 20, 2005 4:27 am
Hi,
We are using Datastage V7.5 (Unix). The current requirement is to load an Oracle table in Datastage parallel job.
The column which is to be generated for next higher number is a Primary Key column that is INDEXED. Also there is a sequence defined on this column in Oracle.
How to insert a row into this Oracle table taking care of this Surrogate Key column.
One way i think is to get a stage variable in Transformer which represents the maximum value of that surrogate key column. The value of this variable can be increased by 1.
As an alternative, i tried to map all the columns except this column in Tranformer and then this columns were passed on to Oracle Bulk Loader.
But this didn't work
Is there a better way with Oracle Bulk Loader or Enterprise Stage. Either stage will do as the number of records that will be inserted are in the range of few thousands.
Please guide. Syntax will be v. useful
Thanks in advance.
Regards,
Dhiren
We are using Datastage V7.5 (Unix). The current requirement is to load an Oracle table in Datastage parallel job.
The column which is to be generated for next higher number is a Primary Key column that is INDEXED. Also there is a sequence defined on this column in Oracle.
How to insert a row into this Oracle table taking care of this Surrogate Key column.
One way i think is to get a stage variable in Transformer which represents the maximum value of that surrogate key column. The value of this variable can be increased by 1.
As an alternative, i tried to map all the columns except this column in Tranformer and then this columns were passed on to Oracle Bulk Loader.
But this didn't work
Is there a better way with Oracle Bulk Loader or Enterprise Stage. Either stage will do as the number of records that will be inserted are in the range of few thousands.
Please guide. Syntax will be v. useful
![Smile :)](./images/smilies/icon_smile.gif)
Thanks in advance.
Regards,
Dhiren