OraFAQ Forum: SQL & PL/SQL » generate numbers between 1 to 100

Home » SQL & PL/SQL » SQL & PL/SQL » generate numbers between 1 to 100 (oracle 9i, Linux)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

generate numbers between 1 to 100 [message #333046]

Thu, 10 July 2008 06:40

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

Report message to a moderator

Re: generate numbers between 1 to 100 [message #333052 is a reply to message #333046]

Thu, 10 July 2008 06:52

rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA

Senior Member

You can use Dual Table with level keyword or Pipelined Functions.

Regards,
Rajat

Report message to a moderator

Re: generate numbers between 1 to 100 [message #333054 is a reply to message #333046]

Thu, 10 July 2008 06:56

flyboy
Messages: 1903
Registered: November 2006

Senior Member

http://www.orafaq.com/wiki/Oracle_Row_Generator_Techniques

Report message to a moderator

Re: generate numbers between 1 to 100 [message #333055 is a reply to message #333052]

Thu, 10 July 2008 06:57

sajut
Messages: 69
Registered: January 2007

Member

thank you for your reply. Could you elaborate on using the dual table

Report message to a moderator

Re: generate numbers between 1 to 100 [message #333056 is a reply to message #333055]

Thu, 10 July 2008 06:58

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

Report message to a moderator

Re: generate numbers between 1 to 100 [message #333058 is a reply to message #333056]

Thu, 10 July 2008 07:01

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

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: generate numbers between 1 to 100 [message #333063 is a reply to message #333062]

Thu, 10 July 2008 07:14

sajut
Messages: 69
Registered: January 2007

Member

Thank you all, thanks a lot. It worked
Saju

Report message to a moderator

Re: generate numbers between 1 to 100 [message #333064 is a reply to message #333062]

Thu, 10 July 2008 07:17

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

Report message to a moderator

Re: generate numbers between 1 to 100 [message #333066 is a reply to message #333064]

Thu, 10 July 2008 07:21

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

And this is a bug in oracle older versions.

Bug number?

Regards
Michel

Report message to a moderator

Re: generate numbers between 1 to 100 [message #333074 is a reply to message #333066]

Thu, 10 July 2008 07:46

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

Report message to a moderator

Previous Topic:	Restrict Permission
Next Topic:	how to fetch last inserted record from oracle table?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Apr 26 16:02:47 CDT 2026