# Free 1Z0-149 practice questions

> 10 free 1Z0-149 practice questions with the correct answer and an explanation.

Page: https://devopscodelab.com/exams/oracle-pl-sql/free-practice-questions/
Practice exams: https://devopscodelab.com/exams/oracle-pl-sql/

### Question 1 of 10

Which statement correctly assigns the next value of the sequence ORD_SEQ to a variable in PL/SQL in Oracle Database 19c?

- A. v_id := SEQUENCE.NEXTVAL('ord_seq');
- B. v_id := NEXTVAL(ord_seq);
- C. v_id := ord_seq.CURRVAL + 1;
- D. v_id := ord_seq.NEXTVAL;

**Answer:** D

**Explanation:** Since Oracle Database 11g, the NEXTVAL and CURRVAL pseudocolumns can be used directly in PL/SQL expressions, so the assignment is valid without SELECT ... FROM dual. NEXTVAL is not a function taking the sequence as an argument, CURRVAL + 1 does not advance the sequence and fails if NEXTVAL has not yet been called in the session, and there is no SEQUENCE package with a NEXTVAL function.

### Question 2 of 10

A block inserts an order header and then 20 lines. If any line insert fails, the header must remain but all lines must be undone, and the block should continue. Which approach meets the requirement?

- A. Issue COMMIT after each line insert so that failed lines do not affect earlier ones
- B. Wrap the header insert in an autonomous transaction and roll back the main transaction on error
- C. Rely on statement-level rollback, which undoes all line inserts automatically when one fails
- D. SAVEPOINT before the lines, and ROLLBACK TO it in a handler around the line inserts

**Answer:** D

**Explanation:** A savepoint marks the point after the header; rolling back to it in an exception handler removes only the line inserts while the header stays pending and processing continues. Committing each line keeps partial lines, an autonomous header would be committed independently and complicate the transaction, and statement-level rollback undoes only the single failing statement.

### Question 3 of 10

A developer must load 2 million rows from a cursor and process them in chunks without running out of PGA memory. Which technique is appropriate?

- A. Declare the collection as a VARRAY(2000000) so its memory is preallocated
- B. Use a cursor FOR loop, which always fetches the entire result set at once
- C. FETCH the cursor with BULK COLLECT INTO a collection LIMIT 1000 in a loop
- D. SELECT ... BULK COLLECT INTO a collection once without any LIMIT clause

**Answer:** C

**Explanation:** BULK COLLECT with LIMIT fetches a bounded number of rows per round trip, keeping memory usage predictable while still minimizing context switches. Collecting everything at once can exhaust PGA, a large VARRAY does not reduce memory use, and a cursor FOR loop does not fetch the entire result set at once (it array-fetches 100 rows at a time when optimized).

### Question 4 of 10

An explicit cursor is opened, and after OPEN another session commits an update that changes rows the cursor's query will return. What does the first session see as it fetches?

- A. Data as of the moment the cursor was opened, thanks to read consistency
- B. A mix of old and new values depending on which data blocks are still in the buffer cache
- C. An ORA-01555 error is raised as soon as the other session commits its change
- D. The newly committed values for rows that have not yet been fetched by the cursor

**Answer:** A

**Explanation:** The result set of a cursor is determined at OPEN time; Oracle uses undo to give a read-consistent view as of that point, so later commits by other sessions are not seen. ORA-01555 can occur only if the required undo is overwritten, not simply because of a commit, and the buffer cache does not affect the consistent view.

### Question 5 of 10 (choose 2)

Which two statements about the WHEN OTHERS handler are correct? (Choose two.)

- A. Inside it, SQLCODE and SQLERRM can be used to identify the error that was raised
- B. It also catches exceptions raised in the same block's declaration section
- C. It can be combined with a named exception using WHEN OTHERS OR NO_DATA_FOUND
- D. It must be the last handler in the exception section of a block
- E. It automatically re-raises the error to the caller after its statements complete

**Answer:** A, D

**Explanation:** WHEN OTHERS must appear last, and inside it SQLCODE and SQLERRM identify the actual error. Errors raised during declaration propagate to the enclosing block and are never seen by the same block's handlers. OTHERS cannot be combined with other names using OR, and once a handler finishes the exception is considered handled unless the code issues RAISE explicitly.

### Question 6 of 10 (choose 2)

