Inserting into multiple tables 1

[TGJ]

Hello, I have a situation where I need to insert into 2 tables that are related through ID and V3DETAILKEY.

The tables are:
V3DETAILSERVICE (ID VARCHAR(9), V3DETAILKEY VARCHAR(20), SERVICE NUMBER(7,4))

V3DETAILSALARY (ID VARCHAR(9), V3DETAILKEY VARCHAR(20), SALARY NUMBER(9,2))

So, the ID is 000001, SERVICE is 12.00, SALARY is 2200.00, V3DETAILKEY is automatically generated. Now if I was just going to insert into each table separately this is how I would do it:

INSERT INTO V3DETAILSERVICE
VALUES ('000001', V3DETAILKEY_SEQ.NEXTVAL, 12.00)

INSERT INTO V3DETAILSALARY
VALUES ('000001', V3DETAILKEY_SEQ.NEXTVAL, 2200.00)

However I need to update both tables with the same V3DETAILKEY and I am not sure how to do this.

Can anyone help me? Thanks in advance.

 

[SantaMufasa]

Yes, TGJ, you can accomplish what you want with a slight adjustment to your code: For whatever is the second INSERT statement, use the "<sequence>.currval" pseudo-column:

INSERT INTO V3DETAILSERVICE
VALUES ('000001', V3DETAILKEY_SEQ.NEXTVAL, 12.00)

INSERT INTO V3DETAILSALARY
VALUES ('000001', V3DETAILKEY_SEQ.[b]CURRVAL[/b], 2200.00)

The ".currval" expression provides the same value of the sequence that was generated previously. (You can only use the ".currval" expression within a session after you have used the ".nextval" expression.)


Let us know if this resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[TGJ]

Yes, that will work, thanks!

Do you know if there is a way to lock the tables you are updating to prevent other users from updating them.

 

[SantaMufasa]

You do not want to have Oracle lock any more rows than the row(s) which you are actually updating...Then you want to COMMIT your changes as soon as reasonable so that you do not prevent others from getting their work done. If you are afraid of someone else using the same sequence number that you are using, that just is not going to happen (Oracle doesn't allow that to happen.)

If you have "locking" concerns that my above explanation does not address, please post a follow-up reply.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[TGJ]

My concern is that if I insert into V3DETAILSERVICE and then someone else inserts into V3DETAILSERVICE before I insert into V3DETAILSALARY, I will then be using the wrong V3DETAILKEY when I do insert into V3DETAILSALARY.

I thought I could lock both tables then do my inserts and then unlock the tables. Is this ok?

Thanks.

 

[SantaMufasa]

Nope...You do not need/want to lock tables for any row-level activities, especially INSERTs. Once you (in your session) INSERT into "V3DETAILSERVICE", using "V3DETAILKEY_SEQ.NEXTVAL":

a) no one else/no other session can obtain the same sequence value that you received for "V3DETAILKEY_SEQ.NEXTVAL".
b) no one else/no other session will receive your value for "V3DETAILKEY_SEQ.CURRVAL".

Think of discrete values in both "V3DETAILKEY_SEQ.NEXTVAL" and "V3DETAILKEY_SEQ.CURRVAL" as locked for your session. Even if you have another window with a connection to Oracle as the same user, that session is completely separate from your first session. Oracle guarantees that it will keep values separate and invisible between sessions until a transaction issues a COMMIT.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[TGJ]

Excellent, thank you!