Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
   

Oracle test data & date generation with SQL

Oracle Tips by Indrajit Agasti
April 18, 2008

 
 
Also, see these notes on generating test data for Oracle, Generating random test data with dbms_random and Oracle test data generation tips.
 
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.
 
  1. 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');
 
 
 
  1. 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.
 
 
  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');
 
 
  1. 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)
 
 
 
  1. 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

 

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.


 

 

  
 

 
 
 
 
 

 
 
 
 
 
 
Oracle training Excel
 
Oracle performance tuning software 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright ? 1996 -  2014

All rights reserved by Burleson

Oracle ? is the registered trademark of Oracle Corporation.