SQL Script Random Number Generator

Updated: 28-Feb-2003
Version: 1.0

Description

This script contains 3 useful functions for generating random numbers, string and dates. 

These are useful to generate test data for performance benchmarking, or for generating random passwords. 

SQL> l
  1* select rand_string(6) from dual
SQL> /

RAND_STRING(6)
--------------
TYTDUW

Parameters

rand_string - Length Of String
rand_number - Length Of Number
rand_date - The maximum number fo days in past/future to select date from.
Use a negative for future dates.

SQL Source

REM Copyright (C) Think Forward.com 1998- 2005. All rights reserved. 


create or replace function gen_alph_num_rand return varchar2
is 

rno varchar2(3);

begin 

rno := to_char(round(dbms_random.value(0, 35))) ;

If rno <=9 then 
    rno := rno; 
else 
    rno := chr(to_number(rno)+55); 
end if; 

RETURN rno; 

end; 
/  
show errors

create or replace function rand_string (p_length IN NUMBER)
return varchar2 
is 

l_str varchar2(256) default null;

begin

for i IN 1..p_length loop
   l_str := l_str || gen_alph_num_rand ;
end loop;

RETURN l_str;

end;
/
show errors

create or replace function rand_number (p_length IN NUMBER)
return number
is

l_num NUMBER default null;

begin

l_num := POWER(10,p_length) * dbms_random.value (0,0.999999999) ;

RETURN TRUNC(l_num);

end;
/
show errors

create or replace function rand_date (p_max_days_in_past IN NUMBER)
return date
is

l_date DATE default sysdate;

begin

l_date := sysdate - dbms_random.value (0,p_max_days_in_past) ;

RETURN l_date;

end;
/
show errors

Return to Index of SQL Scripts

Site Map

General Information : Home | Company Profile | Contact Us | Legal
Database Support : Proactive Support | Pay Per Incident Support
Expert Services : High Availability | Creative Solutions | Training
Build Services : Oracle Installations | Revive your Oracle Setup | Low Cost Development
Free Oracle Resources: DBA Scripts | Oracle Links