ORA-01788

Oracle Server - Enterprise Edition - Version: 10.2.0.3
This problem can occur on any platform.
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
----------
0

On 10g:

SQL> select level from dual;
select level from dual
*
ERROR at line 1:
ORA-01788: CONNECT BY clause required in this query block

Solution:

SQL> alter system set "_allow_level_without_connect_by"=true scope=spfile;

or if using a pfile add the line

_allow_level_without_connect_by=true

at the beginning of the pfile.


Both cases will require a restart of the database to take effect.