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.

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;

sorry, the previous two posts didnt help :(

please be a little more specific

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.

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;
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.