1z0146 Oracle 11g: Advanced PL/SQL Free Practice Test — 29 Questions

Question 1

Consider a PL/SQL procedure designed to process an extensive `employees` table. The procedure employs `BULK COLLECT INTO` to populate a collection of employee records, with a `LIMIT` clause set to fetch a maximum of 500 rows per iteration. During the execution of this procedure, what is the most significant advantage gained from this specific combination of `BULK COLLECT` and the `LIMIT` clause in terms of system resource utilization?

Accepted Answer

Minimizing context switching between the SQL and PL/SQL engines while controlling the memory allocated to PGA for fetched data.

Question 2

A critical PL/SQL procedure, `process_order`, is designed to update inventory levels and simultaneously log any exceptions to an audit table using an autonomous transaction. The procedure structure is as follows:

```sql
CREATE OR REPLACE PROCEDURE process_order (p_order_id IN NUMBER)
IS
  v_error_message VARCHAR2(4000);
BEGIN
  -- Main transaction logic
  UPDATE inventory SET quantity = quantity - 1 WHERE product_id = (SELECT product_id FROM orders WHERE order_id = p_order_id);
  COMMIT; -- Commit main transaction

-- Simulate an unexpected error after main commit
  RAISE_APPLICATION_ERROR(-20001, \'Simulated processing error.\');

EXCEPTION
  WHEN OTHERS THEN
    v_error_message := SQLERRM;
    -- Log error using autonomous transaction
    log_audit_entry(p_order_id, v_error_message);
    RAISE; -- Re-raise the exception
END;
/

CREATE OR REPLACE PROCEDURE log_audit_entry (p_order_id IN NUMBER, p_message IN VARCHAR2)
IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO audit_log (order_id, log_message, log_timestamp)
  VALUES (p_order_id, p_message, SYSTIMESTAMP);
  COMMIT; -- Commit the autonomous transaction
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK; -- Rollback the autonomous transaction if insertion fails
    RAISE;
END;
/
```

If `process_order` is called with a valid `p_order_id`, and the `RAISE_APPLICATION_ERROR` occurs after the main transaction\'s `COMMIT`, what will be the state of the `audit_log` table regarding the entry for this specific order?

Accepted Answer

The audit log entry will be present because the autonomous transaction committed independently before the main transaction's exception was handled.

Question 3

Consider a scenario where a PL/SQL procedure named `process_customer_orders` is designed to update the `order_status` in the `orders` table for a batch of customer orders fetched via a cursor. The procedure employs a `FORALL` statement with the `SAVE EXCEPTIONS` clause to handle potential errors during the update process, such as constraint violations. If the `FORALL` statement encounters an error on the 5th order it attempts to process, and the exception handler is correctly implemented to inspect `SQL%BULK_EXCEPTIONS`, what is the most accurate description of the execution flow and the state of the `orders` table and the `SQL%BULK_EXCEPTIONS` collection?

Accepted Answer

The `FORALL` statement will process the first 4 orders successfully, then halt execution. The `SQL%BULK_EXCEPTIONS` collection will contain one entry detailing the error for the 5th order, and the `orders` table will reflect the successful updates for the first 4 orders.

Question 4

Consider a PL/SQL procedure named `process_order` that attempts to insert a record into an `orders` table and then logs any exceptions encountered in a separate `error_log` table using an autonomous transaction procedure called `log_error`. If `process_order` encounters an unhandled exception after successfully inserting into `orders` but before committing, and the `log_error` procedure is called, which then commits its own transaction, what will be the ultimate outcome if `process_order` subsequently issues a `ROLLBACK`?

Accepted Answer

The order will be rolled back from the `orders` table, but the error will be successfully recorded in the `error_log` table.

Question 5

A senior developer is reviewing a PL/SQL procedure designed to process a substantial volume of records from a staging table into an operational table. The procedure employs `BULK COLLECT` with a `LIMIT` clause to fetch records in manageable chunks. However, within the loop that iterates through each fetched collection, individual `UPDATE` statements are executed for each record to apply business logic and stage the data. Analysis of the execution plan reveals numerous context switches between the PL/SQL and SQL engines, significantly impacting overall performance, particularly when the `LIMIT` value is set to a relatively small number. What strategic adjustment to the PL/SQL code would most effectively mitigate this performance bottleneck by reducing SQL context switching?

