The Oracle PL/SQL LEVEL Keyword

Term: LEVEL

Definition:
In Oracle PL/SQL, the term LEVEL refers to a pseudo column in Oracle which is used in a hierarchical query to identify the hierarchy level in numeric format. For each row returned by a hierarchical query, the LEVEL pseudocolumn returns 1 for a root row, 2 for a child of a root, and so on. In a tree-like structure, the lowest level is the root (LEVEL=1) and it progresses in unit incremental ratio.

Note that LEVEL must be used in the CONNECT_BY queries.

Example Usage:

The SELECT query below lists employees reporting to a Manager:
    SELECT EMPNO, MGR_ID, LEVEL
    FROM EMPLOYEES E
    START WITH MGR_ID = NULL
    CONNECT BY PRIOR EMPNO = MGR_ID;


It can also be used as 'n' row generator using recursive hierarchy. The query below generates 50 sequence numbers.
    SELECT LEVEL SEQ
    FROM DUAL
    CONNECT BY LEVEL <= 50;