 Posted 6/16/2010 11:49:37 AM
|
|
|
|
| Ora Error ORA-01788: CONNECT BY clause required in this query block I'm getting following error in oracle 11gR2:
SQL> select max(level) from test;
select max(level) from test
*
ERROR at line 1:
ORA-01788: CONNECT BY clause required in this query block
SQL> select level from dual;
select level from dual
*
ERROR at line 1:
ORA-01788: CONNECT BY clause required in this query block
|
|
|
Posted 6/16/2010 11:50:20 AM
|
|
|
|
| I issued the following to fix this ora error: ORA-01788: CONNECT BY clause required in this query block :
SQL> alter system set "_allow_level_without_connect_by"=true scope=both;
System altered.
SQL> select max(level) from test; MAX(LEVEL)
----------
0
|
|
|
Posted 6/16/2010 11:50:48 AM
|
|
|
|
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.
|
|
|
Posted 6/16/2010 11:52:34 AM
|
|
|
|
please suggest me the way to resolve the following:
Java Application pointed to Oracle10gR1 database earlier, recently the database is upgraded to 10gR2. I'm getting the following error
ORA-01788: CONNECT BY clause required in this query block
|
|
|
Posted 6/16/2010 11:53:07 AM
|
|
|
|
Please find the query below:
SELECT categories.title_desc, categories.cat_id, categories.parent_id,
categories.acronym, categories.ord, categories_det.title,
categories_det.summary,LEVEL,lang_id, visible
FROM categories, categories_det, status
WHERE categories.cat_id = categories_det.cat_id
AND categories_det.status_id = status.status_id
AND acronym = 'job'
AND region_id = 'ALL'
AND visible = 1
AND lang_id = 'en';
In the above query LEVEL is not working..
*
ERROR at line :
ORA-01788: CONNECT BY clause required in this query block
|
|
|
Posted 6/16/2010 11:53:28 AM
|
|
|
|
Of course LEVEL is not working, it is a pseudo-column indicating at which level of recursion in CONNECT BY you are.
As you have no CONNECT BY, this is not valid.
|
|
|
|