Accepted Answer

Replace the individual `UPDATE` statements within the loop with a single `FORALL` statement that operates on the entire fetched collection.

Question 6

Consider a PL/SQL package named `data_processing` with two procedures: `process_data` and `log_error`. The `log_error` procedure is declared with `PRAGMA AUTONOMOUS_TRANSACTION` and contains a `WHEN OTHERS` exception handler that logs the error message using `DBMS_OUTPUT.PUT_LINE` and then re-raises the exception using `EXCEPTION_INIT`. The `process_data` procedure calls `log_error` within its execution flow. If an unhandled exception occurs during the execution of `process_data` *before* the call to `log_error`, and `log_error` successfully catches and re-raises this exception, what will be the observable outcome in terms of `DBMS_OUTPUT` and transaction state if `process_data` also has an exception handler that logs a message and performs a `ROLLBACK` before its own `COMMIT` statement?

Accepted Answer

`log_error`'s output, `process_data`'s exception output, and `process_data`'s rollback occurs, preventing the commit.

Question 7

A senior PL/SQL developer is tasked with creating a robust procedure that dynamically queries a table whose column names might be provided as parameters. This procedure needs to be resilient to unexpected changes in the database schema or invalid column references passed during execution. If the dynamically generated SQL statement encounters an error during execution, such as a non-existent column or a table that has been altered, what is the most effective strategy to ensure the procedure does not terminate abruptly and can continue to process other operations, demonstrating adaptability to unforeseen data-related issues?

Accepted Answer

Implement a generic `WHEN OTHERS` exception handler around the `EXECUTE IMMEDIATE` statement to capture and manage any SQL execution errors.

Question 8

A PL/SQL procedure is designed to process a nested table of `order_line` records, where each record contains details like `product_id`, `quantity`, and `price`. The business requirement dictates that any order line with a `quantity` of zero should be excluded from further processing. The developer initially implements a loop using a standard `FOR i IN 1..collection.COUNT LOOP` structure and, upon finding a zero quantity, calls `collection.DELETE(i)`. What is the most significant drawback of this implementation strategy concerning the accurate processing of all valid order lines?

Accepted Answer

Elements with zero quantity are not actually removed, leading to redundant checks in subsequent iterations.

Question 9

A senior PL/SQL developer is tasked with refactoring a procedure that processes a batch of customer orders. The original procedure iterates through a cursor, updates the `order_status` to \'PROCESSED\' for each order, and then calls a separate notification procedure. The developer is concerned about the performance implications and potential for implicit cursor issues, especially if the notification procedure interacts with the same order data. What refactoring strategy best addresses these concerns while adhering to advanced PL/SQL development principles for efficiency and robustness?

Accepted Answer

Utilize BULK COLLECT to fetch order identifiers into collections, followed by a FORALL statement to update the order status, and then a separate loop to call the notification procedure for each processed order identifier.

Question 10

A senior PL/SQL developer is tasked with creating a robust order processing system. The system requires that any changes to an order\'s status be logged in a separate audit table, even if the primary order update fails. The developer implements a procedure `process_order` that updates the `order_details` table. Within an exception handler for `process_order`, a nested procedure `log_order_status` is called. `log_order_status` is declared with `PRAGMA AUTONOMOUS_TRANSACTION` and performs an `INSERT` into an `order_audit_log` table, followed by a `COMMIT`. The `process_order` procedure\'s exception handler establishes a `SAVEPOINT` named `before_order_log` before calling `log_order_status`, and then issues a `ROLLBACK TO before_order_log` after `log_order_status` returns. If an error occurs during the `UPDATE order_details` statement in `process_order`, what is the state of the `order_details` table and the `order_audit_log` table upon completion of the exception handler?

Accepted Answer

The `order_details` table is rolled back to its state before the `UPDATE` statement, and the `order_audit_log` table contains the new entry.

