1Z0-149 Oracle Database Program with PL/SQL Practice Questions

The PL/SQL compiler processes code sequentially. When it compiles proc_A, it will encounter a call to proc_B, which has not yet been defined later in the package body. This results in a compilation error. To solve this, a forward declaration for proc_B must be placed at the top of the package body before proc_A is defined.

This is the correct approach. A BEFORE INSERT trigger allows the business rule to be checked before the DML operation occurs. The :NEW.patient_id can be used to efficiently query the BILLS table for the specific patient. Using RAISE_APPLICATION_ERROR is the standard and best practice for returning custom errors from the database to a client application. It stops the DML operation and provides a meaningful error message.

DDL statements cannot be executed using static SQL in PL/SQL because the objects they reference must exist at compile time. When the object name is dynamic (passed as a parameter), Native Dynamic SQL (NDS) with EXECUTE IMMEDIATE is the required method. The entire DDL command must be constructed as a string and then executed.

AUTHID CURRENT_USER specifies that the procedure executes with the privileges of the user who invokes it (the invoker), not the user who owns it (the definer). In this case, JANE is the invoker. Since JANE lacks the necessary UPDATE privilege on the SALES table, the SQL statement within the procedure will fail due to insufficient privileges.

PL/SQL compile-time warnings identify conditions that are not syntax errors but could lead to runtime errors or poor performance. Examples include unreachable code, subprograms that don't return a value on all paths, or severe performance warnings like passing a NOT NULL parameter to a subprogram that declares it without the constraint. Enabling warnings is a best practice for improving code quality and robustness.

A record type defined within a PL/SQL block or package (TYPE ... IS RECORD) is a PL/SQL-only construct and cannot be used as a data type for a table column. To store a composite structure in a table column, you must create a SQL OBJECT type using the CREATE TYPE ... AS OBJECT DDL statement.

FORALL is the companion to BULK COLLECT. It takes an entire collection and sends all the DML operations (like INSERT, UPDATE, or DELETE) to the SQL engine in a single call. This bulk binding approach drastically reduces the performance overhead of context switching that occurs when executing DML inside a regular loop.

The RELIES_ON clause is specifically designed for result-cached functions to declare their dependency on database objects. When data in the CREDIT_SCORES table changes, the database automatically invalidates the function's result cache, ensuring subsequent calls re-execute the function and fetch fresh data. DETERMINISTIC is for functions that always return the same result for the same inputs, but it doesn't manage dependencies on table data. PARALLEL_ENABLE is for parallel query execution, and AUTONOMOUS_TRANSACTION is for independent transactions.

The NOCOPY hint is used in a subprogram's parameter list to request that the compiler pass the corresponding actual parameter by reference instead of by value. This is particularly effective for large composite types like records or collections passed as OUT or IN OUT parameters, as it avoids the overhead of creating a temporary copy of the data.

Conditional compilation allows the PL/SQL compiler to selectively include or exclude code based on conditions evaluated at compile time. By using the $IF directive with the DBMS_DB_VERSION.VERSION inquiry directive, a developer can write code blocks that are only compiled and included in the final package if the database version meets a certain criterion (e.g., is 19c or higher), providing a clean way to manage version-specific logic.