OCP SQL & PL/SQL Programming 3

Question 1

What is the correct syntax to define a PL/SQL record type?

Accepted Answer

TYPE rec IS RECORD (col1 type, col2 type)

Answer

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?

Accepted Answer

UNION

Answer

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?

Accepted Answer

Multiple NULLs are allowed in a UNIQUE column

Answer

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)?

Accepted Answer

All savepoints in the transaction are released

Answer

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?

Accepted Answer

PRAGMA EXCEPTION_INIT

Answer

PRAGMA EXCEPTION_INIT associates a user-defined exception name with a specific Oracle error number like ORA-00001.

(OCP) Oracle Certified Professional Practice Test