0

Hi,

Im working on a stored procedure to get monthly reports from a table. For that i need to develop a logic to perform the following function:
suppose i give a start_date and end_date, i need to get the months split up between these dates.
for eg) if I give start_date = 21-JAN-2009 and end_date = 15-APR-2009 i need to get the split up in the following format.

TEMP_START_DATE TEMP_END_DATE
---------------------------------
21-JAN-2009 31-JAN-2009
01-FEB-2009 28-FEB-2009
01-MAR-2009 31-MAR-2009
01-APR-2009 15-APR-2009

Finally, when this output is generated, it should be inserted into a cursor or some form of temporary data set, so that I can retrieve the values later on.
Any help regarding this would be highly appreciated.

4
Contributors
6
Replies
7
Views
7 Years
Discussion Span
Last Post by babyDBA
0

You can create global temporary tables in oracle and use them in your procedure.

Creation Of Temporary Tables
The data in a temporary table is private for the session that created it and can be session-specific or transaction-specific. If the data is to deleted at the end of the transaction the table should be defined as follows: 
CREATE GLOBAL TEMPORARY TABLE my_temp_table (
  column1  NUMBER,
  column2  NUMBER
) ON COMMIT DELETE ROWS;
If on the other hand that data should be preserved until the session ends it should be defined as follows: 
CREATE GLOBAL TEMPORARY TABLE my_temp_table (
  column1  NUMBER,
  column2  NUMBER
) ON COMMIT PRESERVE ROWS;
0

I need the logic for a stored procedure. The input to the procedure would be two dates- start date(something like 22-JUN-2009) and end date(something like 12-SEP-2009). This stored procedure should be able to split this date interval into months(not the number of months, but the actual month).to be specific, the output should have two columns/fields like shown below:
22-JUN-2009 30-JUN-2009
01-JUL-2009 31-JUL-2009
01-AUG-2009 31-AUG-2009
01-SEP-2009 12-SEP-2009

This output should be inserted into a cursor.
This is my requirement.

0

I think this script you will be sufficient

create table test_result
(
    date1 date,
    date2 date
);

create or replace procedure split_months(dDate1 date, dDate2 date) is
tempDate date;
begin

    tempDate := dDate1;
    loop
        if last_day(tempDate)<dDate2
        then
            insert into test_result(date1, date2)
            values(tempDate, last_day(tempDate));
            tempDate := last_day(tempDate)+1;
        else
            insert into test_result(date1, date2)
            values(tempDate, dDate2);
            exit;
        end if; 
    end loop;

end split_months;

declare
dDate1 date := to_date('22-JUN-2009','dd-mon-yyyy');
dDate2 date := to_date('12-SEP-2009','dd-mon-yyyy');
begin
    delete test_result;
    split_months(dDate1, dDate2);
    commit;
end;

select date1, date2 from test_result;
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.