OraFAQ Forum: SQL & PL/SQL » How to Exclude Value from Random Generating Function

Home » SQL & PL/SQL » SQL & PL/SQL » How to Exclude Value from Random Generating Function (Oracle6i Forms,Oracle11g,Windows Server)

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

How to Exclude Value from Random Generating Function [message #513274]

Sat, 25 June 2011 10:24

jolly_makkar
Messages: 63
Registered: March 2008

Member

Hi Everyone!

I am in urgent need of Generating Random numbers for one of the application .The number once generated is getting stored in table

For this purpose i am using Oracle In-Built function

Quote:

Select round(dbms_random.value(1,30)) into a from dual;

Problem is :How to eliminate those numbers to generate which are already stored in Table.

Any Help would be worth.

Thanks & Regards.

Report message to a moderator

Re: How to Exclude Value from Random Generating Function [message #513275 is a reply to message #513274]

Sat, 25 June 2011 10:34

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Problem is :How to eliminate those numbers to generate which are already stored in Table.
UNIQUE INDEX on column & ignore ORA-00001 error

Report message to a moderator

Re: How to Exclude Value from Random Generating Function [message #513280 is a reply to message #513274]

Sat, 25 June 2011 13:54

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

Senior Member
Account Moderator

Why don't you use a sequence?

Do you realize that each time you get a number, it will take more time to get a new one? And it may take hours before you get the last couple ones.

Regards
Michel

Report message to a moderator

Re: How to Exclude Value from Random Generating Function [message #513292 is a reply to message #513280]

Sat, 25 June 2011 21:11

John Watson
Messages: 8989
Registered: January 2010
Location: Global Village

Senior Member

Quote:

Do you realize that each time you get a number, it will take more time to get a new one?

I didn't know that. Thank you.
If dbms_random isn't good enough, how about

orcl> select sys_guid() from dual;

SYS_GUID()
--------------------------------
B178A8FA4CCD4BEB8258DFDF7C5BBC61

orcl>

It isn't random, but will be unique.

Report message to a moderator

Re: How to Exclude Value from Random Generating Function [message #513294 is a reply to message #513292]

Sat, 25 June 2011 23:31

jolly_makkar
Messages: 63
Registered: March 2008

Member

Thankyou all for your contribution

I have found one solution .I will be storing 1 to 50 values in a table and generate random value from only those set of values.

SELECT column FROM
( SELECT column FROM table
ORDER BY dbms_random.value )
WHERE rownum = 1

After Generating Value ,i am deleting that specific value from table.

Its working for me .Thanks Once again.

Report message to a moderator

Re: How to Exclude Value from Random Generating Function [message #513295 is a reply to message #513294]

Sun, 26 June 2011 01:06

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

Senior Member
Account Moderator

John Watson wrote on Sun, 26 June 2011 04:11

Quote:
Do you realize that each time you get a number, it will take more time to get a new one?

I didn't know that. Thank you.

This has nothing to do with dbms_random but logic.
You want an integer number between 1 and 30 excluding those that are already been taken, this means you will reject the number if you pick one of these already tabken ones therefore more you get numbers less likely you will get a new one with the random function and more likely you will have to execute a new try.

Regards
Michel

Report message to a moderator

Re: How to Exclude Value from Random Generating Function [message #513296 is a reply to message #513294]

Sun, 26 June 2011 01:07

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

Senior Member
Account Moderator

jolly_makkar wrote on Sun, 26 June 2011 06:31

Thankyou all for your contribution

I have found one solution .I will be storing 1 to 50 values in a table and generate random value from only those set of values.

SELECT column FROM
( SELECT column FROM table
ORDER BY dbms_random.value )
WHERE rownum = 1

After Generating Value ,i am deleting that specific value from table.

Its working for me .Thanks Once again.

Until you will have 2 sessions that execute it at the same time (or unless you lock the table before picking a number).

Regards
Michel

Report message to a moderator

Previous Topic:	select only last date
Next Topic:	Unique problem with TRIGGER

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Sep 06 10:26:58 CDT 2025