OCP SQL & PL/SQL Programming 3 — Questions and Answers
Question 1: What is the correct syntax to define a PL/SQL record type?
- TYPE rec IS TABLE OF employee%ROWTYPE
- TYPE rec IS RECORD (col1 type, col2 type) (Correct answer)
- RECORD TYPE rec (col1 type)
- CREATE RECORD rec AS (col1 type)
Correct answer: TYPE rec IS RECORD (col1 type, col2 type)
PL/SQL record types are declared using TYPE name IS RECORD (field_name datatype, ...) syntax.
Question 2: Which SQL set operator removes duplicate rows and requires matching column count and compatible datatypes?
- UNION ALL
- MINUS
- INTERSECT
- UNION (Correct answer)
Correct answer: UNION
UNION combines result sets and removes duplicates, requiring the same number of columns with compatible types; UNION ALL keeps duplicates.
Question 3: In Oracle SQL, what does a NULL value in a UNIQUE constraint column mean?
- It violates the UNIQUE constraint
- Multiple NULLs are allowed in a UNIQUE column (Correct answer)
- Only one NULL is permitted per table
- NULL is treated as zero for uniqueness
Correct answer: Multiple NULLs are allowed in a UNIQUE column
Oracle treats NULL as unknown, so multiple NULLs are permitted in a column with a UNIQUE constraint.
Question 4: What happens to a SAVEPOINT if the enclosing transaction is rolled back with ROLLBACK (no TO clause)?
- The savepoint persists for future use
- All savepoints in the transaction are released (Correct answer)
- Only the named savepoint is removed
- The savepoint is converted to an autonomous transaction
Correct answer: All savepoints in the transaction are released
A full ROLLBACK undoes the entire transaction and releases all savepoints defined within it.
Question 5: Which PL/SQL pragma is used to declare an exception that maps to a specific Oracle error number?
- PRAGMA AUTONOMOUS_TRANSACTION
- PRAGMA RESTRICT_REFERENCES
- PRAGMA EXCEPTION_INIT (Correct answer)
- PRAGMA SERIALLY_REUSABLE
Correct answer: PRAGMA EXCEPTION_INIT
PRAGMA EXCEPTION_INIT associates a user-defined exception name with a specific Oracle error number like ORA-00001.
Question 6: What does the TRIM method do on a nested table in PL/SQL?
- Removes all elements from the collection
- Removes one or more elements from the end of the collection (Correct answer)
- Removes elements matching a condition
- Removes leading and trailing whitespace from string elements
Correct answer: Removes one or more elements from the end of the collection
The TRIM(n) method removes n elements from the end of a nested table or VARRAY; TRIM with no argument removes one element.
Question 7: Which Oracle function converts a number to a character string using a format model?
- CAST
- TO_CHAR (Correct answer)
- CONVERT
- NUM_TO_CHAR
Correct answer: TO_CHAR
TO_CHAR(number, format_model) converts a numeric value to a VARCHAR2 string using the specified format pattern.
OCP SQL & PL/SQL Programming 3
What is the correct syntax to define a PL/SQL record type?