Hi, Tom.
I need to create a bulk of serial numbers in one of my tables and for that I want to create a stored procedure. I want to pass first SN and last SN and the procedure to return all the SN in between these two.
How can I do that without using loops?
For example I pass to the procedure '300RASEL0000000671000001' and '300RASEL0000000671000010' and should get the following:
'300RASEL0000000671000001'
'300RASEL0000000671000002'
'300RASEL0000000671000003'
...
'300RASEL0000000671000010'
Thank you in advance
You don't need a stored procedure :)
A simple select will do it
ops$tkyte%ORA11GR2> variable s varchar2(30)
ops$tkyte%ORA11GR2> variable e varchar2(30)
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> exec :s := '300RASEL0000000671000001'
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> exec :e := '300RASEL0000000671000010'
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select substr( :s, 1, 18 ) || to_char( to_number( substr( :s, 19 ) )+level-1, 'fm000000' )
2 from dual
3 connect by level <= to_number( substr( :e, 19 ) )-to_number( substr( :s, 19 ) )+1
4 /
SUBSTR(:S,1,18)||TO_CHAR(
-------------------------
300RASEL0000000671000001
300RASEL0000000671000002
300RASEL0000000671000003
300RASEL0000000671000004
300RASEL0000000671000005
300RASEL0000000671000006
300RASEL0000000671000007
300RASEL0000000671000008
300RASEL0000000671000009
300RASEL0000000671000010
10 rows selected.
or using the "newer" recursive with clause:
ops$tkyte%ORA11GR2> with data (sn)
2 as
3 ( select to_number( substr( :s, 19 ) ) sn from dual
4 union all
5 select sn+1 from data where sn <= to_number( substr( :e, 19 ) )-to_number( substr( :s, 19 ) )
6 )
7 select substr( :s, 1, 18 ) || to_char( sn, 'fm000000' )
8 from data
9 /
SUBSTR(:S,1,18)||TO_CHAR(
-------------------------
300RASEL0000000671000001
300RASEL0000000671000002
300RASEL0000000671000003
300RASEL0000000671000004
300RASEL0000000671000005
300RASEL0000000671000006
300RASEL0000000671000007
300RASEL0000000671000008
300RASEL0000000671000009
300RASEL0000000671000010
10 rows selected.