Oracle Random Number Generator Stored Procedure without using DBMS_RANDOM

Question

I don't have access to the DBMS_RANDOM package, so I would like to create my own stored procedure to generate random numbers in Oracle. Does anyone know how I might do that?

Thanks in advance!

EDIT: I am trying to generate a random row from each state. This is my code:

SELECT Z.* FROM ( 
      SELECT A.*, ROW_NUMBER() OVER (PARTITION BY A.STATE ORDER BY (
          select to_char(systimestamp,'ff') from dual)) AS ROW_ID 
        FROM   STATE_TABLE A ) Z WHERE Z.ROW_ID=1;

It gives me the same rows everytime I run it, but I want it to give me different rows everytime I run it. Help please?

Use the dbms_crypto package instead? Sure, you could look up any number of pseudo-random number generation algorithms and implement one of these in PL/SQL. Doing so, however, would be rather insane-- if the DBAs don't want to give you access to the dbms_random package, I am hard-pressed to imagine why they would want you to build your own solution that is likely to be much less efficient, much less standard, and much less random. — Justin Cave
– Justin Cave, Commented Jun 2, 2015 at 20:09
"we would like to get this asap so we're trying to create it ourselves." This sound kind of contradictory to me. That being said, how strong has to be that pseudo-random number generator ? For what kind of purpose is it ? In many, if not all, cases, writing his own pseudo-random number generator is a bad idea -- unless you know exactly what your are doing... — Sylvain Leroux
– Sylvain Leroux, Commented Jun 2, 2015 at 20:37
Obligatory xkcd - xkcd.com/221. Is this for a real project or a homework assignment? I am hard-pressed to imagine why you'd want to do this in a real system or how you could possibly justify the computational expense of doing so in a real system. Compounding the issue by saying that you don't have time to get the actual privileges you need so you want to write your own even less efficient random number generator seems crazy. — Justin Cave
– Justin Cave, Commented Jun 2, 2015 at 20:53

Martina · Accepted Answer · 2015-06-02 20:56:18Z

4

If you want to select random rows from the table you can use SAMPLE clause

SELECT * FROM mytable SAMPLE(4);

This gives you randomly selected 4% of all rows.

answered Jun 2, 2015 at 20:56

Martina

9297 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Alex Poole · Accepted Answer · 2015-06-02 23:00:28Z

3

If your DBAs refuse to give you access to dbms_random (I have experienced this, for security reasons, but arguing enough got an exemption in the end) you could create a Java call:

create or replace function my_random return float as
language java name 'java.lang.Math.random() return double';
/

select my_random from dual;

 MY_RANDOM
----------
.411402327

And in your query:

SELECT Z.* FROM ( 
  SELECT A.*, ROW_NUMBER() OVER (PARTITION BY A.STATE ORDER BY my_random) AS ROW_ID 
  FROM   STATE_TABLE A
) Z WHERE Z.ROW_ID=1;

Not saying it's ideal but it's possible.

answered Jun 2, 2015 at 23:00

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Comments

Stefan Yordanov · Accepted Answer · 2015-06-02 20:15:43Z

0

You can pseudo generate random numbers with

select to_char(systimestamp,'ff') from dual;

answered Jun 2, 2015 at 20:15

Stefan Yordanov

6663 silver badges10 bronze badges

2 Comments

calgrad Over a year ago

Hmm thanks! I tried this in my code, but if I run the code again and again, it just gives me the same rows: SELECT Z.* FROM ( SELECT A.*, ROW_NUMBER() OVER (PARTITION BY A.STATE ORDER BY (select to_char(systimestamp,'ff') from dual)) AS ROW_ID FROM STATE_TABLE A ) Z WHERE Z.ROW_ID=1;

Justin Cave Over a year ago

@calgrad - You'd have to ensure that the systimestamp changes between calls to get a different number of fractional seconds. That's If you put in in a query, Oracle is only going to call systimestamp once so you'd get the same "random number" for every row. You could create a separate function that internally calls systimestamp and add a dbms_lock.sleep call to every execution to ensure that you don't just get a bunch of monotonically increasing values. That would be slow as heck though.

Collectives™ on Stack Overflow

Oracle Random Number Generator Stored Procedure without using DBMS_RANDOM

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related