Home » SQL & PL/SQL » SQL & PL/SQL » generate numbers between 1 to 100 (oracle 9i, Linux)
generate numbers between 1 to 100 [message #333046] Thu, 10 July 2008 06:40 Go to next message
sajut
Messages: 69
Registered: January 2007
Member
Hi All

I want to generate numbers, say, from 1 to 5000, to find out the missing serial numbers in a table. Any help
Re: generate numbers between 1 to 100 [message #333052 is a reply to message #333046] Thu, 10 July 2008 06:52 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
You can use Dual Table with level keyword or Pipelined Functions.

Regards,
Rajat
Re: generate numbers between 1 to 100 [message #333054 is a reply to message #333046] Thu, 10 July 2008 06:56 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
http://www.orafaq.com/wiki/Oracle_Row_Generator_Techniques
Re: generate numbers between 1 to 100 [message #333055 is a reply to message #333052] Thu, 10 July 2008 06:57 Go to previous messageGo to next message
sajut
Messages: 69
Registered: January 2007
Member
thank you for your reply. Could you elaborate on using the dual table
Re: generate numbers between 1 to 100 [message #333056 is a reply to message #333055] Thu, 10 July 2008 06:58 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
select 
   * 
from 
  (
    select rownum from dual connect by level<=5000
  )


If you are using 9i then you have to use outer query because of bug in sql*plus 9i.In case of 10g the inner query will work fine.


Regards,
Rajat
Re: generate numbers between 1 to 100 [message #333058 is a reply to message #333056] Thu, 10 July 2008 07:01 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

select level l from dual connect by level <= 5000

[Updated on: Thu, 10 July 2008 07:01]

Report message to a moderator

Re: generate numbers between 1 to 100 [message #333062 is a reply to message #333056] Thu, 10 July 2008 07:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
because of bug in sql*plus 9i

It is NOT a bug, it is how it works in 9i.

@tahpush, @rajatratewal
Why 5000 if question is up to 100?

Regards
Michel
Re: generate numbers between 1 to 100 [message #333063 is a reply to message #333062] Thu, 10 July 2008 07:14 Go to previous messageGo to next message
sajut
Messages: 69
Registered: January 2007
Member
Thank you all, thanks a lot. It worked
Saju
Re: generate numbers between 1 to 100 [message #333064 is a reply to message #333062] Thu, 10 July 2008 07:17 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
sajut wrotes on Thu, 10 July 2008 06:40
Hi All
I want to generate numbers, say, from 1 to 5000, 
to find out the missing serial numbers in a table. 
Any help 


Subject line is from 1 to 100.But he actually asked for 1 to 5000.

And this is a bug in oracle older versions.
If you do count(1) from inner query it will show
5000 but when you display it it will show less record.

Regards,
Rajat
Re: generate numbers between 1 to 100 [message #333066 is a reply to message #333064] Thu, 10 July 2008 07:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
And this is a bug in oracle older versions.

Bug number?

Regards
Michel
Re: generate numbers between 1 to 100 [message #333074 is a reply to message #333066] Thu, 10 July 2008 07:46 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Does it matter?
When the queries
select level from dual connect by level <=50;
and
select *
from (select level from dual connect by level <=50);
return different sets, it's fairly futile to argue that the database is behaving correctly
Previous Topic: Restrict Permission
Next Topic: how to fetch last inserted record from oracle table?
Goto Forum:
  


Current Time: Sat Dec 10 20:12:31 CST 2016

Total time taken to generate the page: 0.18242 seconds