How can I generate numbers from SQL? I’ve seen people using DBA_OBJECTS as a “big” table to generate ROWNUMs from. But this doesn’t seem like an appropriate use of the data dictionary view.
Requires Free Membership to View
I agree with your assertion that using a data dictionary view just for generating numbers is inappropriate, especially when you need more values than objects in your system. One of the simplest methods I’ve come across for generating values uses a hierarchical query:
select rownum
from dual
connect by rownum <=10;
ROWNUM
----------
1
2
3
4
5
6
7
8
9
10
10 rows selected.
Then it’s easy to add it to your query via an inline view:
-- generate lots of fake data
SELECT 'CUSTOMER_' || row_num customer_name
FROM (SELECT ROWNUM row_num
FROM DUAL
CONNECT BY ROWNUM <= 10);
CUSTOMER_NAME
-----------------
CUSTOMER_1
CUSTOMER_2
CUSTOMER_3
CUSTOMER_4
CUSTOMER_5
CUSTOMER_6
CUSTOMER_7
CUSTOMER_8
CUSTOMER_9
CUSTOMER_10
10 rows selected.
Notice I renamed the pseudocolumn ROWNUM in the inline view, so it wouldn’t be confused for the ROWNUM the outer query would produce. If something is worth using over and over, it’s worth encapsulating in a stored function, such as this:
-- create types to support pipelined function
CREATE OR REPLACE TYPE num_rec IS OBJECT
(num INTEGER);
/
CREATE OR REPLACE TYPE num_tab IS TABLE OF num_rec;
/
-- generate sequential numbers
-- given a starting value and total count
CREATE OR REPLACE FUNCTION numgen(
p_start IN INTEGER
,p_count IN INTEGER
)
RETURN num_tab
PIPELINED
IS
BEGIN
FOR rec IN (SELECT num_rec(ROWNUM + p_start - 1) r
FROM DUAL
CONNECT BY ROWNUM <= p_count) LOOP
PIPE ROW (rec.r);
END LOOP;
RETURN;
END numgen;
/
-- generate data
SELECT 'CUSTOMER_' || num customer_name
FROM TABLE(numgen(10, 10));
CUSTOMER_NAME
-----------------
CUSTOMER_10
CUSTOMER_11
CUSTOMER_12
CUSTOMER_13
CUSTOMER_14
CUSTOMER_15
CUSTOMER_16
CUSTOMER_17
CUSTOMER_18
CUSTOMER_19
10 rows selected.
Pipelined functions have the capacity for returning large data sets asynchronously. You might wonder why not just use a FOR loop inside the function?
-- alternate method using FOR loop
CREATE OR REPLACE FUNCTION numgen2(
p_start IN INTEGER
,p_count IN INTEGER
)
RETURN num_tab
PIPELINED
IS
BEGIN
FOR i IN p_start .. p_start + p_count - 1 LOOP
PIPE ROW (num_rec(i));
END LOOP;
RETURN;
END numgen2;
/
SELECT 'CUSTOMER_' || num customer_name
FROM TABLE(numgen2(10, 10));
CUSTOMER_NAME
-----------------
CUSTOMER_10
CUSTOMER_11
CUSTOMER_12
CUSTOMER_13
CUSTOMER_14
CUSTOMER_15
CUSTOMER_16
CUSTOMER_17
CUSTOMER_18
CUSTOMER_19
10 rows selected.
You certainly get the same results. Is one method faster than the other? Let’s try it over a million values and see what happens:
SQL> set timing on SQL> BEGIN 2 FOR rec IN (SELECT num 3 FROM TABLE(numgen(1, 1000000))) LOOP 4 NULL; 5 END LOOP; 6 END; 7 / PL/SQL procedure successfully completed. Elapsed: 00:00:05.32 SQL> SQL> BEGIN 2 FOR rec IN (SELECT num 3 FROM TABLE(numgen2(1, 1000000))) LOOP 4 NULL; 5 END LOOP; 6 END; 7 / PL/SQL procedure successfully completed. Elapsed: 00:00:01.11 -- run twice to be sure SQL> BEGIN 2 FOR rec IN (SELECT num 3 FROM TABLE(numgen(1, 1000000))) LOOP 4 NULL; 5 END LOOP; 6 END; 7 / PL/SQL procedure successfully completed. Elapsed: 00:00:05.32 SQL> SQL> BEGIN 2 FOR rec IN (SELECT num 3 FROM TABLE(numgen2(1, 1000000))) LOOP 4 NULL; 5 END LOOP; 6 END; 7 / PL/SQL procedure successfully completed. Elapsed: 00:00:01.09
The purely PL/SQL solution certainly does appear to be much faster.
Have a question for Dan Clamage? Send an e-mail to [email protected]
This was first published in October 2010
There are Comments. Add yours.