Oracle Database Program with PL/SQL Intermediate Practice Exam: Medium Difficulty 2025

A developer creates a PL/SQL block that declares a variable emp_salary with the same data type as the SALARY column in the EMPLOYEES table. Later, the DBA changes the SALARY column from NUMBER(8,2) to NUMBER(10,2). What declaration method ensures the variable automatically inherits the new data type without code changes?

An application processes customer orders and needs to handle a scenario where a product is out of stock. The developer wants to raise a custom exception named insufficient_inventory_exception when stock falls below the reorder level. Which combination of steps is required to properly implement this custom exception?

A developer creates an explicit cursor to retrieve employee records. During testing, they notice that when no rows are found, the code enters an infinite loop. Which cursor attribute should be checked in the loop condition to prevent this issue?

A company requires a stored procedure that updates employee salaries and should only commit changes if all updates succeed. If any update fails, all changes should be rolled back. The procedure will be called from multiple applications. What is the best practice for transaction control in this procedure?

A development team creates a package with a function that calculates bonus amounts. The bonus calculation logic uses a tax_rate variable that should be accessible to all functions within the package but hidden from external users. Where should the tax_rate variable be declared?

A function named get_department_budget is created with a parameter dept_id that has a default value of 10. The function is called using named notation: get_department_budget(dept_id => 20). Later, the function signature is modified to add a second parameter fiscal_year with a default value. What happens to the existing function calls?

An organization needs to automatically log all changes to the EMPLOYEES table into an AUDIT_LOG table. The log should capture the old and new values for salary changes, but the logging should not prevent the original DML operation from completing even if the logging fails. Which trigger design best meets these requirements?

A package contains both public procedures (declared in the specification) and private procedures (declared only in the body). A public procedure PROC_A calls a private procedure PROC_B. What happens if PROC_B is modified and the package body is recompiled without changing the package specification?

A developer writes a compound trigger on the ORDERS table to maintain running totals. The trigger needs to collect data during row-level processing and perform a single bulk insert at the end. Which sections of the compound trigger should be used?

A cursor FOR loop is used to process employee records and calculate bonuses. Inside the loop, an exception occurs for one employee record due to invalid data. The developer wants the loop to continue processing remaining employees and log the error. What exception handling approach accomplishes this?