Oracle PL/SQL Developer Certified Professional (1Z0-149) Practice Test

The key difference is that a function must return a single value to the calling environment, while a procedure does not have a return value requirement. Functions are typically used for computations or data retrieval that produce a result, whereas procedures are used for executing actions like data manipulation, logging, or performing multiple operations. Additionally, functions can be called directly from SQL statements (subject to purity level restrictions), while procedures cannot.

Packages encapsulate related procedures, functions, variables, cursors, and types into a single, self-contained unit. This modularity simplifies code organization, reduces naming conflicts, and enhances reusability. For performance, packages support persistent global variables and cursors that retain state across user sessions, reducing the need for repeated database access. Additionally, Oracle can load an entire package into memory on first call, improving execution speed for subsequent calls within the same session.

WHEN OTHERS is a catch-all exception handler that traps any exception not explicitly handled by preceding WHEN clauses. It is useful for logging unexpected errors or performing cleanup actions before re-raising the exception. However, overusing or misusing it can mask critical errors, making debugging difficult. Best practice is to use WHEN OTHERS only at the outermost block level, always include error logging, and re-raise the exception using RAISE to ensure the error propagates appropriately.

Explicit cursors give the developer fine-grained control over the query execution lifecycle, including opening, fetching, and closing the cursor. This is essential for processing multi-row queries when you need to fetch rows one at a time, manage complex business logic between fetches, or handle multiple result sets simultaneously. Explicit cursors also support parameters, allowing dynamic filtering without re-parsing the query. Implicit cursors are simpler and faster for single-row fetches but lack this level of control.

Composite data types allow developers to store and manipulate multiple related values as a single logical unit. A RECORD type can hold a row of data from a table or a custom set of fields, making it ideal for passing data between procedures and functions. Associative arrays (index-by tables) provide in-memory key-value storage, enabling efficient data caching, lookup operations, and bulk processing. These types reduce the need for multiple scalar variables and improve code readability and performance.