Oracle Generator Series

Oracle lets generate sequence of number or dates. For eg: if you wanted to show first 10 days of April in a particular format, you can get that via dual table in Oracle. Same is the case for generating integers…

1) Date Sequence

SELECT to_Date('01-APR-2011','DD-MON-YYYY') + rownum - 1 date_sequence
FROM dual
CONNECT BY level <= to_Date('10-APR-2011','DD-MON-YYYY') - to_Date('01-APR-2011','DD-MON-YYYY') + 1
;
 

Output:

DATE_SEQUENCE
01-APR-11
02-APR-11
03-APR-11
04-APR-11
05-APR-11
06-APR-11
07-APR-11
08-APR-11
09-APR-11
10-APR-11

P.S. Use to_char function to get date in other formats

2) Integer Sequence

SELECT ROWNUM int_sequence
FROM dual
CONNECT BY level <=10
;

Output:

INT_SEQUENCE
1
2
3
4
5
6
7
8
9
10

Thanks,
Your’s Truly

Advertisements

About Badal Chowdhary

I am a Software Engineer by profession. I have done SCJP and SCWCD certifications. Like working on cutting edge technologies and frameworks. Driven by challenges and fascinated by technology. I love playing and watching sports: Cricket, Ping Pong, Tennis, Badminton, Racket Ball and Gym.
This entry was posted in Oracle and tagged , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s