Thanks for the question, Bob.
Asked: August 22, 2001 - 10:33 pm UTC
Last updated: December 13, 2001 - 10:57 am UTC
Version: 8.1.7.0
Viewed 1000+ times
You Asked
How to create a PL/SQL sript to generate and insert into a table some sample dummy values?
and Tom said...
I might use the dbms_random package. You can call the plsql functions in it right from SQL.
Oracle8, release 8.0 introduced the DBMS_RANDOM package. In Oracle8i release 2 (version 8.1.6) new functionality was introduced for the DBMS_RANDOM package but it doesn?t seem to have made it into the documentation as yet. Fortunately for you ? one of the new functions is on that returns a random number between 0 and 1. The new functions in their entirety are:
FUNCTION value RETURN NUMBER;
FUNCTION value (low IN NUMBER, high IN NUMBER) RETURN NUMBER;
FUNCTION normal RETURN NUMBER;
FUNCTION string (opt char, len NUMBER) RETURN VARCHAR2;
The first version of VALUE returns a random number that is greater then or equal to 0 and less than 1. The second version of VALUE returns a random number that is greater then or equal to LOW and less then HIGH. An example of their usage would be:
SQL> select dbms_random.value, dbms_random.value(55,100)
2 from dual;
VALUE DBMS_RANDOM.VALUE(55,100)
---------- -------------------------
.782821936 79.6367038
The NORMAL function is designed to return a set of numbers with a normal distribution. This normal distribution has a standard deviation of 1 and is centered around the number zero. Therefore, we would expect about 68% of the values returned by this function to be between ?1 and +1, 95% to be between ?2 and +2, and 99% to be between ?3 and +3. And in fact that is what we observe:
SQL> create table t
2 as
3 select dbms_random.normal
4 from all_objects
5 /
Table created.
SQL> select count(*),
2 sum( decode( ceil(abs(normal)), 1, 1, 0 ) )/count(*) "+/- 1 stddev",
3 sum( decode( ceil(abs(normal)), 1, 1, 2, 1, 0 ) )/count(*) "+/- 2 stddev",
4 sum( decode( ceil(abs(normal)), 1, 1, 2, 1, 3, 1, 0 ) )/count(*) "+/- 3 stddev",
5 stddev(normal)
6 from t
7 /
COUNT(*) +/- 1 stddev +/- 2 stddev +/- 3 stddev STDDEV(NORMAL)
---------- ------------ ------------ ------------ --------------
21958 .679433464 .954458512 .997495218 1.00407451
Lastly, there is the STRING function. This function returns a random string of characters up to 60 characters in length. The OPT parameter is a single character that may be any of the following values:
Value Meaning Sample output
U Upper Case Letters DBMS_RANDOM.STRING('U',20)
---------------------------
LBLRTNOZBGDDEXRDAPGE
FHYUPCQKIKZNKJJBWNZN
L Lower Case Letters DBMS_RANDOM.STRING('L',20)
----------------------------
cupmmjtgcsxiblgzaibt
zwqzsmivudqqroaajygg
A Mixed Case DBMS_RANDOM.STRING('A',20)
Alpha-Numeric Chars ---------------------------
FOsgh_VtqIi`dcWPWUQt
AidjSWQFi[JYraUWBmeb
X Upper Case DBMS_RANDOM.STRING('X',20)
Alpha-Numeric Chars ----------------------------
O1L0F67FI=20?9;@I:NC
3E@PL51IC4R:?3SGRP?2
P Any ?printable? char DBMS_RANDOM.STRING('P',20)
----------------------------
jE+3hPw_v6|+K-T^3nd.
>/v/K&_E/gQOa{ep*BVb
To get more information about these functions and the DBMS_RANDOM package, you can issue:
select text
from all_source
where name = 'DBMS_RANDOM'
and type = 'PACKAGE' order by line;
In SQLPlus for documentation
Rating
(2 ratings)
Is this answer out of date? If it is, please let us know via a Comment