Use the pseudocolumn LEVEL : LEVEL « PL SQL Programming « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. PL SQL Programming
3. LEVEL

SQL>
SQL> CREATE TABLE books (
  2    isbn      VARCHAR2(10) PRIMARY KEY,
  3    parent_isbn VARCHAR2(10),
  4    series    VARCHAR2(20),
  5    category  VARCHAR2(20),
  6    title     VARCHAR2(100),
  7    num_pages NUMBER,
  8    price     NUMBER,
  9    copyright NUMBER(4));

Table created.

SQL>
SQL> INSERT INTO books (isbn, parent_isbn, series, category, title, num_pages, price, copyright)
  2    VALUES ('72191473', '72121467', 'Oracle PL/SQL', 'Oracle Server', 'Oracle9i PL/SQL Programming', 664, 49.99, 2002);

1 row created.

SQL>
SQL> INSERT INTO books (isbn, parent_isbn, series, category, title, num_pages, price, copyright)
  2    VALUES ('72121467', null, 'Oracle PL/SQL', 'Oracle Server', 'Oracle8i Advanced PL/SQL Programming', 772, 49.99, 2000);

1 row created.

SQL>
SQL> INSERT INTO books (isbn, parent_isbn, series, category, title, num_pages, price, copyright)
  2    VALUES ('72230665', '72191473', 'Oracle PL/SQL', 'Oracle Server', 'Oracle Database 10g PL/SQL Programming', 1008, 54.99, 2004);

1 row created.

SQL>
SQL> INSERT INTO books (isbn, parent_isbn, series, category, title, num_pages, price, copyright)
  2    VALUES ('72132302', null, 'Oracle Ebusiness', 'Oracle Ebusiness', 'Oracle E-Business Suite Financials Handbook', 820, 59.99, 2002);

1 row created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> SET SERVEROUTPUT ON
SQL>
SQL> PROMPT

SQL> PROMPT ** The following is a demonstration of LEVEL, and the
** The following is a demonstration of LEVEL, and the
SQL> PROMPT **  use of START WITH ... CONNECT BY PRIOR to display
**  use of START WITH ... CONNECT BY PRIOR to display
SQL> PROMPT **  parent/child hierarchical relationships.
**  parent/child hierarchical relationships.
SQL> PROMPT

SQL>
SQL> DECLARE
  2     v_level PLS_INTEGER;
  3     v_title BOOKS.TITLE%TYPE;
  4
  5     CURSOR cur_tree
  6     IS
  7        SELECT isbn, title, series
  8        FROM books;
  9  BEGIN
 10
 11  FOR l IN cur_tree
 12  LOOP
 13
 14  SELECT max(LEVEL)
 15  INTO v_level
 16  FROM books
 17  START WITH isbn = l.isbn
 18  CONNECT BY PRIOR parent_isbn = isbn;
 19
 20  DBMS_OUTPUT.PUT_LINE(l.title||' is book '
 21                       ||v_level||' in the '||l.series||' series');
 22
 23  END LOOP;
 24
 25  CLOSE cur_tree;
 26
 27  EXCEPTION
 28     WHEN OTHERS
 29     THEN
 30        DBMS_OUTPUT.PUT_LINE(sqlerrm);
 31  END;
 32  /
Oracle9i PL/SQL Programming is book 2 in the Oracle PL/SQL series
Oracle8i Advanced PL/SQL Programming is book 1 in the Oracle PL/SQL series
Oracle Database 10g PL/SQL Programming is book 3 in the Oracle PL/SQL series
Oracle E-Business Suite Financials Handbook is book 1 in the Oracle Ebusiness series
ORA-01001: invalid cursor

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL>
SQL> drop table books;

Table dropped.