Free 1Z0-149 sample questions
Try these free 1Z0-149 sample questions, each with the right answer and an explanation. They test cursor handling, exception blocks and package design. Use them to find the PL/SQL topics you should study again.
Try them in the simulator Same questions, with study, timed and flashcard modes.
Showing 10 of 20 free sample questions.
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) sequenceDiagram participant PLSQL as PL/SQL Engine participant SQL as SQL Engine PLSQL->>SQL: OPEN CURSOR c_emp FOR SELECT ... FOR UPDATE; SQL-->>PLSQL: Lock rows and return cursor handle loop For each row PLSQL->>SQL: FETCH c_emp SQL-->>PLSQL: Return current row data PLSQL->>SQL: UPDATE employees SET ... WHERE CURRENT OF c_emp; SQL-->>PLSQL: Update locked row end PLSQL->>SQL: CLOSE c_emp SQL-->>PLSQL: Release locks
A junior developer has written the following procedure to grant a bonus, but it fails to compile. ```sql 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?
A developer needs to execute a DDL statement, such as `TRUNCATE TABLE`, from within a PL/SQL procedure. The name of the table to be truncated is passed as a parameter to the procedure. Which method should be used to execute the DDL statement?
A PL/SQL procedure is defined with `AUTHID CURRENT_USER`. The procedure updates the `SALES` table. User `SCOTT`, who owns the procedure, grants `EXECUTE` privilege on it to user `JANE`. User `JANE` does not have any direct privileges on the `SALES` table. What happens when `JANE` executes the procedure?
What is the primary purpose of enabling compile-time warnings using `ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL'`?
True or False: A user-defined PL/SQL record type can be used as a column data type in a database table.
A procedure processes a large set of rows fetched using `BULK COLLECT`. After processing, it needs to insert the modified data into a history table. Which statement provides the most performant way to insert all the records from the collection into the history table?
10 more free samples are waiting
Create a free account to unlock the whole 1Z0-149 sample bank, or get full access to all 320 practice questions in the simulator.