Question 11

Consider a PL/SQL procedure designed to update employee salary records. The procedure declares a user-defined exception named `invalid_salary_range`. Within the procedure, an `UPDATE` statement modifies salary data, followed by an unconditional `RAISE invalid_salary_range` statement. If the `UPDATE` statement executes successfully without raising any pre-defined exceptions, which exception handler will be invoked to manage the `invalid_salary_range` exception?

Accepted Answer

The `WHEN invalid_salary_range THEN` handler

Question 12

A team of developers is tasked with optimizing a critical PL/SQL package, `INVENTORY_MGMT_PKG`, responsible for updating stock levels across thousands of product SKUs daily. They\'ve identified that a procedure, `UPDATE_STOCK_BATCH`, which currently iterates through a cursor to update each item\'s stock quantity individually, is the primary cause of system slowdowns during peak processing hours. To address this, they plan to refactor the procedure to adopt a more efficient, set-based processing approach. Given the need to handle potential data inconsistencies and maintain high throughput, which PL/SQL construct combination would most effectively address the performance bottleneck and demonstrate adaptability to the changing processing requirements?

Accepted Answer

Utilize `BULK COLLECT INTO` to fetch multiple rows into an associative array, followed by a `FORALL` statement with the `SAVE EXCEPTIONS` clause to perform the DML operations on the entire collection.

Question 13

Consider a PL/SQL procedure, `process_order`, designed to retrieve and update customer order details. This procedure is called from an anonymous PL/SQL block. Within `process_order`, a query is executed to fetch a specific order record. If no record matches the provided order ID, a `NO_DATA_FOUND` exception is raised internally by the SQL engine. However, the `process_order` procedure itself does not contain a specific `WHEN NO_DATA_FOUND THEN` exception handler. The anonymous PL/SQL block that invokes `process_order` has a general `WHEN OTHERS THEN` exception handler. What is the most likely outcome when an order ID that does not exist is passed to `process_order`?

Accepted Answer

The `NO_DATA_FOUND` exception propagates from `process_order` to the anonymous block, where it remains unhandled by the `WHEN OTHERS THEN` clause and terminates the entire execution, displaying the standard `ORA-01403: no data found` error.

Question 14

A PL/SQL procedure is designed to apply bonus adjustments to employee salaries. It retrieves employee IDs and bonus amounts from a staging table `emp_bonus_data` and uses a `FORALL` statement with `SAVE EXCEPTIONS` to update the `salary` column in the `employee_salary` table. The `FORALL` loop iterates through a collection populated by `BULK COLLECT INTO`. Consider a scenario where `emp_bonus_data` contains 100 records, and the `employee_salary` table has corresponding entries for 95 of these employee IDs. For the remaining 5 employee IDs, there are no matching records in `employee_salary`. What would be the expected outcome regarding the number of successful updates and the error handling mechanism within the procedure, assuming the procedure iterates through the `SQL%BULK_EXCEPTIONS` collection to log errors?

Accepted Answer

95 rows will be successfully updated, and the `SQL%BULK_EXCEPTIONS` collection will contain 5 entries detailing the `NO_DATA_FOUND` exceptions for the non-existent employee IDs.

Question 15

Consider a PL/SQL procedure, `process_customer_orders`, designed to iterate through a collection of customer orders. Inside the loop, for each order, it invokes a separate procedure, `update_inventory`, to adjust stock levels. The `process_customer_orders` procedure contains a single `COMMIT` statement positioned after the loop concludes. If the `update_inventory` procedure encounters an issue and raises an unhandled exception during the processing of an order midway through the loop, what is the most likely outcome for the entire transaction initiated by `process_customer_orders`?

Accepted Answer

The transaction will be rolled back, and no orders will be committed.

Question 16

A critical PL/SQL package, `customer_management_pkg`, is being refactored to comply with new stringent data privacy regulations that mandate the anonymization of historical customer address data older than five years. The `update_customer_address` procedure within this package directly modifies the `customers` table. Consider a scenario where this procedure encounters an unexpected database constraint violation during the address update for a customer. Which of the following strategies best exemplifies adaptability and problem-solving by ensuring data integrity and compliance in the event of such an error, without necessarily committing the partial update?

