I know this question might be duplicated even though, I am raised the same kind question because mine requirement is different and new to ORACLE Database.
Basically, I am having four queries with different tables.
Query 1: Table name: users
INSERT INTO users (user_id,user_name,acc_is_active,full_name,email,description,us_report_role,us_is_system,us_is_active)
VALUES ('aaaaaa',' araut_xxxxxxxx’','Y', ‘Ram G DON’, ‘[email protected]’,‘Staff_ID','0','N','Y')
Query 2: Table Name: sequences
update sequences set SQ_SEQ_VALUE='aaaaaa' where SQ_SEQ_NAME='USER_SEQ'
Query 3: Table Name: users_auth_data
INSERT into users_auth_data (UAD_USER_ID,UAD_KEY,UAD_VALUE) VALUES ('aaaaaa','LDAP_SERVER_ID','-1')
Query 4: Table : users_auth_data
INSERT into users_auth_data (UAD_USER_ID,UAD_KEY,UAD_VALUE) VALUES ('aaaaaa','LDAP_USER_DN','CN=Staff_ID,OU=ExamplePeople,DC=InfoDir,DC=DEV,DC=EXAMPLE')
these are four queries I need to run sequentially. if among these any queries get failed then We have to rollback the all changes. if all queries runs successfully then will commit all queries changes.
In order to achieve above scenario which approach , we need to follow and how?
If someone help me with solution then it could be great help.
What you are looking for, is a Transaction.
One way to do this via a Pl/Sql block using exceptions. Ensure that you set
autocommit off;
Declare
<declare your variables>
Begin
<your queries>
commit; --This will commit the changes if all queries run fine
Exception
WHEN OTHERS THEN
rollback; --This will rollback the changes if there are any exceptions caught.
end;
There is one more way to accomplish this which is using save points. This is better described in the below link :