Oracle Magazine, Jan/Feb 2018
Database Developer and DBA ETL table real world data problems can cause a hierarchical query to crash with an error and return no data at all For example in Listing 4 the manager for employee KING is set to 7499 This introduces a logical corruption in the hierarchy definition for the employees in this company Traversing through the hierarchy in one direction indicates that KING is the manager of ADAMS but traversing in the opposite direction through the hierarchy indicates that ADAMS is the manager of KING There is now a cyclic relationship in the hierarchy To continuously follow the CONNECT BY syntax would infinitely traverse this hierarchy and never come to an end To avoid an endless loop the database will return an error when CONNECT BY encounters such a cyclic relationship Code Listing 4 Error stops endless loop in a hierarchical query SQL update emp set mgr 7499 2 where ename KING ORACLE MAGAZINE JANUARY FEBRUARY 2018 93 1 row updated SQL select sys_ connect_ by_ path ename 2 from emp 3 start with ename KING 4 connect by prior empno mgr 5 ERROR ORA 01436 CONNECT BY loop in user data
You must have JavaScript enabled to view digital editions.
