Random value between a range
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Random value between a range

  1. #1
    Join Date
    Apr 2001
    Posts
    45
    I would like to randomize the values of x and y in the following query:

    insert into tab1 values (chr(x)||chr(y)||);

    This would allow me to populate random values of strings in this table. I plan to use several of these random generators (concatenated) to populate several hundred thousand records.

    Is there a function in PL/SQL or SQL that would do this ? The Dbms_random does not have a range restriction (am I wrong?). I would need to restrict the range of the x and y to be 65-90 incl for the chr function to return me characters of the english-alphabet.

    thanks for your help.

  2. #2
    Join Date
    Sep 2000
    Posts
    47
    Try using DBMS_RANDOM.VALUE(x,y). Just checkout the exact syntax. This function returns a random number between x and y. Description of DBMS_RANDOM.VALUE is not available in oracle documentation.

    Good Luck.

    Pinakin

  3. #3
    Join Date
    Feb 2001
    Location
    Master Control
    Posts
    86
    i love it, nobody answers any of the responses to the random questions. the closest you get is 'use DBMS_RANDOM' or 'check out DBMS_RANDOM'. nobody actually says anything on how to use it. does anyone have a simple example?

    [Edited by tron on 05-25-2001 at 02:59 PM]

  4. #4
    Join Date
    Apr 2001
    Posts
    45

    Thumbs up

    Tron,

    I will cook something up and report back with an example (just for you...).



  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    It's true the DBMS_RANDOM package doesn't offer any range-base generator, its RANDOM function only return random *******s in the range of BINARY_INTEGER, which means between -2**31 .. 2**31. But it is very trivial to get only values from a specified range. For example, if you wan't to get random positive integers in the range between 100 and 199 you would use something like:

    100+ABS(MOD(dbms_random.random,100))

    HTH,
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    P.S.
    In case you have problems with general usage of DBMS_RANDOM package (seed, initialize), here is a basic example of it's usage:

    Connected to:
    Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production
    With the Partitioning and Java options
    PL/SQL Release 8.1.5.0.0 - Production

    SQL> SET SERVEROUTPUT ON
    SQL> BEGIN
    2 DBMS_RANDOM.INITIALIZE(87241510);
    3 FOR i IN 1..10 LOOP
    4 DBMS_OUTPUT.PUT_LINE(100+ABS(MOD(DBMS_RANDOM.RANDOM,100)));
    5 END LOOP;
    6 DBMS_RANDOM.TERMINATE;
    7 END;
    8 /

    177
    186
    107
    116
    146
    174
    162
    137
    158
    145

    PL/SQL procedure successfully completed.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  7. #7
    Join Date
    May 2001
    Location
    Dallas, US
    Posts
    78

    dbms_random package

    Dear jmodic,
    I am using Oracle 8.1.6 version...

    I tried out your example.... but it gives error as
    "" identifier 'DBMS_RANDOM.INITIALIZE' must be declared""
    .. so please let me know how to initilize this package..

    Thanx in advance..

    RP Kumar
    You Can Win, if u believe Yourself

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    There might be several reasons why you can't use the package DBMS_RANDOM. Check in the following order:

    1. Is package and package body SYS.DBMS_RANDOM installed in your system? AFAIK this package is not instaled by default.

    2. If it is installed, is it valid? It might be that some packages on which it depends are not installed or valid (like DBMS_CRYPTO_TOOLKIT).

    3. If both 1. and 2. are OK, check your synonyms and privileges on that package.


    If the package DBMS_RANDOM is not installed, you have to instal it under schema SYS. You have to run scrypt $ORA_HOME/rdbms/admin/dbmsrand.sql

    You might also need to install DBMS_CRYPTO_TOOLKIT (scrypts dbmsoctk.sql and prvtoctk.plb), UTL_RAW (utlraw.sql and prvtrawb.plb) and mybe some others to succesfully compile DBMS_RANDOM.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  9. #9
    Join Date
    May 2001
    Location
    Dallas, US
    Posts
    78

    Thanks...

    Hai Friend,
    Thanks for your valuable reply...

    RP Kumar
    You Can Win, if u believe Yourself

  10. #10
    Join Date
    Apr 2001
    Posts
    45
    Tron,

    I guess jmodic did a pretty good job. He/she is amazing.

    thanks jmodic and all those who contributed to this thread.

    mayse.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width