Oracle Database Program with PL/SQL Practice Exam 2025: Latest Questions

You need to declare a variable to store a value that must always match the datatype of the EMPLOYEES.SALARY column, even if the column datatype changes later. Which declaration is the best practice?

A developer writes an anonymous block that should return a message to the user. The block compiles but prints nothing when executed in SQL*Plus. What is the most likely reason?

You need to fetch multiple rows from a query and process each row one at a time. Which PL/SQL construct is most appropriate?

A procedure contains a SELECT ... INTO statement that may return no rows. You want to handle this situation and set an output parameter to NULL without treating it as an unhandled error. Which exception should you handle?

You are troubleshooting a stored function used in a SELECT statement. It occasionally raises an exception when input is invalid, causing the entire query to fail. What is the best practice to prevent the query from failing while still indicating an invalid input?

A team wants to expose a set of procedures and functions while hiding helper subprograms and package-level variables from other schemas. Which design best supports this requirement?

A developer declares an explicit cursor and opens it in a procedure. Under some conditions, the procedure exits early and the cursor remains open, eventually causing ORA-1000 (maximum open cursors exceeded). What is the best approach to prevent this?

A trigger is required to record who changed a row and when. It must set LAST_UPDATED_BY and LAST_UPDATED_DATE on each UPDATE of the ORDERS table, without requiring application code changes. Which trigger type is most appropriate?

A row-level trigger on EMPLOYEES queries EMPLOYEES to compute an aggregate (for example, total headcount in a department) and fails with ORA-04091: table is mutating. Which solution is recommended?

You created a function to be used in SQL queries. The function writes to a logging table to audit each call. When executing SELECT my_func(col) FROM t, it fails due to restrictions on side effects. What is the most appropriate design change?

A PL/SQL block assigns a value to a variable using: v_num := 'ABC'; where v_num is declared as NUMBER. What happens at runtime?

You need to ensure a PL/SQL procedure is treated as an atomic unit: if any statement fails, none of its changes should be committed. What is the best practice for transaction control?

Which SQL statement is guaranteed to raise NO_DATA_FOUND when executed with SELECT ... INTO in PL/SQL (assuming the table exists)?

A package exposes a constant tax rate and several procedures. The tax rate must be readable by callers but never modifiable, and it should be available without calling any procedure. What is the best design?

You have an explicit cursor that selects rows from ORDERS for processing. You need to lock each selected row to prevent other sessions from updating it until your transaction ends. Which cursor definition is appropriate?

A row-level trigger must set :NEW.last_updated := SYSDATE whenever a row in CUSTOMER is updated, but only when the update actually changes one of these columns: name, phone, or status. Which trigger header best fits?

A procedure loops through a cursor and updates rows. When any error occurs, you want to log the error message but still re-raise the original exception so the caller can handle it. Which exception section is best?

You want to restrict direct access to package implementation details while allowing callers to use only a controlled public API. Which approach best enforces this encapsulation?

A trigger on SALES writes audit rows to SALES_AUDIT. The audit insert must succeed even if the main transaction later rolls back. Which design should be used?

You create a function to be used in a SQL query, but calling it in SELECT causes ORA-14551: cannot perform a DML operation inside a query. The function performs an INSERT into a logging table. What is the most appropriate solution?

You need a PL/SQL block that updates a row and then checks whether the UPDATE actually affected any rows, without issuing a separate SELECT. Which approach is correct?

A developer writes the following code to process rows from EMPLOYEES: DECLARE CURSOR c IS SELECT employee_id FROM employees; v_id employees.employee_id%TYPE; BEGIN FOR i IN c LOOP FETCH c INTO v_id; -- process v_id END LOOP; END; What is the result?

You are writing a reusable function to compute a sales tax based on an input amount. You need to ensure that calling SQL statements can use the function (for example, in a SELECT list), and that the function does not perform any DML or COMMIT. Which is the best practice?

A table ORDER_ITEMS has a BEFORE INSERT OR UPDATE row-level trigger that calculates and assigns :NEW.line_total := :NEW.qty * :NEW.unit_price. During testing, the INSERT fails with ORA-04084: cannot change NEW values for this trigger type. Which change fixes the problem while keeping the same behavior?

You maintain a package with a procedure that opens a REF CURSOR and returns it to the caller. Users report that after repeated calls, the session eventually raises errors related to running out of open cursors. Which practice best prevents this issue?