Home » SQL & PL/SQL » SQL & PL/SQL » Problem in Connect By
Problem in Connect By [message #234489] Tue, 01 May 2007 15:13 Go to next message
Nirmala
Messages: 43
Registered: October 2004
Member
Hi,

I have a question regarding the generation of a sequence of numbers using the connect by clause and dual table.


SQL>   select level data
  2      from dual
  3    connect by level <= 20;

      DATA                                                                      
----------                                                                      
         1                                                                      

Elapsed: 00:00:00.00
SQL> create table t
  2  as
  3    select level data
  4      from dual
  5    connect by level <= 20;

Table created.

Elapsed: 00:00:00.00
SQL> select * from t;

      DATA                                                                      
----------                                                                      
         1                                                                      
         2                                                                      
         3                                                                      
         4                                                                      
         5                                                                      
         6                                                                      
         7                                                                      
         8                                                                      
         9                                                                      
        10                                                                      
        11                                                                      

      DATA                                                                      
----------                                                                      
        12                                                                      
        13                                                                      
        14                                                                      
        15                                                                      
        16                                                                      
        17                                                                      
        18                                                                      
        19                                                                      
        20                                                                      

20 rows selected.



I am unable to understand how this is possible. I means why does the query give only one row when executed and when i create a table using the same query how does it contain 20 rows. When i execute the same set of queries in Oracle SQL Developer i get 10 rows for the first SQL and if i execute the sql in TOAD i get 20 rows. So i am wondering if there is a environment variable which determines the number of rows that are output in the first query.
I would appreciate any help in solving this puzzle.
Re: Problem in Connect By [message #234495 is a reply to message #234489] Tue, 01 May 2007 15:49 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Everything seems to be OK on my Oracle 10.2.0.1.0; your database version might help. Did you, perhaps, set *something* in SQL Developer?

./fa/2400/0/
  • Attachment: level.JPG
    (Size: 31.20KB, Downloaded 263 times)
Re: Problem in Connect By [message #234505 is a reply to message #234489] Tue, 01 May 2007 17:08 Go to previous messageGo to next message
Nirmala
Messages: 43
Registered: October 2004
Member
No i have'nt set anything SQL Developer. My version of oracle is
Oracle9i Enterprise Edition Release 9.2.0.4.0
Re: Problem in Connect By [message #234524 is a reply to message #234505] Wed, 02 May 2007 00:40 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Dual is special. sqlplus 'knows' there is only 1 row in dual, so it stops after 1 row.
do a select * from (<your-query>)
Re: Problem in Connect By [message #234536 is a reply to message #234489] Wed, 02 May 2007 01:15 Go to previous message
Nirmala
Messages: 43
Registered: October 2004
Member
Thanks Frank that seems to work.
Previous Topic: Trouble with FK and PK ref from another table
Next Topic: Pipelined functions
Goto Forum:
  


Current Time: Fri Dec 02 17:01:34 CST 2016

Total time taken to generate the page: 0.06971 seconds