Oracle PL Changed 1z0-071 Questions

For Transaction Control Language (TCL) operations:

• A. COMMIT erases all the transaction’s savepoints and releases its locks: When a COMMIT operation is executed, it finalizes all changes made during the transaction, removes any savepoints that have been set during the transaction, and releases any locks held on the database objects by the transaction.
• B. COMMIT ends the transaction and makes all its changes permanent: A COMMIT command completes the transaction, making all data modifications permanent and visible to other sessions.
• E. ROLLBACK without the TO SAVEPOINT clause undoes all the transaction's changes, releases its locks, and erases all its savepoints: The ROLLBACK command, when used without specifying a SAVEPOINT, undoes all changes made during the transaction, releases any locks held, and removes any savepoints that were established during the transaction.

Incorrect options:

• C: ROLLBACK, without any specification, not only undoes all the transaction's changes but also releases all locks held by the transaction.
• D: ROLLBACK to a SAVEPOINT only undoes changes up to the named savepoint and does not end the transaction; the transaction continues until a COMMIT or a full ROLLBACK is issued.
• F: ROLLBACK without a TO SAVEPOINT clause also erases all savepoints created during the transaction.

B: True. Multiple table insert statements, specifically the conditional INSERT ALL, can transform a single row from the source table into multiple rows in one or more target tables depending on the conditions specified in the WHEN clauses.

E: True. The unconditional INSERT ALL statement will insert rows into multiple target tables without any conditions. However, it does not require the same number of columns in both the source and target tables. The INSERT ALL syntax allows you to specify different columns for each target table into which rows will be inserted.

Multiple table insert operations allow for complex insert scenarios, where based on the data, one can insert into different tables or multiple times into the same table based on different conditions.

References:Oracle SQL reference details the use of INSERT ALL and INSERT FIRST clauses for inserting into multiple tables based on conditions specified.

In Oracle Database, object privileges are rights to perform a particular action on a specific object in the database. Here's why the other options are incorrect:

A. UPDATE can be granted on tables, views, and materialized views, but not sequences. B. DELETE cannot be granted on sequences because sequences do not store data that can be deleted. D. INSERT cannot be granted on sequences; sequences are used to generate numbers, not to be inserted into directly. C. REFERENCES allows the grantee to create a foreign key that references the table or the columns of the table. It is applicable only to tables and views. E. SELECT can indeed only be granted on tables and views (including materialized views). F. ALTER is an object privilege that can be granted on tables and sequences but not views.

For more details, one may refer to the Oracle Database SQL Language Reference documentation, which specifies the types of object privileges and the objects they apply to.

In the case of SQL commands TRUNCATE and DELETE:

• A. the result of a delete can be undone by issuing a rollback: DELETE is a DML operation that affects rows individually and can be rolled back if it is performed within a transaction.
• B. delete can use a where clause to determine which row(s) should be removed: DELETE operation allows the use of a WHERE clause to specify which rows should be deleted based on certain conditions.

Incorrect options are:

• C: TRUNCATE does not support the use of a WHERE clause. It is designed to remove all rows from a table swiftly and cannot be conditional.
• D: TRUNCATE does not leave indexes in an unusable state; it simply removes all rows.
• E: TRUNCATE is a DDL command and its operation typically cannot be rolled back in many SQL database systems, including Oracle.