CODEWITHSUNDEEP
sqloracle-databaseplsqloracle11goracle-sqldeveloper
Justin
Justin

Reputation: 543

PLSQL generate random integer

In Oracle Sql developer 11g, how do I generate a random integer and assign it to a variable? This is what I've tried so far:

S_TB := SELECT dbms_random.value(1,10) num FROM dual;

With this code I got error: 

S_TB := SELECT dbms_random.value(1,10) num FROM dual
Error report -
Unknown Command

What is the proper way to solve my issue?

Upvotes: 15

Views: 91676

Answers (6)

TastySlowCooker
TastySlowCooker

Reputation: 145

For a set of consecutive integers randomly distributed uniformly (in the example below between 1 and 10), I suggest:

select round(dbms_random.value(0.5,10.49999999999),0) from dual

Otherwise I'll unintentionally restrict the first and last number in the set to half the probability of being chosen as the rest of the set.

As pointed out by q3kep and GolezTrol, dbms_random.value(x, y) provides a uniform random distribution for values greater than or equal to x and less than y. So either of the following would be appropriate:

select trunc(dbms_random.value(1,11)) from dual

or

select round(dbms_random.value(0.5, 10.5), 0) from dual

As per documentation

Upvotes: 11

Krishna
Krishna

Reputation: 8516

DECLARE
l_check Integer:=1;
BEGIN
    WHILE l_check < 5 LOOP
        l_check := DBMS_RANDOM.VALUE(1,10);
        DBMS_OUTPUT.PUT_LINE(l_check);
    END LOOP;
END;

-- DBMS_RANDOM.VALUE Gives the random value within the range.

Upvotes: 1

Monicalh
Monicalh

Reputation: 101

If you want to get a random number of n digits you can do this

CREATE OR REPLACE FUNCTION NUM_RANDOM(N IN NUMBER)
RETURN NUMBER 
AS 
BEGIN
    RETURN TRUNC (DBMS_RANDOM.VALUE(POWER(10, N - 1), POWER(10, N) - 1));
END NUM_RANDOM;

Upvotes: 5

ThePolyhedric
ThePolyhedric

Reputation: 21

Integers: 

select dbms_random.random from dual

Positive integers: 

select abs(dbms_random.random) from dual

Upvotes: 2

APC
APC

Reputation: 146229

Variables require PL/SQL; it's not clear from your question whether your code is a proper PL/SQL block. In PL/SQL variables are populated from queries using the INTO syntax rather than the assignment syntax you're using.

declare
    txt varchar2(128);
    n pls_integer;
begin
    --  this is how to assign a literal
    txt := 'your message here';

    --  how to assign the output from a query
    SELECT dbms_random.value(1,10) num 
    into n
    FROM dual;

end;

Although, you don't need to use the query syntax. This is valid, and better practice:

declare
    n pls_integer;
begin
    n := dbms_random.value(1,10);
end;

Upvotes: 29

mahi_0707
mahi_0707

Reputation: 1062

Alternatively, You can create a function for generating random numbers. This can be used in anywhere in the code.

    create or replace function RANDOM
    return number 
    is 
    a number ; 
    begin
    select round(dbms_random.value(1,10)) rnum
    into a 
    from dual;
    return a  ;
    end;
    /

OUTPUT:

Function created.

SQL> select Random from dual;

RANDOM

     6

SQL> select Random from dual;

RANDOM

     9

Upvotes: 5

Related Questions