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

A developer creates a package with a private function that contains a PRAGMA AUTONOMOUS_TRANSACTION directive. The function performs DML operations and commits them. The package specification declares a public procedure that calls this private function within a loop processing 10,000 records. During execution, an exception occurs at record 5,000 in the main procedure after the autonomous transaction has committed 4,999 records. What is the outcome if the main procedure's exception handler issues a ROLLBACK?

A production system uses a cursor FOR loop with a complex multi-table join query processing millions of rows. The DBA notices excessive PGA memory consumption. The developer proposes three solutions: (1) Replace the cursor FOR loop with BULK COLLECT with LIMIT clause, (2) Add the /*+ RESULT_CACHE */ hint to the query, or (3) Explicitly declare the cursor with FOR UPDATE clause. Which solution most effectively addresses the PGA memory issue while maintaining transactional integrity for row-by-row processing?

An application uses a packaged cursor variable (REF CURSOR) that is opened in one procedure, passed to multiple procedures for processing, and closed in a final cleanup procedure. During testing, the application intermittently raises ORA-01001: invalid cursor. Analysis shows the cursor variable is sometimes closed prematurely. What is the most robust design pattern to prevent this issue while maintaining the distributed processing architecture?

A developer creates a pipelined table function that queries a partitioned table and uses PARALLEL_ENABLE with PARTITION BY clause. The function includes error handling that catches exceptions and returns error records as part of the piped output. During execution with parallel query slaves, some exceptions are not being caught and cause the entire query to fail. What is the most likely cause and solution?

A package specification declares a procedure with three formal parameters: (p_id IN NUMBER, p_data IN OUT VARCHAR2, p_flag IN BOOLEAN DEFAULT TRUE). The package body implementation uses these parameters but the procedure is also called from a database trigger. After compilation, the trigger fails with PLS-00306. What is the cause and the correct solution?

A package maintains state using package-level variables including a collection that caches reference data. Multiple sessions execute package procedures concurrently. The DBA wants to reduce memory usage by having all sessions share the same cached data. The developer considers using SERIALLY_REUSABLE pragma. What is the correct assessment of this approach and its implications?

A compound trigger is designed to enforce a complex business rule on a table with high concurrency. The trigger uses BEFORE EACH ROW and AFTER STATEMENT sections. During load testing with 50 concurrent sessions performing DML, some sessions experience ORA-04091 mutating table errors despite the compound trigger design. What is the most likely cause?

A system uses dynamic SQL with EXECUTE IMMEDIATE to run DDL statements for creating temporary objects based on user input. Security review identifies SQL injection risks. The developer proposes using bind variables in the EXECUTE IMMEDIATE statement. What is the correct assessment?

A critical exception handling requirement states that when a stored procedure encounters a NO_DATA_FOUND exception in a nested block three levels deep, it must log the error with the complete call stack and continue processing. The procedure uses DBMS_UTILITY.FORMAT_ERROR_STACK and DBMS_UTILITY.FORMAT_CALL_STACK in the exception handler. During testing, the call stack information is incomplete. What is the issue and solution?

A performance-critical package function is defined as RESULT_CACHE and queries a table that is modified infrequently. The function accepts two parameters and returns aggregated data. After deployment, developers notice that the result cache hit ratio is unexpectedly low despite identical function calls. Investigation reveals the function accepts a parameter of type VARCHAR2 that comes from different client NLS settings. What is the root cause and optimal solution?