OCP SQL & PL/SQL Programming 4

Question 1

Which type of PL/SQL trigger fires once for the entire DML statement regardless of how many rows are affected?

Accepted Answer

Statement-level trigger

Answer

A statement-level trigger (without FOR EACH ROW) fires exactly once per DML statement, not once per affected row.

Question 2

What is the result of SELECT COALESCE(NULL, NULL, 5, NULL, 10) FROM DUAL?

Accepted Answer

5

Answer

COALESCE returns the first non-NULL expression in its argument list, which is 5 in this case.

Question 3

In Oracle SQL, which analytic function assigns a unique sequential integer to rows within a partition without gaps?

Accepted Answer

DENSE_RANK

Answer

DENSE_RANK assigns consecutive rank values without gaps when ties occur, unlike RANK which skips ranks after ties.

Question 4

What is a pipelined table function in Oracle PL/SQL?

Accepted Answer

A function that returns rows incrementally using PIPE ROW before completing

Answer

A pipelined table function uses PIPE ROW to return rows one at a time to the caller before the function finishes, enabling streaming behavior.

Question 5

Which SQL clause would you add to prevent phantom reads in a query used inside a long-running PL/SQL procedure?

Accepted Answer

AS OF SCN

Answer

AS OF SCN (or AS OF TIMESTAMP) queries Flashback data at a specific point in time, ensuring a consistent read snapshot throughout the procedure.

(OCP) Oracle Certified Professional Practice Test