How to get Numbers in number range by PL-Sql .?

Question

here is my serial table.it has more than 1000 records.its with start number and end number.but between numbers not exist. i need to add all number [start/between & end numbers] records in another temp table number by number like below

EXIST TABLE

select concat(CARD_BULK_CODE,start_serial) startserial,concat(CARD_BULK_CODE,end_serial) endserial
from TSR_BULK_CARD_SERIALS

---------------------------
STARTSERIAL ENDSERIAL |
---------------------------
18126944    18126946  |
18141101    18141122  |
15150722    15150729  |
19069303    19069317  |
---------------------------

REQUIRED TABLE

-----------
SERIAL_NO | 
-----------
18126944    
18126945    
18141101    
18141102    
....
-----------

seem its need pl-sql to implement this. please help me to sort out this issue

I tried with below query with the help of dual. but its very slow and not yet got results :-) running more than 1 Hour

select distinct concat(t.CARD_BULK_CODE,t.START_SERIAL)+level-1 SERIAL
from TSR_BULK_CARD_SERIALS t, dual
connect by level-1<=(concat(t.CARD_BULK_CODE,t.END_SERIAL ))-concat(t.CARD_BULK_CODE,t.START_SERIAL) 
order by 1

EDIT :

Dear Alen & Dba.i tried with your ones and below error occured.

DECLARE
     l_st NUMBER;
     l_en NUMBER;
BEGIN
     FOR rec IN (select concat(card_bulk_code, start_serial) startserial,concat(card_bulk_code, end_serial) endserial from tsr_bulk_card_serials)
     LOOP
          l_st := rec.startserial;
          l_en := rec.endserial;
          FOR rec1 IN l_st..l_en
          LOOP
               INSERT INTO temp(serial_no) values(rec1);
          END LOOP;
     END LOOP;
     COMMIT;
END;
Error at line 1
ORA-01426: numeric overflow
ORA-06512: at line 9

Script Terminated on line 1.

possible duplicate of [Oracle get numbers with range](http://stackoverflow.com/questions/20045045/oracle-get-numbers-with-range) — Nick Krasnov, Nov 21 '13 at 09:58
post the structure of the table temp to resolve number overflow error — psaraj12, Nov 21 '13 at 10:45
dear friend Nicholas, I got dual sql from that post.but its not matched for my requirement. — Priyan RockZ, Nov 21 '13 at 10:57

score 4 · Answer 1 · answered Nov 21 '13 at 10:06

One way to do it without resorting to plsql

WITH ranges AS
(
  SELECT CONCAT(CARD_BULK_CODE, start_serial) startserial,
         CONCAT(CARD_BULK_CODE, end_serial) endserial
    FROM TSR_BULK_CARD_SERIALS
),
numbers(n) AS (
  SELECT 0 n
    FROM dual
UNION ALL
  SELECT n + 1
    FROM numbers
   WHERE n <= 
  (
    SELECT MAX(endserial - startserial)
      FROM ranges
  )
)
SELECT t.startserial + n.n SERIAL_NO
  FROM ranges t JOIN numbers n
    ON n.n <= t.endserial - t.startserial
 ORDER BY SERIAL_NO

Here is SQLFiddle demo

thanks dear peterm.I was tried once with dual.but its get more time to executing.thatswhy i ask for plsql :-) — Priyan RockZ, Nov 21 '13 at 10:53

Dba · Answer 2 · 2013-11-21T09:57:15.073

1

Try like this,

WITH t(ST, EN) AS
(
     SELECT 18126944,    18126946 FROM dual
     UNION
     SELECT 18141101,    18141122 FROM dual
     UNION
     SELECT 15150722,    15150729 FROM dual
     UNION
     SELECT 19069303 ,   19069317 FROM dual
)
SELECT DISTINCT st + LEVEL -1
FROM   t
CONNECT BY LEVEL <= (SELECT en - st + 1 FROM DUAL)
ORDER BY 1;

/

Try something like this for PL/SQL,

DECLARE
     l_st NUMBER;
     l_en NUMBER;
BEGIN
     FOR rec IN (SELECT * FROM t)
     LOOP
          l_st := rec.st;
          l_en := rec.en;
          FOR rec1 IN l_st..l_en
          LOOP
               INSERT INTO <your_tab>;
          END LOOP;
     END LOOP;
     COMMIT;
END;

edited Nov 21 '13 at 09:57

answered Nov 21 '13 at 09:29

Dba

6,511
1
24
33

dear Dba, what about pl-sql for used here.? because more number of rows in exist table – Priyan RockZ Nov 21 '13 at 09:31
3

This produces an enormous number of rows and will be hella slow. – Przemyslaw Kruglej Nov 21 '13 at 09:32
2

Just write some PL/SQL - iterate through your table and insert rows in the temp table. – Alen Oblak Nov 21 '13 at 09:34
yes dear Przemyslaw.thatswhy i suppose to insert in temp table. – Priyan RockZ Nov 21 '13 at 09:41
dear Alen.please help me to write it.actually i'm beginner level with pl-sql – Priyan RockZ Nov 21 '13 at 09:42
1

@PrzemyslawKruglej, You are right, It'll be terribly slow if we go with sql. Thanks for pointing out. – Dba Nov 21 '13 at 09:43

Alen Oblak · Accepted Answer · 2013-11-21T12:02:22.520

1

Just write some PL/SQL - iterate through your table and insert rows in the temp table.

declare
   l_start number;
   l_end number;
begin
   for r_rec in (select to_number(concat(card_bulk_code, start_serial)) startserial
                 ,      to_number(concat(card_bulk_code, end_serial)) endserial
                 from   tsr_bulk_card_serials )
   loop
      l_start := r_rec.startserial;
      l_end := r_rec.endserial;
      for l_i in l_start..l_end loop
         insert into your_temp_table;
      end loop;
   end loop;
end;

edited Nov 21 '13 at 12:02

answered Nov 21 '13 at 09:37

Alen Oblak

3,285
13
27

Just add to_number( ... ) so the data type is numeric. Concat function in your example probably returns varchar2 type. Updated answer. – Alen Oblak Nov 21 '13 at 12:03
1

How big are your numbers? What is the max startserial and endserial? – Alen Oblak Nov 21 '13 at 12:05
like this '121100246016920' – Priyan RockZ Nov 21 '13 at 12:12
1

OK, then just add to_number() and it should be OK. – Alen Oblak Nov 21 '13 at 12:17

score 0 · Answer 4 · answered Nov 21 '13 at 12:50

DECLARE
   l_st      NUMBER (20);
   l_en      NUMBER (20);
   testnum   NUMBER (4);
BEGIN
   FOR rec IN (SELECT CONCAT (card_bulk_code, start_serial) startserial,CONCAT (card_bulk_code, end_serial) endserial FROM tsr_bulk_card_serials)
   LOOP
      l_st := TO_NUMBER (rec.startserial);
      l_en := TO_NUMBER (rec.endserial);
      testnum := l_en - l_st;
      DBMS_OUTPUT.put_line (l_st);
      DBMS_OUTPUT.put_line (l_en);

      IF l_st < l_en
      THEN
         FOR rec1 IN 0 .. testnum
         LOOP
            l_st := l_st + 1;

            INSERT INTO temp(serial_no) VALUES (l_st);
         END LOOP;
      END IF;
   END LOOP;
   COMMIT;
END;

above code helped me to sorted my issue thanks all :-)

How to get Numbers in number range by PL-Sql .?

4 Answers4