SQL & PL/SQL Programming Flashcards

Which PL/SQL package subprogram overloading rule is NOT permitted?

Answer: Overloading by return type alone

PL/SQL does not allow overloading based solely on the return type of a function; parameters must differ.

What does the EXECUTE IMMEDIATE statement enable in PL/SQL?

Answer: Parses and executes a dynamic SQL string at runtime

EXECUTE IMMEDIATE is Oracle's Native Dynamic SQL statement for parsing and running SQL or PL/SQL strings built at runtime.

In Oracle SQL, what is the difference between WHERE and HAVING when filtering aggregated data?

Answer: WHERE filters before grouping; HAVING filters after grouping

WHERE is applied to individual rows before GROUP BY, while HAVING filters the resulting groups after aggregation.

Which Oracle built-in package provides procedures for outputting debug messages to the SQL*Plus buffer?

Answer: DBMS_OUTPUT

DBMS_OUTPUT.PUT_LINE writes messages to a server-side buffer that SQL*Plus (with SERVEROUTPUT ON) displays after execution.

What is an autonomous transaction in PL/SQL?

Answer: An independent transaction that can commit or rollback without affecting the calling transaction

PRAGMA AUTONOMOUS_TRANSACTION makes a subprogram run in its own transaction context, allowing it to commit or rollback independently of the caller.

Which SQL function would you use to return a specified number of characters from the left side of a string?

Answer: SUBSTR(str, 1, n)

Oracle uses SUBSTR(string, 1, n) to extract n characters starting from position 1, equivalent to LEFT() in other databases.

What is the purpose of the RETURNING INTO clause in an Oracle DML statement?

Answer: Returns column values of the affected row(s) directly into PL/SQL variables

RETURNING INTO captures column values of inserted, updated, or deleted rows into PL/SQL variables or collections without a separate SELECT.