How could you create six unique random numbers between 1 and 49
with one SQL statement?
We would generate the set of numbers to pick from (see the
innermost query that follows); any table with 49 or more records would do it. First
the quick-and-dirty solution without a pipelined function.
from (select r
from (select rownum
where rownum < 50)
where rownum <= 6;
That query works by generating the numbers 1 .. 49, using the
inline view. We wrap that innermost query as an inline view and sort it by a
random value, using DBMS_RANDOM.VALUE. We wrap that result set in yet another inline
view and just take the first six rows. If we run that query over and over, we'll get
a different set of six rows each time.
This sort of question comes up frequently—maybe not about
how to generate a set of six random numbers but rather, "how can we get N rows?" For
example, we'd like the inclusive set of all dates between 25-FEB-2004 and
10-MAR-2004. The question becomes how to do this without a "real" table, and the
answer lies in Oracle9i/10g with its PIPELINED function capability.
We can write a PL/SQL function that will operate like a
table. We need to start with a SQL collection type;
this describes what the PIPELINED function will return. In this case, we are choosing
a table of numbers; the virtual table we are creating will simply return the numbers
1, 2, 3, ... N:
create type array
as table of number
Next, we create the actual PIPELINED function. This function will
accept an input to limit the number of rows returned. If no input is provided, this
function will just keep generating rows for a very long time (so be careful and make
sure to use ROWNUM or some other limit in the query itself!). The PIPELINED
keyword on line 4 allows this function to work as if it were a table:
gen_numbers(n in number default null)
for i in 1 .. nvl(n,999999999)
Suppose we needed three rows for something. We can now do that in
one of two ways:
select * from TABLE(gen_numbers(3));
select * from TABLE(gen_numbers)
where rownum <= 3;
Now we are ready to re-answer the original question, using the
from (select * from table(gen_numbers(49)))
order by dbms_random.random
where rownum <= 6
We can use this virtual table functionality for many things, such
as generating that range of dates:
Note the name of the column we used: COLUMN_VALUE. That is the
default name for the column coming back from the PIPELINED function.
This are the typical steps to perform when using PL/SQL Table
The producer function must use the PIPELINED keyword in its
The producer function must use an OUT parameter that is a
record, corresponding to a row in the result set.
Once each output record is completed, it is sent to the consumer
function through the use of the PIPE ROW keyword.
The producer function must end with a RETURN statement that does
not specify any return value.
The consumer function or SQL statement then must use the TABLE
keyword to treat the resulting rows from the PIPELINE function like a regular
The first step is to define the format of the rows that are
going to be returned. In this case here, we're going to return a INT, DATE followed
by a VARCHAR2(25).
CREATE OR REPLACE TYPE myObjectFormat
Next a collection type for the type previously defined must
CREATE OR REPLACE TYPE myTableType
AS TABLE OF
Finally, the producer function is packaged in a package. It
is a pipelined function as indicated by the keyword pipelined.
CREATE OR REPLACE PACKAGE myDemoPack
CREATE OR REPLACE PACKAGE BODY myDemoPack AS
FUNCTION prodFunc RETURN myTableType PIPELINED IS
FOR i in 1 .. 5
PIPE ROW (myObjectFormat(i,SYSDATE+i,'Row '||i));
ALTER SESSION SET NLS_DATE_FORMAT='dd.mm.yyyy';
SELECT * FROM TABLE(myDemoPack.prodFunc());
---------- ---------- ---------
1 31.05.2004 Row 1
2 01.06.2004 Row 2
3 02.06.2004 Row 3
4 03.06.2004 Row 4
5 04.06.2004 Row 5