CODEWITHSUNDEEP
sqloracle-databasestored-procedurescommitoracle12c
Reeya Oberoi
Reeya Oberoi

Reputation: 859

Do I need multiple commits in my stored procedure while inserting data in TABLE A and using that data to insert in TABLE B

I have a stored procedure in which I am inserting data in Table T1. Then based on the inserted data in Table T1, I have to insert data in Table T2 (see below code). My question is - Do I need to use the commit; twice (after inserting data in Table T1 and then again after inserting in Table T2) or can I just use it once in the end and I will be join on the data inserted in Table T1 within that session of procedure while inserting data in Table T2?

CREATE OR REPLACE PROCEDURE FOO
AS
BEGIN

INSERT INTO T1 (ID, NAME, PHONE) 
SELECT ID,
NAME,
PHONE
FROM CUST;

COMMIT; --Do I really need to use this commit here?

INSERT INTO T2 (ID,NAME,CITY)
SELECT C.ID,
C.NAME,
C.CITY
FROM CUST C
JOIN T1 X
ON X.ID = C.ID

COMMIT;

END FOO;

Upvotes: 1

Views: 1219

Answers (1)

Vani S
Vani S

Reputation: 384

No. You don't have to put that first commit. The second commit does the job.

Upvotes: 2

Related Questions