OCP SQL & PL/SQL Programming 2 — Questions and Answers
Question 1: Which PL/SQL collection type can be indexed by a VARCHAR2 key?
- Nested table
- VARRAY
- Associative array (Correct answer)
- INDEX BY BINARY_INTEGER only
Correct answer: Associative array
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?
- Inserts multiple rows in one DML statement
- Fetches multiple rows into a collection in a single context switch (Correct answer)
- Creates a bulk index on a collection
- Aggregates data from multiple tables
Correct 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.
Question 3: Which SQL function returns the number of months between two dates?
- DATE_DIFF
- MONTHS_BETWEEN (Correct answer)
- DATEDIFF
- INTERVAL_MONTHS
Correct answer: MONTHS_BETWEEN
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?
- -1
- 1
- 0 (Correct answer)
- NULL
Correct answer: 0
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?
- WHERE
- FROM
- HAVING (Correct answer)
- ORDER BY
Correct answer: HAVING
HAVING filters groups produced by GROUP BY, so it is evaluated after GROUP BY but before ORDER BY.
Question 6: What is the effect of the NOCOPY hint on a PL/SQL OUT parameter?
- Prevents the parameter from being modified
- Passes the parameter by reference instead of by value (Correct answer)
- Creates a read-only copy of the parameter
- Disables constraint checking on the parameter
Correct 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.
Question 7: Which Oracle SQL clause would you use to pivot rows into columns without using the PIVOT operator?
- CONNECT BY
- CASE with GROUP BY and aggregate functions (Correct answer)
- ROLLUP
- GROUPING SETS
Correct 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.
OCP SQL & PL/SQL Programming 2
Which PL/SQL collection type can be indexed by a VARCHAR2 key?