Oracle get numbers with range

Question

Here is my serial table:

+-----------------+-----------------+
|  START_SERIAL   |   END_SERIAL    |
+-----------------+-----------------+
| 120500216057150 | 120500216057155 |
| 120500216057157 | 120500216057157 |
| 120500216057161 | 120500216057164 |
+-----------------+-----------------+

Need to get result like this:

120500216057150 
120500216057151 
120500216057152 
120500216057153 
120500216057154 
120500216057155
120500216057157
120500216057161....

Please help me to sort out this issue. [seems need pl-sql with for loop needed.?]

EDIT dear TechDo, What about pl/sql usage here as follows.but its gives me a error :-)

DECLARE
  start_serial  NUMBER;
  end_serial  NUMBER;
  records NUMBER;
BEGIN
  select start_serial INTO start_serial
    from sales_details ;
  select (start_serial+no_of_cards)as end_serial into end_serial
    from sales_details;
  select count(*) as records into records 
    from sales_details;

  FOR i IN 1..records  LOOP
    INSERT INTO temp (serial_no)
    VALUES(i);
  END LOOP;
END;
/

@Actually you need a for loop approach for this problem? – TechDo Nov 18 '13 at 10:46 — TechDo, Nov 18 '13 at 10:46
how is the answer whn pl-sql added.? – Priyan RockZ Nov 20 '13 at 04:33 — Priyan RockZ, Nov 20 '13 at 04:33

score 5 · Accepted Answer · edited Nov 21 '13 at 10:43

5

Please try:

select 
  distinct START_SERIAL+level-1 SERIAL
from 
  YourTable
connect by level-1<=END_SERIAL-START_SERIAL
order by 1;

edited Nov 21 '13 at 10:43

SQB

3,926
2
28
49

answered Nov 18 '13 at 10:19

TechDo

18,398
3
51
64

many thanks dear TechDo now its sorted.wt abt pl-sql usage for that question.? – Priyan RockZ Nov 18 '13 at 10:25
1

+1 for the simplicity. @TechDo Just a curiosity, why `dual` table? – Jorge Campos Nov 18 '13 at 10:29
1

Please check http://www.orafaq.com/wiki/Oracle_Row_Generator_Techniques, http://stackoverflow.com/questions/13540661/why-does-connect-by-level-on-a-table-return-extra-rows – TechDo Nov 18 '13 at 10:32
1

There is absolutely no need to cross join `YourTable` with `dual`. Why are you doing this? By doing this you are just adding one `nested loop` to the execution plan and increasing, however insignificant it can be, the overall cost of a query. – Nick Krasnov Nov 18 '13 at 14:23
@NicholasKrasnov Please add the solution. It will be helpful for all. – TechDo Nov 19 '13 at 04:09

Oracle get numbers with range

1 Answers1

Linked