O SpecGold OracleBusIntApps7 clr



   Call us now 

  Manchester Office

  +44 (0) 8450 940 998




John's Blog

This is my blog of various topics, from things that I think might be useful to others, to things that I just find interesting personally. If you have any comments or questions on any of my posts then please do ask - any positive contribution is very much welcomed.

The views expressed in this blog and completely my own and do not reflect those of Beyond Systems Ltd. All content is provided for informational purposes only, and you are solely responsible for how you use this on your own or others systems. We accept no liability for any losses or damages caused.

  • Home
    Home This is where you can find all the blog posts throughout the site.
  • Categories
    Categories Displays a list of categories from this blog.
  • Tags
    Tags Displays a list of tags that have been used in the blog.
  • Bloggers
    Bloggers Search for your favorite blogger from this site.
  • Team Blogs
    Team Blogs Find your favorite team blogs here.
  • Login
    Login Login form

Generating Rows Automatically in Oracle

  • Font size: Larger Smaller
  • Subscribe to this entry
  • Print

Generating Rows Automatically in Oracle

Quite often there is a need to generate rows in SQL. Whether it be to generate a row for every date between X and Y (the most common I personally find), or simply just to generate some dummy data for testing purposes. Or even just as an academic exercise it is good practice to explore the options for achieving something which may seem very simple. Traditionally I have seen people do something like this:

SELECT 'stuff'
FROM user_objects
WHERE rownum < :n;

Which of course is perfectly valid. If you can guarantee that user_objects contains enough rows for your :n limit, or if you prefer to use all_ or dba_ then the user is able to select from them. If not, you tend to find cartesian joins and such going on, or sometimes pl/sql loops (which, depending on how they are being used, can be a big performance hit).
So, are there any other alternative (future-proof, more reliable, faster...) techniques we can use? Of course there are! As with anything there is more than one way to solve a particular problem, but here are a few of the most common techniques.

Connect By

My personal favourite, simply for its ease of use and implementation, plus speed of execution (fast-dual). This relies on exploiting the connect by functionality by not supplying any prior clause. You're effectively connecting every row to itself. It's worth mentioning here that officially this technique isn't documented, and is simply taking advantage of a side-effect. However it is so commonly used I can't see it ever being deprecated or removed (then again, this is Oracle we're talking about!).

SELECT 'stuff'
FROM dual

Where I tend to personally use this is to generate calendars. Let's say I want a row for every day in the current year.

FROM dual

The addition and subtraction of 1 is because Level starts at 1.

Recursive Query

This technique is more valid than the one described above and so will possibly be preferred by the purists. It is however slightly more complex. It utilizes a recursive query with an exit strategy when your desired number of rows is reached.

WITH i(n) AS (
SELECT n+1 n FROM i WHERE n < :n)

Model Clause

Possibly a contender for the most obscure of the bunch, simply because the Model clause in SQL is not very well understood by most. I have to admit I'm not an expert in it by any means! It is however extremely powerful and a simple statement can produce our row generator.

SELECT 'stuff'
FROM dual
dimension by (0 as i)
measures (0 j)
rules iterate(100)

Pipelined Function

Another technique that is often overlooked is the pipelined function (and again a contender for the most obscure). It carried some overhead, however it is very reusable and flexible. We first have to define an additional type object to support it, however once done, the results can be surprisingly pleasing and very quick to develop with!

create type gen_num is table of number;

create function n_rows(n in number) return gen_num pipelined
  for i in 1 .. n 
    pipe row (i);
  end loop;

select * from table(n_rows(:n));


Each of the techniques above has its own pro's and con's and there is no right way. I am yet to compare real-world performance between the different methods, however one must be cautious that simply generating rows is only half the problem. The key is how we can then join to those rows within another query, and how that query as a whole performs.
Please feel free to post any comments or questions.

Last modified on
Tagged in: development Oracle sql tips
in Technical Hits: 2999 0 Comments

My primary area of interest and expertise is Oracle E-Business Suite, particularly Foundation, Human Resources, Payroll, Time and Labor, Warehouse Management and Inventory. Whilst I prefer to focus on functional aspects I do have a keen interest in technical areas too such as SQL tuning/the optimizer, Application Express (APEX), Unix (particularly Linux) and general development.

  ACE Logo

Cert Logo  
Cert Logo 2


  • No comments made yet. Be the first to submit a comment

Leave your comment

Guest Tuesday, 20 March 2018