ETL into operational oracle database - used by jsp/spring/hibernate app

I am needing to have some legacy data loaded into an operational oracle (11gR2) database. The database is being used by a jsp/spring/hibernate (3.2.5.ga) applicat开发者_开发问答ion. A sequence is used for generating unique-keys across all the tables. the sequence definition is as below:

CREATE SEQUENCE  "TEST"."HIBERNATE_SEQUENCE"  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1000 CACHE 20 NOORDER  NOCYCLE 

The idea for the data load/ETL is to come up wtih a script that starts out with the max sequence value by running

   select HIBERNATE_SEQUENCE.NEXTVAL from dual

at the beginning of the script generation process - and generated SQL Insert statements for the data that needs to be populated. there is some logic involved in handling data cleanup, business rules etc that get applied applied through the script and the generated SQL Insert statements are expected to be run in one batch and that should be able to bring in all of the legacy data.

assuming that the max sequence value was 1000 - the script uses this as as variable and increments is as necessary, and the output SQL INSERTS will be as below:

INSERT INTO USER_STATUS(ID, CREATE_DATE, UPDATE_DATE, STATUS_ID, USER_ID)
VALUES (**1001**, CURRENT_DATE, CURRENT_DATE, 20, 445);

INSERT INTO USER_ACTIVITY_LOG(ID, CREATE_DATE, UPDATE_DATE, DETAILS, LAST_USER_STATUS_ID)
VALUES (**1002**, CURRENT_DATE, CURRENT_DATE, 'USER ACTIVITY 1', **1001**);

INSERT INTO USER_STATUS(ID, CREATE_DATE, UPDATE_DATE, STATUS_ID, USER_ID)
VALUES (**1003**, CURRENT_DATE, CURRENT_DATE, 10, 445);

INSERT INTO USER_ACTIVITY_LOG(ID, CREATE_DATE, UPDATE_DATE, DETAILS,  LAST_USER_STATUS_ID)
VALUES (**1004**, CURRENT_DATE, CURRENT_DATE, 'USER ACTIVITY 3', **1003**);

I have created some mock SQL to show the idea of how the output INSERTS are going to be - there are going to be a lot more tables involved in the insert operations. whenever we need to make data changes from the back-end we would use the HIBERNATE_SEQUENCE.NEXTVAL to get the next unique key value. but since the sql generation script runs in a disconnected mode, it does not use the HIBERNATE_SEQUENCE.NEXTVAL, but tries to increment a local variable instead.

The assumption we are having about being able to generate (and run) this script is to

1. have the application taken down for maintenance
2. have no database activity during the time of running the script and start out with the max sequence value.
3. generate the SQL

4. run the SQL - commit.

5. assuming that, in the process of script generation, the max sequence value goes up from 1000 to 5000 - after the script is run and the data is loaded, the HIBERNATE_SEQUENCE would need to dropped/created to start at 5001.

6. bring the application back up.

Now, to the reason i am posting this, in such detail... i am needing your suggestions/input about any loopholes in this design and if there is anything i am overlooking.

Any input is appreciated.

Thanks!

I would suggest against dropping and creating the sequence if its used for any other task in your application, doing so means you also need to re-add any permissions, synonyms,etc.

Do you know at the start of the script how many inserts you will do? If so, and assuming that you wont have any other activity, then you can adjust the 'increment by' value of the sequence , so a single select from it will move the sequence forward by whatever value you want.

> drop sequence seq_test;
sequence SEQ_TEST dropped.
> create sequence seq_test start with 1 increment by 1;
sequence SEQ_TEST created.
> select seq_test.nextval from dual;
NEXTVAL                
---------------------- 
1                      

> alter sequence seq_test increment by 500;
sequence SEQ_TEST altered.
> select seq_test.nextval from dual;
NEXTVAL                
---------------------- 
501                    

> alter sequence seq_test increment by 1;
sequence SEQ_TEST altered.
> select seq_test.nextval from dual;
NEXTVAL                
---------------------- 
502    

Just be aware that the DDL statements will issue an implicit commit, so once they have run any inflight transaction will be commited, and any work performed after them will be a separate transaction.