A financial services application uses a PL/SQL package to calculate loan eligibility. To improve performance for frequently called scenarios, the lead developer decides to use a result-cached function. The function takes a customer ID and loan amount as input. During testing, it's discovered that the function returns stale data if the customer's credit score, stored in a separate CREDIT_SCORES table, is updated. Which clause must be added to the function definition to ensure the cache is invalidated when the CREDIT_SCORES table changes?
Answer and explanation
Correct answer: B
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.
Question 2
A DBA is reviewing a large PL/SQL package and notices several procedures pass a large record type (defined with %ROWTYPE from a table with 50 columns) as an IN OUT parameter. The DBA suspects this is causing performance degradation due to excessive copying of the record's data. Which compiler hint should be used in the procedure definition to request pass-by-reference semantics and potentially improve performance?
Answer and explanation
Correct answer: A
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.
Question 3
A developer needs to create a PL/SQL package that will be deployed across different customer environments running Oracle Database 12c, 18c, and 19c. The package should use a new feature available only in 19c when possible, but fall back to an older implementation on earlier database versions. Which PL/SQL feature allows for this version-specific code implementation within a single source file?
Answer and explanation
Correct answer: C
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.
Question 4
A security audit requires that a specific sensitive procedure, process_payroll, within the HR_PKG package can ONLY be called by the BATCH_JOB_PKG and the FINANCE_REPORTS_PKG. No other database object or user should be able to execute this procedure directly. Which is the most effective and secure method to enforce this access restriction?
Answer and explanation
Correct answer: B
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.
Question 5
A data processing pipeline needs to load configuration settings from a key-value table into a PL/SQL collection for fast lookups. The keys are VARCHAR2 strings (e.g., 'TIMEOUT', 'LOG_LEVEL') and the values are also VARCHAR2. The number of settings is unknown and can change. Which composite data type is the most appropriate choice for this requirement?
Answer and explanation
Correct answer: D
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.
Question 6
Multiple answers
A developer is writing a procedure to archive old orders. The procedure must perform two main tasks: copy order data to an archive table and then delete the original orders. It is critical that the logging of the archive operation succeeds and is committed, even if the subsequent deletion of the original orders fails and is rolled back. Which two PL/SQL features should be combined to achieve this? (Select TWO)
Answer and explanation
Correct answers: A, C
Creating a separate, local procedure for the logging action encapsulates the logic cleanly.
This pragma declares that the logging procedure runs in its own independent transaction. It can commit its work (the log entry) without affecting the main transaction (the data archiving and deletion). This ensures the log is saved even if the main transaction is later rolled back.
Question 7
Case Study:
A logistics company, ShipFast Inc., is developing a new package, TRACKING_PKG, to manage shipment statuses. The package needs to provide a procedure, UPDATE_STATUS, that takes a tracking number and a new status. A key requirement is that every status update attempt, whether successful or not, must be recorded in an AUDIT_LOG table for compliance reasons. The audit record must be saved permanently, even if the main transaction that called UPDATE_STATUS is later rolled back by the calling application.
Furthermore, the UPDATE_STATUS procedure will be part of a large, complex transaction and must not issue its own COMMIT or ROLLBACK, as this would interfere with the calling application's transaction control. The audit logging, however, must be self-contained. The development team has decided to use a private procedure within the package body, LOG_AUDIT_ATTEMPT, to handle the insertion into the AUDIT_LOG table.
To optimize performance, another procedure, BULK_UPDATE_STATUSES, is required. This procedure will accept a collection of tracking numbers and statuses and update them all. The team wants to minimize context switching between the PL/SQL and SQL engines during this bulk operation.
Which package body implementation correctly satisfies all the requirements for both transactional integrity and performance optimization?
Answer and explanation
Correct answer: B
This solution correctly addresses all requirements. Using PRAGMA AUTONOMOUS_TRANSACTION in the private logging procedure allows it to commit its own transaction independently, ensuring audit records are saved regardless of the main transaction's outcome. Using the FORALL statement for the bulk update is the most performant method, as it sends all DML statements to the SQL engine in a single call, minimizing context switching.
Question 8
True or False: An INDEX BY table (associative array) defined within a PL/SQL package specification persists for the duration of the database session.
Answer and explanation
Correct answer: A
Variables, cursors, and types declared in a package specification or body (outside of a specific subprogram) are part of the package's state. This state is initialized when the package is first referenced in a session and persists for the entire duration of that database session, allowing data to be maintained across multiple calls to the package's subprograms within the same session.
Question 9
A developer needs to process a result set of employee records. The number of employees is large but manageable within session memory. For each employee, multiple DML operations are required. To improve performance, the developer wants to fetch all employee records from the EMPLOYEES table into a PL/SQL collection in a single database round-trip. Which SQL statement clause should be used?
Answer and explanation
Correct answer: C
The BULK COLLECT INTO clause is used with SELECT, FETCH, and RETURNING clauses to retrieve multiple rows of data into one or more collections with a single call to the SQL engine. This dramatically reduces context switching and improves performance compared to fetching one row at a time in a loop.
Question 10
Examine the following code:
DECLARE
TYPE t_emp_rec IS RECORD (
employee_id employees.employee_id%TYPE,
salary employees.salary%TYPE
);
v_emp_rec t_emp_rec;
BEGIN
SELECT employee_id, salary
INTO v_emp_rec
FROM employees
WHERE department_id = 90;
DBMS_OUTPUT.PUT_LINE('Employees found: ' || SQL%ROWCOUNT);
END;
The employees table has three employees in department 90. What is the result when this block is executed?
Answer and explanation
Correct answer: C
A SELECT ... INTO statement is designed to fetch exactly one row. If the WHERE clause results in the query returning more than one row, Oracle raises the predefined TOO_MANY_ROWS exception. Since the block does not have an exception handler for this, the block will terminate and propagate the unhandled exception.