Which two requirements must a stored function meet to be called from a SQL statement such as SELECT f(salary) FROM employees? (Choose two.)

- A. It must be created with AUTHID CURRENT_USER
- B. It must be declared DETERMINISTIC
- C. Its parameters must use IN mode only
- D. It must be defined inside a package rather than as a standalone function
- E. Its parameter and return types must be SQL-compatible, so a BOOLEAN parameter is not allowed in Oracle Database 19c

**Answer:** C, E

**Explanation:** Functions callable from SQL may only have IN parameters, and in Oracle Database 19c their parameter and return types must be SQL data types, so PL/SQL-only types such as BOOLEAN or records are rejected. DETERMINISTIC is an optimization hint, not a requirement. Standalone and packaged functions can both be called from SQL, and the rights model is irrelevant to callability.

### Question 7 of 10

In a package body, procedure calc_total calls the private procedure apply_discount, which is defined further down in the same body. Compilation fails with PLS-00313 (not declared in this scope). What is the correct fix without moving code?

- A. Add PRAGMA INLINE(apply_discount, 'YES') immediately before the call in calc_total
- B. Prefix the call with the package name, writing pkg_name.apply_discount, inside calc_total
- C. Declare apply_discount in the package specification so that it becomes public
- D. Add a forward declaration of apply_discount before calc_total in the body

**Answer:** D

**Explanation:** PL/SQL requires identifiers to be declared before use; a forward declaration (the subprogram specification ending with a semicolon) placed earlier in the body solves this while keeping the procedure private. Moving it to the spec would also work but needlessly exposes it. Qualifying the name does not change declaration order, and PRAGMA INLINE controls optimization only.

### Question 8 of 10

A reporting tool must run arbitrary ad hoc queries whose number and types of select-list columns are unknown until run time and describe each column. Which approach is required?

- A. EXECUTE IMMEDIATE with USING OUT bind arguments for every select-list column
- B. OPEN a strong REF CURSOR FOR the query string and fetch into a %ROWTYPE record
- C. The DBMS_SQL package, using DESCRIBE_COLUMNS and DEFINE_COLUMN
- D. EXECUTE IMMEDIATE with BULK COLLECT INTO a record of VARCHAR2 fields

**Answer:** C

**Explanation:** Native dynamic SQL needs the select list's shape at compile time; when the number or types of columns are unknown, DBMS_SQL (method 4) is needed because it can describe and define columns dynamically. A fixed record cannot adapt to unknown columns, a strong REF CURSOR cannot be opened for a dynamic string, and OUT binds do not return query columns.

### Question 9 of 10

A function is called from a WHERE clause of a large query and always returns the same output for the same input values, with no side effects. Which clause tells the optimizer it may reuse earlier results within the query?

- A. DETERMINISTIC
- B. PRAGMA AUTONOMOUS_TRANSACTION
- C. RESULT_CACHE RELIES_ON (employees)
- D. AUTHID DEFINER

**Answer:** A

**Explanation:** DETERMINISTIC declares that the function's result depends only on its arguments, allowing the database to avoid redundant calls and to use it in function-based indexes. RESULT_CACHE is a cross-session cache (RELIES_ON is deprecated and ignored since 11.2), AUTHID controls privilege checking, and an autonomous transaction is unrelated to result reuse.

### Question 10 of 10

A DBA needs to capture every error raised in the database, such as ORA-01555, along with the user who hit it. Which trigger event is appropriate?

- A. AFTER DDL ON DATABASE
- B. BEFORE SHUTDOWN ON DATABASE
- C. AFTER SERVERERROR ON DATABASE
- D. AFTER SUSPEND ON DATABASE

**Answer:** C

**Explanation:** An AFTER SERVERERROR trigger fires when a server error message is logged, and attribute functions such as ora_server_error give the error numbers. AFTER SUSPEND fires for resumable space allocation suspensions, BEFORE SHUTDOWN fires when the instance is shut down, and AFTER DDL fires after successful DDL commands.

Ready for a timed run? Take the free 15-question 1Z0-149 mini exam: https://devopscodelab.com/app/exams/oracle-pl-sql/mini/

DevOpsCode Lab is not affiliated with Oracle. 1Z0-149 and related names are trademarks of their owners. We write every question ourselves; these are not real exam questions.
