Data Retrieval in PL/SQL – SELECT, LEVEL, and Hierarchical Queries

Introduction to Data Retrieval in PL/SQL

Data Retrieval in PL/SQL goes beyond simple SELECT queries. Oracle supports:

Standard SQL SELECT statements
Hierarchical queries using LEVEL
Pattern matching using Regular Expressions
Full-text search with Oracle Text

This article explores how to retrieve both flat and hierarchical data in PL/SQL with real-world examples.

All PL/SQL code—whether part of an anonymous block, stored procedure, function, trigger, or package—starts with a block. Even the most complex Oracle application is built from these simple building blocks.

Basic SELECT Statement in PL/SQL

The basic SELECT syntax is:

SELECT column_list
INTO variable_list
FROM table_list
[WHERE condition]
[ORDER BY column_list];

SELECT: Columns, expressions, or *
INTO: Stores result into one or more PL/SQL variables
FROM: One or more tables or subqueries
WHERE: Filters the result set
ORDER BY: Sorts the result

Variables and INTO Clause

PL/SQL requires SELECT INTO to fetch values into variables. These variables must match the number and data types of the selected columns.

The basic SELECT syntax is:

DECLARE
  v_title BOOKS.TITLE%TYPE;
BEGIN
  SELECT title INTO v_title FROM books WHERE isbn = '72230665';
  DBMS_OUTPUT.PUT_LINE(v_title);
END;

Handling SELECT Exceptions

You must ensure exactly one row is returned. Otherwise:

No rows:
ORA-01403: no data found
Multiple rows:
ORA-01422: exact fetch returns more than requested number of rows

You can trap these using:

EXCEPTION
  WHEN NO_DATA_FOUND THEN ...
  WHEN TOO_MANY_ROWS THEN ...

Hierarchical Data Retrieval in PL/SQL

Some data is structured hierarchically — like employees and managers, or books in a series. To navigate these relationships, Oracle uses:

LEVEL pseudocolumn
START WITH
CONNECT BY PRIOR

Column	Description
`ISBN`	Unique ID for book
`PARENT_ISBN`	References previous book
`TITLE`	Book title
`SERIES`	Series name

Example: Using LEVEL, START WITH, CONNECT BY

SET SERVEROUTPUT ON
DECLARE
  v_level PLS_INTEGER;
  v_title BOOKS.TITLE%TYPE;

  CURSOR cur_tree IS
    SELECT isbn, title, series FROM books;

BEGIN
  FOR l IN cur_tree LOOP
    SELECT MAX(LEVEL)
    INTO v_level
    FROM books
    START WITH isbn = l.isbn
    CONNECT BY PRIOR parent_isbn = isbn;

    DBMS_OUTPUT.PUT_LINE(l.title || ' is book ' ||
      v_level || ' in the ' || l.series || ' series');
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/

Best Practices for Data Retrieval in PL/SQL

Always handle exceptions for SELECT INTO.
Use %TYPE to keep variable types consistent with table columns.
Use hierarchical queries for recursive relationships.
For multi-row SELECTs, use cursors or bulk collect.

Learn more about Oracle SELECT Syntax – SQL Reference

AmantPoint Exclusive Learning Series
© 2025 AmantPoint. All rights reserved. Educational content for developers and learners.