INFO-TECH

In oracle to get next or previous 12 months from specified month of year, below is a procedure which accept two parameter year and month name.

CREATE OR REPLACE PROCEDURE dbo.GET_MONTHS(
PERIOD_YEAR     IN  VARCHAR2,
PERIOD_MONTH    IN  VARCHAR2
)
AS
MONTH_STR1          VARCHAR2(2000);
MONTH_STR2          VARCHAR2(2000);
V_MONTH_DIGIT       NUMBER;
V_PERIOD_YEAR       NUMBER;
P_PERIOD_YEAR       NUMBER;

BEGIN

    SELECT TO_NUMBER(TO_CHAR(TO_DATE(PERIOD_MONTH,'MON'),'MM'),'99')
    INTO V_MONTH_DIGIT FROM DUAL;
    V_PERIOD_YEAR := TO_CHAR(TO_NUMBER(PERIOD_YEAR,'9999')+1,'9999');    
    P_PERIOD_YEAR := TO_CHAR(TO_NUMBER(PERIOD_YEAR,'9999')-1,'9999');
    FOR LP IN 0..11
    LOOP
         IF LP<V_MONTH_DIGIT-1 THEN
            MONTH_STR2 := MONTH_STR2 || TO_CHAR(ADD_MONTHS('1-JAN-'||PERIOD_YEAR,LP),'MON')||'-'||PERIOD_YEAR||',';
            MONTH_STR1 := MONTH_STR1 || TO_CHAR(ADD_MONTHS('1-JAN-'||PERIOD_YEAR,LP),'MON')||'-'||V_PERIOD_YEAR||',';             
        ELSE
            MONTH_STR2 := MONTH_STR2 || TO_CHAR(ADD_MONTHS('1-JAN-'||PERIOD_YEAR,LP),'MON')||'-'||P_PERIOD_YEAR||',';
            MONTH_STR1 := MONTH_STR1 || TO_CHAR(ADD_MONTHS('1-JAN-'||PERIOD_YEAR,LP),'MON')||'-'||PERIOD_YEAR||','; 
        END IF;
    END LOOP;
    
    MONTH_STR1 := substr(MONTH_STR1,0,length(MONTH_STR1)-1);
    MONTH_STR2 := substr(MONTH_STR2,0,length(MONTH_STR2)-1);
    
    DBMS_OUTPUT.PUT_LINE('Next 12 Months :'||MONTH_STR1);
    DBMS_OUTPUT.PUT_LINE('Previous 12 Months :'||MONTH_STR2);
       EXCEPTION 
        WHEN NO_DATA_FOUND THEN
            NULL;
        WHEN OTHERS THEN
            RAISE;

END GET_MONTHS; 

Parameters :
 
PERIOD_YEAR: Year from which we get next or previous year
PERIOD_MONTH: Month from which we get next or previous month
In the procedure firstly we get the passed month in numeric format in local variable V_MONTH_DIGIT. After that we execute a loop for generate months and store in local variables MONTH_STR1 and MONTH_STR2.

To execute procedure: EXEC GET_MONTHS('2007','APR');

Output: