| |
|
|
|
Oracle test data & date generation with SQL
Oracle Tips by Indrajit Agasti
April 18, 2008
|
By Indrajit Agasti
During the application
development, quite often, we feel the need to generate a series of dates
using SQL or PL/SQL, which can be used in the application.
One way to accomplish the same
is to create a table and store the dates.
But maintaining this table
with accurate data may require significant effort.
Alternatively, procedural
logic in PL/SQL can also be written to generate such series of dates.
In this article we will
demonstrate a few methods to generate the row-source without creating any
additional table or PL/SQL procedural code and create meaningful series of
dates which can be used in the application.
As of this writing, in Oracle,
there are at least three ways to generate a row-source without the need to
create any additional table using straight SQL.
Option 1:
SELECT 1
FROM DUAL
CONNECT BY LEVEL <=
<n>
Where <n> is the number of rows needed.
Option 2:
SELECT 1
FROM DUAL
GROUP BY CUBE (<n
comma separated numbers or characters>)
The above will generate 2^n (2 to the power n)
rows.
Note: In the below article we will use this
technique for all demonstration purpose.
Option 3:
SELECT dim
FROM dual
MODEL
DIMENSION BY (0 dim)
MEASURES (0 rnum)
RULES ITERATE (<n>) (
rnum [ITERATION_NUMBER] = ITERATION_NUMBER
);
Where <n> is the number of rows needed.
Note: The above clause is available from Oracle 10g
and above.
-
SQL to find all working days in current month:
First we will generate a
row-source of sequential numbers using a single SQL on the DUAL table of
Oracle. Then we will manipulate the row-source to generate meaningful dates
out of it.
We will use the CUBE function
of Oracle to generate the rows
From Oracle Documentation:
“CUBE takes a specified set of grouping columns and creates subtotals for
all of their possible combinations……. If n columns are specified for a CUBE,
there will be 2 to the n combinations of subtotals returned.”
In one month, there can be
maximum 31 days. The nearest integer > 31 which is a power of 2 is 32.
2^5 (2 to the power 5) is 32.
Hence, from the definition of
CUBE above, if we pass five digits to the CUBE function, we should obtain a
source of 32 rows.
SELECT 1
FROM Dual
GROUP BY CUBE (2, 2, 2, 2, 2);
Note: In
this case, just for the sake of clarity we have passed five 2’s.
You can
choose any
number or characters to the CUBE function to achieve the same result e.g.
SELECT 1 FROM Dual
GROUP BY CUBE (1, 2, 3, 4, 5);
We will wrap the above SQL in
an inline view and restrict the row-source to 31 rows using ROWNUM
pseudo-column.
SELECT ROWNUM FROM
(
SELECT 1
FROM Dual
GROUP BY CUBE (2, 2, 2, 2, 2)
)
WHERE ROWNUM <= 31;
Thus, the above SQL will give
us a sequential series of rows from 1 to 31.
Now, let us use the Date
functions to generate the dates.
TRUNC(<ANY DATE>,’MM’) will
give the 1st day of the month.
Hence if we add (ROWNUM-1)
value to each row, we will get the series of dates in the current month,
starting from the first date of the month.
But every month will not have
31 days. So, for the months having 30 days or 28 days or 29 days, the
sequential values added will exceed the last day of current month and will
generate rows for next month as well.
If we execute the following
query, we will see that the boundary values are exceeded and the dates for
the subsequent month are also getting included.
SELECT TRUNC(TO_DATE('20080415','yyyymmdd'),'MM')+ROWNUM
-1 DATES FROM
(
SELECT 1
FROM Dual
GROUP BY CUBE (2, 2, 2, 2, 2)
)
WHERE ROWNUM <= 31
We need to introduce a
mechanism so that the generated rows can be restricted to the current month
only. We will use the ADD_MONTHS built-in function of Oracle to achieve the
same.
From Oracle Documentation:
“ADD_MONTHS returns the
date “date”
plus “integer”
months. The date argument can be a datetime value or any value that can be
implicitly converted to DATE. The
“integer”
argument can be an integer or any value that can be implicitly converted to
an integer. The return type is always DATE, regardless of the datatype of
“date”.
If
“date”
is the last day of the month or if the resulting month has fewer days than
the day component of date, then the result is the last day of the resulting
month. Otherwise, the result has the same day component as “date”.”
With the above usage in our
mind, we can easily conclude that if we pass 1 as “integer” to the
ADD_MONTHS function, with the “date” as the First Day of the Month,
we will obtain the first day of the next month.
From here, the math is easy.
The difference between the first day of next month and first day of current
month will give us the exact number of days in the current month. Also note
that Oracle Date functions (and not exactly our logic) will now take care of
the number of days in a month. Even if the year is a leap year, Oracle will
determine the appropriate number of days.
The following SQL will clarify
the statement.
SELECT ADD_MONTHS(TRUNC(TO_DATE('20080415','YYYYMMDD'),'MM'),1)
FIRST_DAY_OF_NEXT_MONTH ,
TRUNC(TO_DATE('20080415','YYYYMMDD'),'MM')
FIRST_DAY_OF_CURRENT_MONTH ,
ADD_MONTHS(TRUNC(TO_DATE('20080415','YYYYMMDD'),'MM'),1)
- TRUNC(TO_DATE('20080415','YYYYMMDD'),'MM') NO_OF_DAYS_IN_CURRENT_MONTH
FROM DUAL;
We can try the above SQL with
any date, even February month of a leap year. The result will always be
accurate for any month.
Hence, in order to generate
all the dates for the month of April, the query will be as follows:-
SELECT TRUNC(TO_DATE('20080415','yyyymmdd'),'MM')+ROWNUM
-1 DATES FROM
(
SELECT 1
FROM Dual
GROUP BY CUBE (2, 2, 2, 2, 2)
)
WHERE ROWNUM <= ADD_MONTHS(TRUNC(TO_DATE('20080415','YYYYMMDD'),'MM'),1)
- TRUNC(TO_DATE('20080415','YYYYMMDD'),'MM'); –Here,
are restricting the number of rows to that of no of days in the month
From the above SQL, we now
have all the dates in the month.
We just need to exclude the
Saturday and Sunday to finally get the complete result-set of working days.
We will wrap-up the above
query in an inline view and then apply a WHERE clause to filter out the
Saturdays and Sundays.
The following will be the
final query to generate the Working Days.
SELECT * FROM
(
SELECT TRUNC(TO_DATE('20080415','yyyymmdd'),'MM')+ROWNUM
-1 DATES FROM
(
SELECT 1
FROM Dual
GROUP BY CUBE (2, 2, 2, 2, 2)
)
WHERE ROWNUM <= ADD_MONTHS(TRUNC(TO_DATE('20080415','YYYYMMDD'),'MM'),1)
- TRUNC(TO_DATE('20080415','YYYYMMDD'),'MM')
)
WHERE TO_CHAR( DATES, 'DY') NOT IN ('SAT','SUN');
-
SQL to find all working days in current year:
In this case the approach will
be same, but there will be a small change in the arithmetic.
A year consists of maximum 366
days. The nearest integer > 366 which is a power of 2 is 512.
2^9 (2 to the power 9) is 512.
Also, we need to modify t he
ADD_MONTHS and TRUNC functions to calculate the number of rows for a year.
In this case also, any special date handling (e.g. the leap year
consideration) will be taken care by Oracle.
SELECT * FROM
(
SELECT TRUNC(TO_DATE('20080415','yyyymmdd'),'Y')+ROWNUM
-1 DATES FROM
(
SELECT 1
FROM Dual
GROUP BY CUBE (2, 2,
2, 2, 2, 2, 2, 2, 2)
)
WHERE ROWNUM <=
ADD_MONTHS(TRUNC(TO_DATE('20080415','YYYYMMDD'),'Y'),12) - TRUNC(TO_DATE('20080415','YYYYMMDD'),'Y')
)
WHERE TO_CHAR( DATES, 'DY') NOT IN ('SAT','SUN');
Notice the sections in the
above query which is coloured blue. To fetch the working days for the
current year, that is the only change made to the Query discussed and
formulated in Section 1.
-
SQL to
determine the first and last working day of the month for a given date:
We will extend the concept of
the query of section 1. The following query will provide the first and last
working day of the month for a given date.
SELECT MIN(DATES) FIRST_WORKING_DAY , MAX(DATES)
LAST_WORKING_DAY FROM
(
SELECT TRUNC(TO_DATE('20080415','yyyymmdd'),'MM')+ROWNUM
-1 DATES FROM
(
SELECT 1
FROM Dual
GROUP BY CUBE (2, 2, 2, 2, 2)
)
WHERE ROWNUM <= ADD_MONTHS(TRUNC(TO_DATE('20080415','YYYYMMDD'),'MM'),1)
- TRUNC(TO_DATE('20080415','YYYYMMDD'),'MM')
)
WHERE TO_CHAR( DATES, 'DY') NOT IN ('SAT','SUN');
-
SQL to
determine the first and last working day of each month in the year for a
given date:
We will extend the concept of
the query in the section 2.
The following query will give
us the first and last working days in each month of the year.
SELECT MIN(DATES), MAX(DATES) FROM
(
SELECT TRUNC(TO_DATE('20080415','yyyymmdd'),'Y')+ROWNUM
-1 DATES FROM
(
SELECT 1
FROM Dual
GROUP BY CUBE (2, 2, 2, 2, 2, 2, 2, 2, 2)
)
WHERE ROWNUM <= ADD_MONTHS(TRUNC(TO_DATE('20080415','YYYYMMDD'),'Y'),12)
- TRUNC(TO_DATE('20080415','YYYYMMDD'),'Y')
)
WHERE TO_CHAR( DATES, 'DY') NOT IN ('SAT','SUN')
GROUP BY TRUNC(DATES,'MM')
ORDER BY MIN(DATES), MAX(DATES)
-
SQL to
generate a series of letters from A to Z:
The approach will be similar
to the examples shown above.
We need to generate the
numbers from 1 to 127 to represent all the standard ASCII characters.
After the row-source is
generated, we will convert them to the ASCII character using the Oracle
built in function called “ascii”.
Finally we will filter out the
rows for which only the alphabets are present.
The following SQL will
generate a series of letters from A to Z in upper and lower case:
SELECT CHR(rnum) Ascii_Character FROM
(
SELECT ROWNUM Rnum FROM
(
SELECT 1 FROM DUAL
GROUP BY CUBE(2,2,2,2,2,2,2)
)
WHERE ROWNUM <= 127
)
WHERE
(
Rnum BETWEEN ASCII('A') AND ASCII('Z')
OR
Rnum BETWEEN ASCII('a') AND ASCII('z')
)
to generate the series of characters from 'A' to 'Z' and 'a' to 'z'.
About the author:
|
Indrajit Agasti
|
|
Indrajit Agasti is working with Cognizant Technology Solutions Ltd,
United Kingdom, as a Project Lead and Oracle Developer. He has 6+ years
of experience of application development and has been developing various
Oracle based applications for the last 4 years.
|
E-Mail:
indrajit.agasti@yahoo.com
 |
If you like Oracle tuning, you
might enjoy my book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning tips and
scripts.
You can buy it direct from the publisher for 30%-off and get instant
access to the code depot of Oracle tuning scripts. |
 |
|
Need an Oracle Health Check?
- Do you have
bad performance after an upgrade?
- Need to
certify that your database follows best practices?
BC Oracle performance gurus can quickly
certify every aspect of your
Oracle database and provide a complete verification that your database
is fully optimized. |
|
|
