OCP SQL & PL/SQL Programming 2

Question 1

Which PL/SQL collection type can be indexed by a VARCHAR2 key?

Accepted Answer

Associative array

Answer

Associative arrays (INDEX BY tables) support both BINARY_INTEGER and VARCHAR2 index keys.

Question 2

What does the BULK COLLECT clause do in PL/SQL?

Accepted Answer

Fetches multiple rows into a collection in a single context switch

Answer

BULK COLLECT retrieves multiple rows into a PL/SQL collection in one fetch, reducing context switches between SQL and PL/SQL engines.

Question 3

Which SQL function returns the number of months between two dates?

Accepted Answer

MONTHS_BETWEEN

Answer

MONTHS_BETWEEN(date1, date2) returns the number of months between two date values.

Question 4

In a PL/SQL exception handler, what does SQLCODE return when no exception has occurred?

Accepted Answer

0

Answer

SQLCODE returns 0 when no exception has been raised in the current block.

Question 5

Which clause in a SELECT statement is evaluated AFTER the GROUP BY clause?

Accepted Answer

HAVING

Answer

HAVING filters groups produced by GROUP BY, so it is evaluated after GROUP BY but before ORDER BY.

(OCP) Oracle Certified Professional Practice Test