Accepted Answer

Establish a savepoint immediately before the `UPDATE` statement for the customer's address and issue a `ROLLBACK TO SAVEPOINT` within an exception handler for the `UPDATE` operation, ensuring the transaction state reverts to before the address modification.

Question 17

A senior PL/SQL developer is tasked with creating a robust procedure to retrieve customer order summaries based on a `customer_id` provided by an external system. The procedure must dynamically construct a `SELECT` statement to query the `orders` table, filtering by the provided `customer_id`. The primary concern is to mitigate any potential SQL injection vulnerabilities that could arise from the external input. Which of the following approaches represents the most secure and recommended method for handling the dynamic SQL execution in Oracle 11g?

Accepted Answer

Utilize `EXECUTE IMMEDIATE` with a colon-prefixed placeholder in the SQL string and pass the `customer_id` parameter to the `USING` clause of the `EXECUTE IMMEDIATE` statement.

Question 18

Consider a PL/SQL block designed to retrieve a single record based on a unique identifier. The block utilizes a `SELECT INTO` statement with a `ROWNUM = 1` condition applied to a table that, for a specific input value, contains no matching records that satisfy the initial filtering criteria *before* the `ROWNUM` pseudocolumn is conceptually applied. Analyze the state of the implicit cursor attributes (`SQL%ROWCOUNT`, `SQL%FOUND`, `SQL%NOTFOUND`, `SQL%BULK_ROWCOUNT`) immediately after the execution of this `SELECT INTO` statement. Which combination accurately reflects this state?

Accepted Answer

SQL%ROWCOUNT is 0, SQL%FOUND is FALSE, SQL%NOTFOUND is TRUE

Question 19

A team of developers is building a high-throughput order processing system using Oracle 11g. They have developed a PL/SQL procedure named `process_order` that takes an `order_id` as input. To prevent race conditions when multiple sessions might try to process the same order simultaneously, the procedure includes the following snippet:

```sql
BEGIN
  SELECT quantity INTO v_quantity
  FROM orders
  WHERE order_id = p_order_id
  FOR UPDATE NOWAIT;

-- ... (further processing logic)

EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE = -54 THEN
      -- Log the conflict and return a specific error
      log_error(\'Order \' || p_order_id || \' is currently being processed by another session.\');
      RAISE_APPLICATION_ERROR(-20001, \'ERR_ORDER_BUSY\');
    ELSE
      RAISE; -- Re-raise other unexpected errors
    END IF;
END;
```

If another session has already acquired an exclusive lock on the row with `order_id = p_order_id` using `FOR UPDATE`, what is the most likely outcome of executing `process_order` in the current session?

Accepted Answer

The `process_order` procedure will raise the `ERR_ORDER_BUSY` application error.

Question 20

A senior developer is reviewing a PL/SQL package designed for e-commerce order fulfillment. The `process_customer_orders` procedure uses a cursor (`customer_cur`) to loop through active customer orders. Inside the loop, it calls a separate procedure, `update_inventory_level`, to adjust stock quantities. The developer notices that the logging mechanism within the loop, intended to record the number of customer records processed in each iteration, consistently reports an incorrect count, often zero even when orders are clearly being processed. The code snippet for the relevant section is:

```sql
FOR v_customer_rec IN customer_cur LOOP
    update_inventory_level(v_customer_rec.customer_id, v_customer_rec.order_id);
    IF SQL%ROWCOUNT = 0 THEN
        -- Log an error: No customer record processed
        DBMS_OUTPUT.PUT_LINE(\'Error: No customer record processed for customer \' || v_customer_rec.customer_id);
    ELSE
        -- Log success: Customer record processed
        DBMS_OUTPUT.PUT_LINE(\'Processed customer \' || v_customer_rec.customer_id || \' - Rows affected by inventory update: \' || SQL%ROWCOUNT);
    END IF;
END LOOP;
```

Considering the execution context and the behavior of implicit cursor attributes in Oracle PL/SQL, what is the fundamental reason for the inaccurate logging of processed customer records?

Accepted Answer

The `SQL%ROWCOUNT` attribute is evaluated after the `update_inventory_level` procedure call, reflecting the rows affected by that procedure rather than the `FETCH` operation of the `customer_cur` cursor.

Question 21

A team is developing a PL/SQL procedure to manage a high volume of incoming customer orders. The procedure must process each order, update inventory levels, and record transaction details. A key requirement is to maintain operational continuity by preventing the failure of the entire batch of orders if a single order encounters a specific, non-fatal issue, such as an invalid product identifier. However, if a more fundamental problem arises, like a missing customer record that prevents any meaningful transaction, the procedure must clearly flag this critical failure. Which PL/SQL exception handling strategy best supports this requirement for selective error management and operational resilience?

Accepted Answer

Implementing a single, generic `WHEN OTHERS THEN` exception handler that logs all errors and uses `RAISE_APPLICATION_ERROR` with a generic error code to signal a problem.

Question 22

A critical business application relies on a PL/SQL procedure, `process_customer_orders`, to update order totals based on new item additions. This procedure is executed by multiple concurrent user sessions, each potentially processing different orders or even the same order if it involves multiple independent modifications. During peak loads, it has been observed that the `total_amount` column in the `orders` table sometimes reflects an incorrect value, specifically missing the impact of one or more recent adjustments. For instance, if an order initially had a total of $100.00$, and two separate concurrent transactions attempted to add $25.00$ and $30.00$ respectively, the final total might incorrectly end up as $130.00$ instead of the expected $155.00$, indicating a lost update. Which of the following PL/SQL constructs or strategies is most effective in preventing such data loss due to concurrent modifications in this scenario?

Accepted Answer

Employing `SELECT ... FOR UPDATE` when fetching order details to ensure exclusive row access during the transaction.

Question 23

A critical PL/SQL procedure, `process_customer_orders`, is designed to fetch a single customer record using a `SELECT INTO` statement based on a provided `customer_id`. When a `customer_id` does not correspond to any record in the `customers` table, the `NO_DATA_FOUND` exception is raised. Which of the following error handling strategies best exemplifies adaptability and effective problem-solving by providing clear, actionable feedback to the calling environment while maintaining control over the execution flow?

Accepted Answer

Catch the `NO_DATA_FOUND` exception, log a specific message detailing the unretrieved `customer_id`, and return a custom status indicator signifying that the customer was not found.

Question 24

A PL/SQL procedure named `process_order` is designed to update order statuses and log any processing errors. It calls an autonomous transaction procedure, `log_error_details`, to record exceptions. The `process_order` procedure contains the following structure:

```sql
PROCEDURE process_order (p_order_id IN NUMBER) IS
  v_status VARCHAR2(50);
BEGIN
  -- Main transaction operations
  UPDATE orders SET status = \'PROCESSING\' WHERE order_id = p_order_id;

-- Call to autonomous transaction
  log_error_details(p_order_id, \'Order processing started\');

-- Further main transaction operations
  SELECT status INTO v_status FROM orders WHERE order_id = p_order_id;
  IF v_status = \'PROCESSING\' THEN
    -- Simulate an error scenario that will raise NO_DATA_FOUND
    UPDATE order_details SET quantity = quantity - 1 WHERE order_id = p_order_id AND detail_id = 999; -- Assuming detail_id 999 does not exist
  END IF;

COMMIT; -- Commits main transaction changes

EXCEPTION
  WHEN OTHERS THEN
    -- This exception handler in the main procedure is reached
    -- after the autonomous transaction has already encountered an error
    log_error_details(p_order_id, \'Error during order processing\');
    RAISE; -- Re-raise the exception
END process_order;

-- Autonomous transaction procedure
PROCEDURE log_error_details (p_log_id IN NUMBER, p_message IN VARCHAR2) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO order_log (log_id, log_message, log_timestamp)
  VALUES (p_log_id, p_message, SYSTIMESTAMP);
  COMMIT; -- Commits the autonomous transaction
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK; -- Rolls back the autonomous transaction on any error
    RAISE; -- Re-raise to be caught by the caller if necessary
END log_error_details;
```

If `process_order` is called with an `order_id` for which `order_details` with `detail_id = 999` does not exist, what will be the final state of the `order_log` table with respect to the entries related to this `order_id`?

Accepted Answer

The `order_log` table will not contain any new entries for this `order_id` from this specific execution.

Question 25

A senior PL/SQL developer is crafting a robust error-logging mechanism within a complex batch process. The main transaction performs a series of DML operations, followed by setting a savepoint named `sp_pre_log`. Subsequently, it calls a procedure `log_critical_failure` which is declared with `PRAGMA AUTONOMOUS_TRANSACTION`. Inside `log_critical_failure`, a `ROLLBACK` statement is executed to discard any partial logging attempts if the logging itself encounters an issue, and then the procedure exits. Upon returning to the main transaction, the developer issues a `ROLLBACK TO sp_pre_log`. What is the expected outcome of this sequence of operations?

Accepted Answer

The `ROLLBACK TO sp_pre_log` statement will successfully revert the main transaction's DML operations to the state at `sp_pre_log`, as the autonomous transaction's rollback is isolated.

Question 26

A senior PL/SQL developer is tasked with refactoring a legacy order processing system. The existing `process_order` procedure fetches order details from an `orders` table. The developer implements a nested PL/SQL block to encapsulate the data retrieval logic, including a specific exception handler for `NO_DATA_FOUND`. The intention is to log a detailed message indicating the missing order ID and then allow the calling application to handle the absence of data. What is the most appropriate action within the `NO_DATA_FOUND` exception handler to ensure the calling environment is aware of the failed retrieval and can react accordingly, while still providing the localized logging?

Accepted Answer

Use `RAISE;` to re-raise the captured exception after logging.

Question 27

A financial services firm utilizes an advanced PL/SQL procedure to process high-volume customer loan applications. During the validation phase, if a loan applicant\'s credit score falls below a predefined threshold, a custom exception named `credit_score_too_low` is explicitly raised. The procedure also incorporates standard exception handlers for `NO_DATA_FOUND` when retrieving applicant details and `TOO_MANY_ROWS` if an unexpected duplicate record is encountered. Considering the need for meticulous auditing, timely intervention by loan officers, and preventing the termination of the entire batch processing if a single application fails, which of the following error-handling strategies is most aligned with robust PL/SQL development and business continuity for this scenario?

Accepted Answer

Log the `credit_score_too_low` exception, set the application status to 'Rejected - Credit Threshold', and then raise a distinct exception `application_processing_failed` to be handled by a higher-level calling block for appropriate notification and potential manual review.

Question 28

Consider a PL/SQL procedure designed to count employees based on a provided last name. The procedure utilizes `DBMS_ASSERT.ENQUOTE_LITERAL` to safely incorporate the input last name into a dynamically constructed SQL query. If the input last name provided to the procedure is `O\'Malley\'s`, what will be the outcome of the executed dynamic SQL statement?

Accepted Answer

The dynamic SQL will execute successfully, returning the count of employees whose last name is precisely `O'Malley's`.

Question 29

A senior PL/SQL developer is tasked with refactoring a legacy application module that processes customer order data. The existing code frequently constructs SQL statements by concatenating user-provided order IDs and product codes directly into strings, which are then executed using `EXECUTE IMMEDIATE`. This practice has raised security concerns regarding potential SQL injection attacks. Considering the need for enhanced security and adherence to best practices for dynamic SQL execution in Oracle 11g, which refactoring strategy would most effectively mitigate the identified vulnerabilities while maintaining the procedure\'s functionality?

Accepted Answer

Modify the `EXECUTE IMMEDIATE` statements to incorporate bind variables using the `USING` clause for all dynamic parameters, ensuring that input values are treated as data and not executable code.

1z0146 Oracle 11g: Advanced PL/SQL Free Practice Test — 29 Questions

A lot more is already mapped out

About the 1z0146 Oracle 11g: Advanced PL/SQL Certification