SQL & PL/SQL Programming Flashcards

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

Answer: Statement-level trigger

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

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

Answer: 5

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

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

Answer: DENSE_RANK

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

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

Answer: A function that returns rows incrementally using PIPE ROW before completing

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

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

Answer: AS OF SCN

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

What does the %NOTFOUND cursor attribute return after a FETCH that retrieves no rows?

Answer: TRUE

%NOTFOUND returns TRUE when the most recent FETCH did not return a row, indicating the cursor is exhausted.

Which Oracle DDL statement would you use to change a column's datatype from VARCHAR2(50) to VARCHAR2(100)?

Answer: ALTER TABLE ... MODIFY (column VARCHAR2(100))

ALTER TABLE table_name MODIFY (column_name new_datatype) is the Oracle syntax for modifying an existing column's definition.