4.2.1 Make Use of COMMIT, ROLLBACK, and SAVEPOINT in a PL/SQL Block Log into the CTA schema and enter the following series of commands. (Optionally, you can write the PL/SQL block in a text file and then run the script from the SQL*Plus prompt.) -- ch04_7a.sql BEGIN INSERT INTO student ( student_id, Last_name, zip, registration_date, created_by, created_date, modified_by, modified_date ) VALUES ( student_id_seq.nextval, 'Tashi', 10015, '01-JAN-99', 'STUDENTA', '01-JAN-99', 'STUDENTA','01-JAN-99' ); SAVEPOINT A; INSERT INTO student ( student_id, Last_name, zip, registration_date, created_by, created_date, modified_by, modified_date ) VALUES (student_id_seq.nextval, 'Sonam', 10015, '01-JAN-99', 'STUDENTB','01-JAN-99', 'STUDENTB', '01-JAN-99' ); SAVEPOINT B; INSERT INTO student ( student_id, Last_name, zip, registration_date, created_by, created_date, modified_by, modified_date ) VALUES (student_id_seq.nextval, 'Norbu', 10015, '01-JAN-99', 'STUDENTB', '01-JAN-99', 'STUDENTB', '01-JAN-99' ); SAVEPOINT C; ROLLBACK TO B; END; a) | If you issue the following command, what would you expect to see? Why? SELECT * FROM student WHERE last_name = 'Norbu'; | b) | Try it. What happened ? Why? | Now issue ROLLBACK to SAVEPOINT A; c) | What happened? | d) | If you issue the following, what do you expect to see? SELECT last_name FROM student WHERE last_name = 'Tashi'; | e) | Issue the command and explain your findings. | | SAVEPOINT is often used before a complicated section of the transaction. If this part of the transaction fails, it can be rolled back, allowing the earlier part to continue. | | It is important to note the distinction between transactions and PL/SQL blocks. When a block starts, it does not mean that the transaction starts. Likewise, the start of the transaction need not coincide with the start of a block. | |