StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POETL into operational oracle database - used by jsp/spring/hibernate app
text
Body
copied!<p>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) application. A sequence is used for generating unique-keys across all the tables. the sequence definition is as below:</p> <pre><code>CREATE SEQUENCE "TEST"."HIBERNATE_SEQUENCE" MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1000 CACHE 20 NOORDER NOCYCLE </code></pre> <p>The idea for the data load/ETL is to come up wtih a script that starts out with the max sequence value by running </p> <pre><code> select HIBERNATE_SEQUENCE.NEXTVAL from dual </code></pre> <p>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.</p> <p>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:</p> <pre><code>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**); </code></pre> <p>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.</p> <p>The assumption we are having about being able to generate (and run) this script is to </p> <ol> <li>have the application taken down for maintenance</li> <li>have no database activity during the time of running the script and start out with the max sequence value. </li> <li>generate the SQL</li> <li><p>run the SQL - commit. </p></li> <li><p>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.</p></li> <li><p>bring the application back up.</p></li> </ol> <p>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. </p> <p>Any input is appreciated. </p> <p>Thanks!</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload