1Z0-149 Sample Questions & 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)

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?

True or False: An INDEX BY table (associative array) defined within a PL/SQL package specification persists for the duration of the database session.

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?

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?

A PL/SQL procedure is created to update a table. Later, a column in that table is dropped. What happens the next time the procedure is invoked?

A developer is writing a cursor FOR loop to process employee data. Inside the loop, they need to perform an update on the exact row currently being processed by the cursor. Which two clauses are required to achieve this efficiently and safely? (Select TWO)

A junior developer has written the following procedure to grant a bonus, but it fails to compile.

CREATE OR REPLACE PROCEDURE grant_bonus (
p_emp_id IN employees.employee_id%TYPE,
p_bonus_pct IN NUMBER
)
IS
v_current_salary employees.salary%TYPE;
v_new_salary v_current_salary%TYPE;
BEGIN
SELECT salary INTO v_current_salary FROM employees WHERE employee_id = p_emp_id;
v_new_salary := v_current_salary * (1 + p_bonus_pct);
UPDATE employees SET salary = v_new_salary WHERE employee_id = p_emp_id;
COMMIT;
END grant_bonus;

A senior developer reviews the code and recommends creating it as a function instead of a procedure. What is the primary reason for this recommendation?

A developer needs to create two procedures, proc_A and proc_B, within the same package body. proc_A needs to call proc_B, and proc_B needs to call proc_A. This creates a mutual dependency. If proc_A is defined before proc_B in the package body, what will happen when the package body is compiled?

Case Study:

A healthcare provider is building a system to manage patient appointments. They have a PATIENTS table and an APPOINTMENTS table. A new requirement is to create a trigger that prevents a new appointment from being scheduled if the patient has more than three outstanding unpaid bills in the BILLS table.

The trigger must fire before a new row is inserted into the APPOINTMENTS table. It needs to check the bill count for the specific patient_id being inserted. If the count of unpaid bills is greater than three, the trigger should prevent the insertion and return a clear, user-friendly error message to the application.

The development team wants to ensure the solution is efficient and handles the error condition gracefully.

Which trigger implementation best satisfies these requirements?