SQL & PL/SQL Programming Flashcards
7 cards from real OCP practice questions. Tap to flip, then mark Knew It or Still Learning — missed cards come back until you master them.
Read the first 7 SQL & PL/SQL Programming flashcards as text
Which PL/SQL collection type can be indexed by a VARCHAR2 key?
Answer: Associative array
Associative arrays (INDEX BY tables) support both BINARY_INTEGER and VARCHAR2 index keys.
What does the BULK COLLECT clause do in PL/SQL?
Answer: Fetches multiple rows into a collection in a single context switch
BULK COLLECT retrieves multiple rows into a PL/SQL collection in one fetch, reducing context switches between SQL and PL/SQL engines.
Which SQL function returns the number of months between two dates?
Answer: MONTHS_BETWEEN
MONTHS_BETWEEN(date1, date2) returns the number of months between two date values.
In a PL/SQL exception handler, what does SQLCODE return when no exception has occurred?
Answer: 0
SQLCODE returns 0 when no exception has been raised in the current block.
Which clause in a SELECT statement is evaluated AFTER the GROUP BY clause?
Answer: HAVING
HAVING filters groups produced by GROUP BY, so it is evaluated after GROUP BY but before ORDER BY.
What is the effect of the NOCOPY hint on a PL/SQL OUT parameter?
Answer: Passes the parameter by reference instead of by value
NOCOPY instructs Oracle to pass OUT/IN OUT parameters by reference, avoiding the overhead of copying large structures.
Which Oracle SQL clause would you use to pivot rows into columns without using the PIVOT operator?
Answer: CASE with GROUP BY and aggregate functions
Conditional aggregation using CASE expressions inside aggregate functions like SUM or MAX with GROUP BY can pivot rows into columns.