Use _allow_level_without_connect_by to Revert Back to 9i Hierarchical Query Behavior

(Doc ID 271939.1)

Last updated on MARCH 01, 2017

Applies to:

Oracle Server - Enterprise Edition - Version:
This problem can occur on any platform.
CONNECT BY / START

Symptoms

Using a query like the following works fine in 9i, but will generate errors in 10g.

NOTE: This query comes from sample code found at http://otn.oracle.com/oramag/webcolumns/2003/techarticles/gennick_connectby.html

SELECT level, assembly_id, assembly_name, parent_assembly
FROM bill_of_materials
UNION
SELECT level, assembly_id, assembly_name, parent_assembly
FROM bill_of_materials
START WITH parent_assembly IS NULL
CONNECT BY parent_assembly = PRIOR assembly_id;

fails on 10g with

ORA-01788: CONNECT BY clause required in this query block

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms