Wednesday, 1 July 2015

Row Generators in Oracle

SQL : select  level from dual connect by level <= 100 ;

this generates 100 rows of numbers 1..100. But row generators aren't limited to numbers. 
By using date arithmetic, you can generate dates:
select  date '2015-01-01' + level -1 as gendate  from    dual 
connect by date '2015-01-01' + level -1 < date '2015-02-01' ; 
01-01-2015 
02-01-2015 
.
.
.
30-01-2015
31-01-2015

31 rows selected.

Or by using ASCII codes, you can generate characters:
select chr(65 + level - 1) as letter  from dual  connect by level <= 26; 
 LETT 
 ---- 
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
T
U
V
W
X
Y
Z
26 rows selected.  

No comments:

Post a Comment