Pseudo-column(Dummy) LEVEL in PL/SQL Asp.Net C#

Pseudo-column: Pseudo-columns are not actual columns in a table, it is an Oracle assigned value  used in the same context as an Oracle Database column, but not stored on disk. However, pseudo-columns are treated like columns, so their values must be SELECTED from a table. Sometimes it is convenient to select pseudo-column values from a dummy table, it is not physically existed in the table. If we want to use these column by specifying externally in select statement for projection or selection. but we can't insert, delete and update pseudo columns.

LEVEL: LEVEL is a pseudo-column used with CONNECT BY and denotes the node level of the tree structure, it is a special function in hierarchical queries-it returns the position of any row in the hierarchy. The LEVEL pseudo-column is an indication of how deep in the tree one is. Oracle can handle queries with a depth of up to 255 levels. Whenever you use the START WITH.......CONNECT BY clauses in a hierarchical query, you can use the pseudo-column LEVEL to return the level number for each row returned by the query.

The hierarchy of pseudo-column Level is shows in figure given below: Suppose four employees working under one supervisor in two groups, So the hierarchy which shows three level are given below:

Example:

SQL> CREATE TABLE magazine (
2    ID      VARCHAR2(10) PRIMARY KEY,
3    parent_ID VARCHAR2(10),
4    series    VARCHAR2(20),
5    category  VARCHAR2(20),
6    title     VARCHAR2(100),
7    num_pages NUMBER,
8    price     NUMBER,
9    Licence_no NUMBER(4));
Table created.
SQL>
SQL> INSERT INTO magazine (ID, parent_ID, series, category, title, num_pages, price, Licence_no)
2    VALUES ('1', '2', 'Database', 'Database Server', 'SQL', 664, 49.99, 2002);
1 row created.
SQL>
SQL> INSERT INTO magazine (ID, parent_ID, series, category, title, num_pages, price, Licence_no)
2    VALUES ('2', null, 'Database', 'Database Server', 'Java', 772, 49.99, 2000);
1 row created.
SQL>
SQL> INSERT INTO magazine (ID, parent_ID, series, category, title, num_pages, price, Licence_no)
2    VALUES ('3', '1', 'Database', 'Database Server', 'XML', 1008, 54.99, 2004);
1 row created.
SQL>
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
2     v_level PLS_INTEGER;
3     v_title magazine.TITLE%TYPE;
4     CURSOR cur_tree IS SELECT ID, title, series FROM magazine;
5  BEGIN
6  FOR l IN cur_tree
7  LOOP
8      SELECT max(LEVEL) INTO v_level FROM magazine
9      START WITH ID = l.ID
10      CONNECT BY PRIOR parent_ID = ID;
11
12      DBMS_OUTPUT.PUT_LINE(l.title||' is magazine '||v_level||' in the '||l.series||' series');
13
14  END LOOP;
15  CLOSE cur_tree;
16  EXCEPTION
17     WHEN OTHERS
18     THEN
19        DBMS_OUTPUT.PUT_LINE(sqlerrm);
20  END;
21  /

SQL is magazine 2 in the Database series
Java is magazine 1 in the Database series
XML is magazine 3 in the Database series
ORA-01001: invalid cursor

PL/SQL procedure successfully completed.