SQL & PL/SQL Programming Flashcards

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

Answer: TYPE rec IS RECORD (col1 type, col2 type)

PL/SQL record types are declared using TYPE name IS RECORD (field_name datatype, ...) syntax.

Which SQL set operator removes duplicate rows and requires matching column count and compatible datatypes?

Answer: UNION

UNION combines result sets and removes duplicates, requiring the same number of columns with compatible types; UNION ALL keeps duplicates.

In Oracle SQL, what does a NULL value in a UNIQUE constraint column mean?

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.

What happens to a SAVEPOINT if the enclosing transaction is rolled back with ROLLBACK (no TO clause)?

Answer: All savepoints in the transaction are released

A full ROLLBACK undoes the entire transaction and releases all savepoints defined within it.

Which PL/SQL pragma is used to declare an exception that maps to a specific Oracle error number?

Answer: PRAGMA EXCEPTION_INIT

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

What does the TRIM method do on a nested table in PL/SQL?

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.

Which Oracle function converts a number to a character string using a format model?

Answer: TO_CHAR

TO_CHAR(number, format_model) converts a numeric value to a VARCHAR2 string using the specified format pattern.