Oracle Magazine, September/October 2018
Database Developer and DBA ETL ORACLE MAGAZINE SEPTEMBER OCTOBER 2018 97 7566 JONES 7839 7698 BLAKE 7839 7782 CLARK 7839 7499 ALLEN 7698 7521 WARD 7698 7654 MARTIN 7698 7788 SCOTT 7566 7844 TURNER 7698 7900 JAMES 7698 7902 FORD 7566 7934 MILLER 7782 7369 SMITH 7902 7876 ADAMS 7788 The two sides of the UNION ALL are analogous to two clauses in the conventional hierarchical syntax in that an entry point into the hierarchy is defined followed by the definition of how to use CONNECT BY or recursively traverse the hierarchy It is the developer who now takes control of the conventional pseudofunctions available in the conventional hierarchical syntax For example if I need to print out the depth of the hierarchy as I would typically do with the LEVEL pseudofunction I can add a column to increment the level each time I recursively pass through EACH_ LEVEL Listing 12 shows this in action The upper part of the UNION ALL must by definition be level 1 because it is the entry point and as the recursive statement under the UNION ALL is executed the level increments by 1 each time I recursively navigate through the hierarchy
You must have JavaScript enabled to view digital editions.