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

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.

The ACCESSIBLE BY clause provides a compile-time, whitelist-based access control mechanism for PL/SQL units. By specifying the calling packages in this clause, you instruct the compiler to raise an error if any other unit attempts to call the protected procedure. This is more secure than grant-based systems because it prevents access even from users with high privileges (like SYS) or through dynamic SQL, enforcing the restriction at the code level.

An INDEX BY table, also known as an associative array, is the only PL/SQL collection type that allows a VARCHAR2 index. This makes it ideal for key-value pairs where the key is a string. It allows for direct, fast lookups using the string key (e.g., config_settings('TIMEOUT')), which perfectly matches the requirement. Nested tables and VARRAYs are indexed by integers only.