Re: random records

From: bernardvn <bernard_at_bosvark.com>
Date: 13 Jul 2006 02:52:10 -0700
Message-ID: <1152784330.765069.75930@i42g2000cwa.googlegroups.com>

Not as elegant as the SAMPLE solution, but I used the following two functions. First is to get random number between two limits, and the second selects the records that is to be chosen from in bulk into a netsted table and then I select one of the results using the random number function.

---

  function random_number(p_max      number,
                         p_min      number default 0,
                         p_multiply number default 1) return number is

    x int;
  begin
    x := (mod((abs(dbms_random.random)), p_max) * p_multiply) + p_min;     return x;

  end random_number;

---

  function random_sql_result(p_sql varchar2) return varchar2 is

    type t_temp_string is table of varchar2(1000) index by binary_integer;

    nt_temp_string t_temp_string; -- Our temp string to keep the results from the SQL

  begin

• Execute the SQL into a Netested Table execute immediate p_sql bulk collect into nt_temp_string;
• And get any of the random numbers; return nt_temp_string(random_number(nt_temp_string.last, nt_temp_string.first)); exception when others then debug.f('RANDOM_SQL_RESULT: sqlerrm = %s ' || chr(13) || ' sql = %s', sqlerrm, p_sql); raise_application_error(-20001, sqlerrm || chr(13) || 'Can not execute following SQL : ' || p_sql);

  end random_sql_result;

---

but the SAMPLE seems much better.

Hope it helps

Regards
B

##############################

Faby wrote:
> Hi, I have to select 50 random records from a table. How should i
> proceed? Should I use "sample", "dbms_random.value"...?
> Thanks
Received on Thu Jul 13 2006 - 04:52:10 CDT