After Upgrading To 10g, Getting ORA-01788 When Running A Query That Includes The LEVEL Pseudo-column
After Upgrading To 10g, Getting ORA-01788 When Running A Query That Includes The LEVEL Pseudo-column
Titleimage
Posted by Patrick Hamou on 2016:04:19 20:09:32
Applies To
Oracle Server - Enterprise Edition - Version: 10.2.0.1 and later [Release: 10.2 and later ]
Information in this document applies to any platform.
Symptoms
After upgrading to Oracle 10g, started getting an ORA-1788 error for a query that includes the LEVEL pseudo column. This query was running fine on 9i.
Example:
On 9i:
SQL> select level from dual;
LEVEL
----------
0On 10g:
SQL> select level from dual;
select level from dual
*
ERROR at line 1:
ORA-01788: CONNECT BY clause required in this query block
Cause
This is normal behavior in 10g. LEVEL pseudo column is meaningful only in hierarchical queries, which means queries involving CONNECT BY. However, we allowed this keyword without CONNECT BY until 10g.
In 10g, we started throwing an error message if level is encountered in a query block where there is no CONNECT BY.
Solution
Add a CONNECT BY clause to the query block that includes the LEVEL pseudo column:
SQL> select level from dual connect by 1<0;
LEVEL
----------
1Or you can use the workaround of setting the parameter _allow_level_without_connect_by to TRUE before running the query:
SQL> alter session set "_allow_level_without_connect_by"=TRUE;
Session altered.
SQL> select level from dual;
LEVEL
----------
0
Posted by Patrick Hamou on 2016:04:19 20:09:32