Oracle Updated 1z0-071 Exam Questions and Answers by marlo

For the given SQL commands:

Option A: ROLLBACK;

A ROLLBACK will undo all changes made in the transaction up to the last COMMIT.

Option C: ROLLBACK TO SAVEPOINT post_insert;

ROLLBACK TO SAVEPOINT will undo changes back to the named savepoint, which in this case is after the first insert.

For conditional INSERT ALL in Oracle Database 12c:

D. A single WHEN condition can be used for multiple INTO clauses. This is true. A single WHEN condition in a multi-table insert can direct the insertion of a single row source into multiple target tables.

E. Each WHEN condition is tested for each row returned by the subquery. True, each row from the subquery is evaluated against each WHEN condition to determine into which table(s) the row should be inserted.

Options A, B, and C are incorrect:

A is incorrect because a row can indeed be directed into multiple tables based on the conditions.

B is incorrect as INSERT ALL can include an ELSE clause to handle rows that do not meet any of the specified conditions.

C is incorrect because not every row from the subquery necessarily results in a row insertion; it depends on the conditions being met.

The SET VERIFY ON command is related to how SQL*Plus and SQL Developer display information about substitution variables:

A. It displays values for variables created by the DEFINE command: When VERIFY is set to ON, SQL*Plus and SQL Developer will display the old and new values of a substitution variable when it is redefined using the DEFINE command or when a new value is provided for it during the session.

B. It can be used in SQL Developer and SQL*Plus: While traditionally associated with SQL*Plus, the SET VERIFY command is also supported in SQL Developer, allowing you to control the display of substitution variable values in both environments.

References:

Oracle SQL*Plus User's Guide and Reference, especially the section on the SET command and substitution variables.

Regarding privileges in Oracle:

Option A: Dropping user FINANCE will automatically revoke SELECT on SCOTT.EMP from user FIN_CLERK.

Dropping a user will revoke all privileges that user has granted to others.

Option B: Revoking SELECT on SCOTT.EMP from user FINANCE will also revoke the privilege from user FIN_CLERK.

Since FINANCE granted SELECT on SCOTT.EMP to FIN_CLERK with grant option, revoking the privilege from FINANCE will also cascade and revoke it from FIN_CLERK.

Options C, D, and E are incorrect because:

Option C: User FINANCE can grant privileges it owns, but CREATE SESSION is a system privilege; unless FINANCE has the 'WITH ADMIN OPTION' for CREATE SESSION, it cannot grant this privilege.

Option D: User FIN_CLERK can only grant privileges to others if it has been given those privileges with the 'WITH GRANT OPTION' and it was not given in this case.

Option E: User FINANCE can grant privileges on SCOTT.EMP to other users only if it has those privileges with 'WITH GRANT OPTION'. If the privilege was just SELECT with GRANT OPTION, it cannot grant ALL privileges.