UUS Practice Tests⌕ Search
IT Certifications · 1Z0-149

Oracle Database Program with PL/SQL Exam Practice Test

PL/SQL programming combines SQL data operations with procedural control flow inside Oracle Database. This practice page centers on code-level concepts such as collections, cursor processing, runtime SQL, built-in functions, exceptions, and data types, alongside relational constraints used to protect data integrity.

Exam overview

Exam providerOracle
Exam code1Z0-149
CategoryIT Certifications

About the Oracle Database Program with PL/SQL Exam

PL/SQL work involves choosing between implicit and explicit cursor handling, interpreting cursor attributes, and structuring loops to process query results. Developers also use dynamic SQL when statement text depends on runtime conditions, and handle exceptions so database errors produce controlled outcomes. Data-oriented decisions include selecting suitable types for dates and timestamps, using functions to manage null values or inspect strings, and understanding how sequences generate values. Relational constraints, including foreign keys, help enforce valid relationships between tables. These skills support writing procedures and other database program units that retrieve, modify, and validate data while accounting for transaction and error behavior. The available practice questions sample several of these concepts rather than establishing a complete or official exam outline.

Sample Practice Questions

Review a selection from this exam's published US Practice Tests practice bank.

Question 1

Which collection type is designed to hold a variable number of elements and can be indexed by integer values?

  1. AAssociative array
  2. BPackage specification
  3. CCursor variable
  4. DObject privilege
Show answerCorrect answer: A

A PL/SQL associative array is an index-by collection whose elements are accessed using keys, commonly integers or strings. It can grow or shrink as elements are added or removed.

Question 2

A table has a foreign key from `line_items.order_id` to `orders.order_id`. What does this constraint primarily ensure?

  1. AEvery line item has an order_id value that refers to an existing parent order, unless the foreign key is null.
  2. BEvery order has at least one line item.
  3. CThe order_id values in line_items are generated in increasing order.
  4. DThe referenced order row cannot be updated or deleted under any circumstances.
Show answerCorrect answer: A

A foreign key enforces referential integrity: a non-null child key must match a referenced key in the parent table. It does not require every parent to have a child, and delete or update behavior depends on the constraint and operation.

Question 3

A PL/SQL program needs to find the number of characters in a VARCHAR2 value. Which built-in function is intended for this task?

  1. ALENGTH
  2. BROUND
  3. CMOD
  4. DTRUNC
Show answerCorrect answer: A

LENGTH returns the length of a character value. ROUND, MOD, and TRUNC are numeric functions and are not used to measure a string's character length.

Question 4

Which statement about a PL/SQL cursor FOR loop is correct?

  1. AThe programmer must explicitly open and close the cursor in the loop body.
  2. BThe loop automatically opens the cursor, fetches rows, and closes it when finished.
  3. CThe loop can process only one row from a query.
  4. DThe loop requires a BULK COLLECT clause.
Show answerCorrect answer: B

A cursor FOR loop manages the cursor lifecycle automatically: it opens the cursor, fetches each row into the loop record, and closes the cursor when the loop ends, including when control leaves the loop due to an exception.

Question 5

A PL/SQL procedure needs to run a statement whose text is known only at runtime. Which feature is designed for this?

  1. AStatic SQL
  2. BDynamic SQL using `EXECUTE IMMEDIATE`
  3. CA cursor attribute
  4. DA package initialization section
Show answerCorrect answer: B

Dynamic SQL allows a program to construct and execute SQL or PL/SQL text at runtime. `EXECUTE IMMEDIATE` is a standard PL/SQL statement for executing dynamic SQL statements.

Question 6

Which statement best describes the purpose of a database sequence?

  1. AIt stores a reusable query definition for a table.
  2. BIt generates numeric values, commonly for identifiers.
  3. CIt enforces that a column contains only distinct values.
  4. DIt records every change made by a transaction.
Show answerCorrect answer: B

A sequence is a database object that generates numeric values on request. It is often used to supply identifier values, although a sequence itself does not enforce uniqueness in a table column.

Question 7

An explicit cursor has been opened and a fetch is attempted. Which cursor attribute gives the number of rows fetched so far?

  1. A`cursor_name%ROWCOUNT`
  2. B`cursor_name%ISOPEN`
  3. C`cursor_name%FOUND`
  4. D`cursor_name%NOTFOUND`
Show answerCorrect answer: A

For an explicit cursor, `%ROWCOUNT` reports the number of rows fetched so far. `%ISOPEN` reports whether it is open, while `%FOUND` and `%NOTFOUND` describe the most recent fetch result.

How to use this practice test

Access the free practice session to answer up to 20 random questions from the 50-question bank. After submitting, review the results to identify concepts that need more work, such as cursor behavior, exception handling, or SQL and PL/SQL data operations.

Preparation tips

  • Write small PL/SQL examples that compare cursor FOR loops with explicit OPEN, FETCH, and CLOSE processing; check what cursor attributes report after each fetch.
  • Practice constructing dynamic SQL with runtime-supplied statement text, and distinguish values passed as bind variables from identifiers that form part of the SQL text.
  • Trace exceptions from failing INSERT statements, including unique-constraint violations, and note how a local handler changes the procedure's behavior.
  • Compare Oracle character, date, and timestamp data types, then test functions such as LENGTH and null-handling expressions against representative values.
  • Review how primary-key and foreign-key constraints relate parent and child rows, and how a sequence can supply generated numeric values.
Official sourceOfficial exam page ↗