This set of PL/SQL Multiple Choice Questions & Answers (MCQs) focuses on “PL/SQL Transactions”.
1. Which of the following is an atomic unit of work that may consist of one or more related SQL statements?
a) Collections
b) Transactions
c) Packages
d) Exceptions
View Answer
Explanation: PL/SQL Transaction is an atomic unit of work that may consist of one or more related SQL statements. It is called atomic because all the SQL statements that constitute a transaction can be either committed or rolled back collectively using a single statement.
2. Which of the following event starts a transaction?
a) A commit statement is issued
b) User disconnects with the database
c) The first SQL statement is performed after connecting to a database
d) A DDL statement such as, CREATE command is issued
View Answer
Explanation: The event that starts a transaction is the first SQL statement which is performed after connecting to a database. Another event which starts a transaction is the execution of a new SQL statement after a transaction is completed.
3. In SQL*PLUS a commit is automatically performed.
a) True
b) False
View Answer
Explanation: In SQL*PLUS a commit is automatically performed and you do not have to do this explicitly using the COMMIT command. A user can exit form SQL*PLUS by using the EXIT command. A ROLLBACK command is also automatically performed if SQL*PLUS terminates abnormally.
4. Which of the following command is used to make the changes done in a database, permanent in PL/SQL?
a) COMMIT
b) ROLLBACK
c) SAVE
d) EXIT
View Answer
Explanation: The COMMIT command is used to make the changes permanent in PL/SQL. The general syntax for committing changes is just to right the commit keyword – COMMIT;
Without committing the changes, they can be reverted back or undone.
5. Which of the following command is used to undo the changes done to the database in PL/SQL?
a) REVERT
b) ROLLBACK
c) UNDO
d) EXIT
View Answer
Explanation: The ROLLBACK command is used to undo the changes done to the database in PL/SQL. The general syntax for committing changes is just to right the rollback keyword – ROLLBACK; If there is any save point available in the transaction than the rollback process will happen only till the specified save point otherwise the whole transaction will be undone.
6. Which of the following syntax is correct for creating a savepoint in PL/SQL?
a) SAVEPOINT;
b) <savepoint_namecc;
c) <savepoint_name> SAVEPOINT;
d) SAVEPOINT <savepoint_name>;
View Answer
Explanation: Save points are a sort of checkpoints that helps the user to roll back to their chosen save point in case of long transactions. The correct syntax for creating a savepoint is –
SAVEPOINT <savepoint_name>;
The save point name is user depended.
7. Which of the following environment variable is set to execute a COMMIT automatically whenever a DML command is executed?
a) SELFCOMMIT
b) AUTOSAVEPOINT
c) AUTOCOMMIT
d) AUTOROLLBACK
View Answer
Explanation: The AUTOCOMMIT environment variable is set to execute a COMMIT automatically whenever a DML command like INSERT, UPDATE or DELETE is executed. To turn on the AUTOCOMMIT variable we use – SET AUTOCOMMIT ON;
To turn off the AUTOCOMMIT variable we use – SET AUTOCOMMIT OFF;
Sanfoundry Global Education & Learning Series – PL/SQL.
To practice all areas of PL/SQL, here is complete set of 1000+ Multiple Choice Questions and Answers.
If you find a mistake in question / option / answer, kindly take a screenshot and email to [email protected]
