Q

How to use a hierarchical query to generate ROWNUMs using PL/SQL

Want to generate numbers from SQL? Learn why using a PL/SQL hierarchical query may be a better solution than using a data dictionary view in this tip from PL/SQL expert Dan Clamage.

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. 

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 editor@searchoracle.com

This was first published in October 2010

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close