Home » SQL & PL/SQL » SQL & PL/SQL » Same Conect By query works fine in 9i but not in 10g - Please help (Oracle 10g)
icon5.gif  Same Conect By query works fine in 9i but not in 10g - Please help [message #394553] Fri, 27 March 2009 14:30 Go to next message
cts_srividya
Messages: 2
Registered: March 2009
Junior Member
Embarassed We recently moved our project from 9i to 10g. A connect by query works fine in 9i environment but same not working in 10g environment. Please help.

While trying with a sample table, that also behaves samem way. Please find the query and help on how to solve.
create table test_connect_by (
parent number,
child number,
constraint uq_tcb unique (child)
);

insert into test_connect_by values ( 5, 2);
insert into test_connect_by values ( 5, 3);
insert into test_connect_by values (18,11);
insert into test_connect_by values (18, 7);
insert into test_connect_by values (17, 9);
insert into test_connect_by values (17, 8);
insert into test_connect_by values (26,13);
insert into test_connect_by values (26, 1);
insert into test_connect_by values (26,12);
insert into test_connect_by values (15,10);
insert into test_connect_by values (15, 5);
insert into test_connect_by values (38,15);
insert into test_connect_by values (38,17);
insert into test_connect_by values (38, 6);
insert into test_connect_by values (null, 38);
insert into test_connect_by values (null, 26);
insert into test_connect_by values (null, 18);

COMMIT;

In Oracle 10g:
SQL> select level, lpad(' ',2*(level-1)) || to_char(child) s
2 from test_connect_by
3 start with level = 1
4 connect by prior child = parent;

no rows selected


In Oracle 9i:
SQL> select level, lpad(' ',2*(level-1)) || to_char(child) s
2 from test_connect_by
3 start with level = 1
4 connect by prior child = parent;

LEVEL S



--------------------------------------------------------------------------------
---------------
1 2
1 3
1 10
1 5
2 2
2 3
1 9
1 8
1 11
1 7
1 13
1 1
1 12
1 15
2 10
2 5
3 2
3 3
1 17
2 9
2 8
1 6
1 38
2 15
3 10
3 5
4 2
4 3
2 17
3 9
3 8
2 6
1 26
2 13
2 1
2 12
1 18
2 11
2 7

39 rows selected

[Updated on: Fri, 27 March 2009 15:11] by Moderator

Report message to a moderator

Re: Same Conect By query works fine in 9i but not in 10g - Please help [message #394561 is a reply to message #394553] Fri, 27 March 2009 15:21 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
There are a few possibilities.
One requires to set an hidden parameter and bounce the database. You do that with the word from oracle support.
Did you try this hint?
/*+ no_connect_by_filtering */ 

Please try and post the results.
Re: Same Conect By query works fine in 9i but not in 10g - Please help [message #394564 is a reply to message #394561] Fri, 27 March 2009 15:31 Go to previous messageGo to next message
cts_srividya
Messages: 2
Registered: March 2009
Junior Member
Thanks for responding.

The query is a working code in production environment. Thus any code change will undergo the software life cycle. Since it is a working code in 9i and not working only after migrating to 10g, I need to give a proper justification to my client, for any code change.

Please provide any code/parameter change also with justification as why that change is required in 10g for the code to work.

[Updated on: Fri, 27 March 2009 15:33]

Report message to a moderator

Re: Same Conect By query works fine in 9i but not in 10g - Please help [message #394566 is a reply to message #394564] Fri, 27 March 2009 15:34 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Well, It is a bug.
And, it is just a hint.
If you want justification, please raise a TAR with OSS.
This is just a forum.
Re: Same Conect By query works fine in 9i but not in 10g - Please help [message #394603 is a reply to message #394553] Sat, 28 March 2009 06:19 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Well, your query is not working.
There is some problem, and I am not able to solve it.

SQL>ed
Wrote file afiedt.buf

  1  select lpad(' ',2*(level-1)) || to_char(child) s
  2  from test_connect_by
  3  start with level = 1
  4* connect by prior child = parent
SQL>/
from test_connect_by
     *
ERROR at line 2:
ORA-01788: CONNECT BY clause required in this query block


SQL>select * from v$version;

BANNER
================================================================
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

5 rows selected.


I am requesting our GURUS to solve the issue.

regards,
Delna

[Updated on: Sat, 28 March 2009 06:23]

Report message to a moderator

Re: Same Conect By query works fine in 9i but not in 10g - Please help [message #394606 is a reply to message #394603] Sat, 28 March 2009 07:41 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Read my previous post?
>>One requires to set an hidden parameter
That would be
_allow_level_without_connect_by=true
Re: Same Conect By query works fine in 9i but not in 10g - Please help [message #394634 is a reply to message #394606] Sat, 28 March 2009 22:12 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8620
Registered: November 2002
Location: California, USA
Senior Member
It looks like just a case of bad coding practice to me. If you remove the unnecessary "start with level = 1" then it runs fine, as shown below.

SCOTT@orcl_11g> select banner from v$version
  2  /

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE	11.1.0.6.0	Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

SCOTT@orcl_11g> column level format 99999
SCOTT@orcl_11g> column s     format a15
SCOTT@orcl_11g> select level, lpad(' ',2*(level-1)) || to_char(child) s
  2  from test_connect_by
  3  start with level = 1
  4  connect by prior child = parent
  5  /

no rows selected

SCOTT@orcl_11g> select level, lpad(' ',2*(level-1)) || to_char(child) s
  2  from test_connect_by
  3  --start with level = 1
  4  connect by prior child = parent
  5  /

 LEVEL S
------ ---------------
     1 2
     1 3
     1 5
     2   2
     2   3
     1 10
     1 8
     1 9
     1 7
     1 11
     1 1
     1 12
     1 13
     1 6
     1 15
     2   5
     3     2
     3     3
     2   10
     1 17
     2   8
     2   9
     1 18
     2   7
     2   11
     1 26
     2   1
     2   12
     2   13
     1 38
     2   6
     2   15
     3     5
     4       2
     4       3
     3     10
     2   17
     3     8
     3     9

39 rows selected.

SCOTT@orcl_11g>

Re: Same Conect By query works fine in 9i but not in 10g - Please help [message #394783 is a reply to message #394634] Mon, 30 March 2009 05:58 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Or if you change START WITH LEVEL=1 to START WITH LEVEL=0 - that works too.
Previous Topic: Without temp table senario
Next Topic: Convert update sql into merge
Goto Forum:
  


Current Time: Fri Dec 02 16:59:49 CST 2016

Total time taken to generate the page: 0.11862 seconds