Calling Stored Procedure using Spring Framework

Problem
Owing to our report has performance issue, so I need to adjust SQL statement into store procedure. But how do I call stored procedure via Spring framework?

Solution
Lets us consider stored procedure as:

CREATE OR REPLACE PROCEDURE PROC_FMS451_REPORT(FYR IN VARCHAR2) AS YMINUS6_01 VARCHAR2(5);
 YMINUS6_02 VARCHAR2(5);
 YMINUS6_03 VARCHAR2(5);
 YMINUS6_04 VARCHAR2(5);
 YMINUS6_05 VARCHAR2(5);
 YMINUS6_06 VARCHAR2(5);
 YMINUS6_07 VARCHAR2(5);
 YMINUS6_08 VARCHAR2(5);
 YMINUS6_09 VARCHAR2(5);
 YMINUS6_10 VARCHAR2(5);
 YMINUS6_11 VARCHAR2(5);
 YMINUS6_12 VARCHAR2(5);
 YMINUS5_01 VARCHAR2(5);
 YMINUS5_02 VARCHAR2(5);
 YMINUS5_03 VARCHAR2(5);
 YMINUS5_04 VARCHAR2(5);
 YMINUS5_05 VARCHAR2(5);
 YMINUS5_06 VARCHAR2(5);
 YMINUS5_07 VARCHAR2(5);
 YMINUS5_08 VARCHAR2(5);
 YMINUS5_09 VARCHAR2(5);
 YMINUS5_10 VARCHAR2(5);
 YMINUS5_11 VARCHAR2(5);
 YMINUS5_12 VARCHAR2(5);
 YMINUS4_01 VARCHAR2(5);
 YMINUS4_02 VARCHAR2(5);
 YMINUS4_03 VARCHAR2(5);
 YMINUS4_04 VARCHAR2(5);
 YMINUS4_05 VARCHAR2(5);
 YMINUS4_06 VARCHAR2(5);
 YMINUS4_07 VARCHAR2(5);
 YMINUS4_08 VARCHAR2(5);
 YMINUS4_09 VARCHAR2(5);
 YMINUS4_10 VARCHAR2(5);
 YMINUS4_11 VARCHAR2(5);
 YMINUS4_12 VARCHAR2(5);
 YMINUS3_01 VARCHAR2(5);
 YMINUS3_02 VARCHAR2(5);
 YMINUS3_03 VARCHAR2(5);
 YMINUS3_04 VARCHAR2(5);
 YMINUS3_05 VARCHAR2(5);
 YMINUS3_06 VARCHAR2(5);
 YMINUS3_07 VARCHAR2(5);
 YMINUS3_08 VARCHAR2(5);
 YMINUS3_09 VARCHAR2(5);
 YMINUS3_10 VARCHAR2(5);
 YMINUS3_11 VARCHAR2(5);
 YMINUS3_12 VARCHAR2(5);
 YMINUS2_01 VARCHAR2(5);
 YMINUS2_02 VARCHAR2(5);
 YMINUS2_03 VARCHAR2(5);
 YMINUS2_04 VARCHAR2(5);
 YMINUS2_05 VARCHAR2(5);
 YMINUS2_06 VARCHAR2(5);
 YMINUS2_07 VARCHAR2(5);
 YMINUS2_08 VARCHAR2(5);
 YMINUS2_09 VARCHAR2(5);
 YMINUS2_10 VARCHAR2(5);
 YMINUS2_11 VARCHAR2(5);
 YMINUS2_12 VARCHAR2(5);
 YMINUS1_01 VARCHAR2(5);
 YMINUS1_02 VARCHAR2(5);
 YMINUS1_03 VARCHAR2(5);
 YMINUS1_04 VARCHAR2(5);
 YMINUS1_05 VARCHAR2(5);
 YMINUS1_06 VARCHAR2(5);
 YMINUS1_07 VARCHAR2(5);
 YMINUS1_08 VARCHAR2(5);
 YMINUS1_09 VARCHAR2(5);
 YMINUS1_10 VARCHAR2(5);
 YMINUS1_11 VARCHAR2(5);
 YMINUS1_12 VARCHAR2(5);
 YMINUS0_01 VARCHAR2(5);
 YMINUS0_02 VARCHAR2(5);
 YMINUS0_03 VARCHAR2(5);
 YMINUS0_04 VARCHAR2(5);
 YMINUS0_05 VARCHAR2(5);
 YMINUS0_06 VARCHAR2(5);
 YMINUS0_07 VARCHAR2(5);
 YMINUS0_08 VARCHAR2(5);
 YMINUS0_09 VARCHAR2(5);
 YMINUS0_10 VARCHAR2(5);
 YMINUS0_11 VARCHAR2(5);
 YMINUS0_12 VARCHAR2(5);

CURSOR YYYMM_CURSOR IS
SELECT LPAD(FYR-6, 3, '0')||'01' YMINUS6_01,
       LPAD(FYR-6, 3, '0')||'02' YMINUS6_02,
       LPAD(FYR-6, 3, '0')||'03' YMINUS6_03,
       LPAD(FYR-6, 3, '0')||'04' YMINUS6_04,
       LPAD(FYR-6, 3, '0')||'05' YMINUS6_05,
       LPAD(FYR-6, 3, '0')||'06' YMINUS6_06,
       LPAD(FYR-6, 3, '0')||'07' YMINUS6_07,
       LPAD(FYR-6, 3, '0')||'08' YMINUS6_08,
       LPAD(FYR-6, 3, '0')||'09' YMINUS6_09,
       LPAD(FYR-6, 3, '0')||'10' YMINUS6_10,
       LPAD(FYR-6, 3, '0')||'11' YMINUS6_11,
       LPAD(FYR-6, 3, '0')||'12' YMINUS6_12,
       LPAD(FYR-5, 3, '0')||'01' YMINUS5_01,
       LPAD(FYR-5, 3, '0')||'02' YMINUS5_02,
       LPAD(FYR-5, 3, '0')||'03' YMINUS5_03,
       LPAD(FYR-5, 3, '0')||'04' YMINUS5_04,
       LPAD(FYR-5, 3, '0')||'05' YMINUS5_05,
       LPAD(FYR-5, 3, '0')||'06' YMINUS5_06,
       LPAD(FYR-5, 3, '0')||'07' YMINUS5_07,
       LPAD(FYR-5, 3, '0')||'08' YMINUS5_08,
       LPAD(FYR-5, 3, '0')||'09' YMINUS5_09,
       LPAD(FYR-5, 3, '0')||'10' YMINUS5_10,
       LPAD(FYR-5, 3, '0')||'11' YMINUS5_11,
       LPAD(FYR-5, 3, '0')||'12' YMINUS5_12,
       LPAD(FYR-4, 3, '0')||'01' YMINUS4_01,
       LPAD(FYR-4, 3, '0')||'02' YMINUS4_02,
       LPAD(FYR-4, 3, '0')||'03' YMINUS4_03,
       LPAD(FYR-4, 3, '0')||'04' YMINUS4_04,
       LPAD(FYR-4, 3, '0')||'05' YMINUS4_05,
       LPAD(FYR-4, 3, '0')||'06' YMINUS4_06,
       LPAD(FYR-4, 3, '0')||'07' YMINUS4_07,
       LPAD(FYR-4, 3, '0')||'08' YMINUS4_08,
       LPAD(FYR-4, 3, '0')||'09' YMINUS4_09,
       LPAD(FYR-4, 3, '0')||'10' YMINUS4_10,
       LPAD(FYR-4, 3, '0')||'11' YMINUS4_11,
       LPAD(FYR-4, 3, '0')||'12' YMINUS4_12,
       LPAD(FYR-3, 3, '0')||'01' YMINUS3_01,
       LPAD(FYR-3, 3, '0')||'02' YMINUS3_02,
       LPAD(FYR-3, 3, '0')||'03' YMINUS3_03,
       LPAD(FYR-3, 3, '0')||'04' YMINUS3_04,
       LPAD(FYR-3, 3, '0')||'05' YMINUS3_05,
       LPAD(FYR-3, 3, '0')||'06' YMINUS3_06,
       LPAD(FYR-3, 3, '0')||'07' YMINUS3_07,
       LPAD(FYR-3, 3, '0')||'08' YMINUS3_08,
       LPAD(FYR-3, 3, '0')||'09' YMINUS3_09,
       LPAD(FYR-3, 3, '0')||'10' YMINUS3_10,
       LPAD(FYR-3, 3, '0')||'11' YMINUS3_11,
       LPAD(FYR-3, 3, '0')||'12' YMINUS3_12,
       LPAD(FYR-2, 3, '0')||'01' YMINUS2_01,
       LPAD(FYR-2, 3, '0')||'02' YMINUS2_02,
       LPAD(FYR-2, 3, '0')||'03' YMINUS2_03,
       LPAD(FYR-2, 3, '0')||'04' YMINUS2_04,
       LPAD(FYR-2, 3, '0')||'05' YMINUS2_05,
       LPAD(FYR-2, 3, '0')||'06' YMINUS2_06,
       LPAD(FYR-2, 3, '0')||'07' YMINUS2_07,
       LPAD(FYR-2, 3, '0')||'08' YMINUS2_08,
       LPAD(FYR-2, 3, '0')||'09' YMINUS2_09,
       LPAD(FYR-2, 3, '0')||'10' YMINUS2_10,
       LPAD(FYR-2, 3, '0')||'11' YMINUS2_11,
       LPAD(FYR-2, 3, '0')||'12' YMINUS2_12,
       LPAD(FYR-1, 3, '0')||'01' YMINUS1_01,
       LPAD(FYR-1, 3, '0')||'02' YMINUS1_02,
       LPAD(FYR-1, 3, '0')||'03' YMINUS1_03,
       LPAD(FYR-1, 3, '0')||'04' YMINUS1_04,
       LPAD(FYR-1, 3, '0')||'05' YMINUS1_05,
       LPAD(FYR-1, 3, '0')||'06' YMINUS1_06,
       LPAD(FYR-1, 3, '0')||'07' YMINUS1_07,
       LPAD(FYR-1, 3, '0')||'08' YMINUS1_08,
       LPAD(FYR-1, 3, '0')||'09' YMINUS1_09,
       LPAD(FYR-1, 3, '0')||'10' YMINUS1_10,
       LPAD(FYR-1, 3, '0')||'11' YMINUS1_11,
       LPAD(FYR-1, 3, '0')||'12' YMINUS1_12,
       LPAD(FYR, 3, '0')||'01' YMINUS0_01,
       LPAD(FYR, 3, '0')||'02' YMINUS0_02,
       LPAD(FYR, 3, '0')||'03' YMINUS0_03,
       LPAD(FYR, 3, '0')||'04' YMINUS0_04,
       LPAD(FYR, 3, '0')||'05' YMINUS0_05,
       LPAD(FYR, 3, '0')||'06' YMINUS0_06,
       LPAD(FYR, 3, '0')||'07' YMINUS0_07,
       LPAD(FYR, 3, '0')||'08' YMINUS0_08,
       LPAD(FYR, 3, '0')||'09' YMINUS0_09,
       LPAD(FYR, 3, '0')||'10' YMINUS0_10,
       LPAD(FYR, 3, '0')||'11' YMINUS0_11,
       LPAD(FYR, 3, '0')||'12' YMINUS0_12
FROM DUAL ;

 BEGIN OPEN YYYMM_CURSOR;

 FETCH YYYMM_CURSOR INTO YMINUS6_01,YMINUS6_02,YMINUS6_03,YMINUS6_04,YMINUS6_05,
                         YMINUS6_06,YMINUS6_07,YMINUS6_08,YMINUS6_09,YMINUS6_10,
                         YMINUS6_11,YMINUS6_12,YMINUS5_01,YMINUS5_02,YMINUS5_03,
                         YMINUS5_04,YMINUS5_05,YMINUS5_06,YMINUS5_07,YMINUS5_08,
                         YMINUS5_09,YMINUS5_10,YMINUS5_11,YMINUS5_12,YMINUS4_01,
                         YMINUS4_02,YMINUS4_03,YMINUS4_04,YMINUS4_05,YMINUS4_06,
                         YMINUS4_07,YMINUS4_08,YMINUS4_09,YMINUS4_10,YMINUS4_11,
                         YMINUS4_12,YMINUS3_01,YMINUS3_02,YMINUS3_03,YMINUS3_04,
                         YMINUS3_05,YMINUS3_06,YMINUS3_07,YMINUS3_08,YMINUS3_09,
                         YMINUS3_10,YMINUS3_11,YMINUS3_12,YMINUS2_01,YMINUS2_02,
                         YMINUS2_03,YMINUS2_04,YMINUS2_05,YMINUS2_06,YMINUS2_07,
                         YMINUS2_08,YMINUS2_09,YMINUS2_10,YMINUS2_11,YMINUS2_12,
                         YMINUS1_01,YMINUS1_02,YMINUS1_03,YMINUS1_04,YMINUS1_05,
                         YMINUS1_06,YMINUS1_07,YMINUS1_08,YMINUS1_09,YMINUS1_10,
                         YMINUS1_11,YMINUS1_12,YMINUS0_01,YMINUS0_02,YMINUS0_03,
                         YMINUS0_04,YMINUS0_05,YMINUS0_06,YMINUS0_07,YMINUS0_08,
                         YMINUS0_09,YMINUS0_10,YMINUS0_11,YMINUS0_12 ;

 CLOSE YYYMM_CURSOR;


COMMIT;

 --i. 取得前月的國庫收支現金結餘數, 並將前月的國庫收支現金結餘數搬到當月的期初餘額
--ii. 計算當月的國庫收支現金結餘數=期初餘額(1)+當月收入(2)合計-當月支出(3)
 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS6_01);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS6_01);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS6_02);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS6_02);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS6_03);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS6_03);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS6_04);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS6_04);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS6_05);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS6_05);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS6_06);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS6_06);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS6_07);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS6_07);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS6_08);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS6_08);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS6_09);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS6_09);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS6_10);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS6_10);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS6_11);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS6_11);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS6_12);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS6_12);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS5_01);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS5_01);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS5_02);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS5_02);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS5_03);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS5_03);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS5_04);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS5_04);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS5_05);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS5_05);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS5_06);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS5_06);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS5_07);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS5_07);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS5_08);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS5_08);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS5_09);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS5_09);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS5_10);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS5_10);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS5_11);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS5_11);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS5_12);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS5_12);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS4_01);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS4_01);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS4_02);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS4_02);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS4_03);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS4_03);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS4_04);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS4_04);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS4_05);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS4_05);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS4_06);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS4_06);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS4_07);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS4_07);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS4_08);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS4_08);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS4_09);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS4_09);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS4_10);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS4_10);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS4_11);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS4_11);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS4_12);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS4_12);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS3_01);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS3_01);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS3_02);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS3_02);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS3_03);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS3_03);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS3_04);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS3_04);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS3_05);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS3_05);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS3_06);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS3_06);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS3_07);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS3_07);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS3_08);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS3_08);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS3_09);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS3_09);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS3_10);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS3_10);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS3_11);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS3_11);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS3_12);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS3_12);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS2_01);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS2_01);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS2_02);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS2_02);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS2_03);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS2_03);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS2_04);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS2_04);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS2_05);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS2_05);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS2_06);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS2_06);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS2_07);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS2_07);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS2_08);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS2_08);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS2_09);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS2_09);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS2_10);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS2_10);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS2_11);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS2_11);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS2_12);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS2_12);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS1_01);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS1_01);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS1_02);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS1_02);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS1_03);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS1_03);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS1_04);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS1_04);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS1_05);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS1_05);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS1_06);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS1_06);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS1_07);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS1_07);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS1_08);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS1_08);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS1_09);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS1_09);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS1_10);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS1_10);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS1_11);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS1_11);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS1_12);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS1_12);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS0_01);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS0_01);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS0_02);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS0_02);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS0_03);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS0_03);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS0_04);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS0_04);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS0_05);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS0_05);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS0_06);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS0_06);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS0_07);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS0_07);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS0_08);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS0_08);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS0_09);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS0_09);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS0_10);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS0_10);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS0_11);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS0_11);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS0_12);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS0_12);

 END;

We will assume that required NamedParameterJdbcTemplate is injected in our DAO class.

 @Slf4j  
 public class Fms451faRepositoryImpl implements Fms451faRepositoryCustom {  
   @Autowired  
   private NamedParameterJdbcTemplate jdbcTemplate;  
   /**  
    * {@inheritDoc}  
    */  
   @Override  
   public void updateSubtotal(String yyyMM) {  
     jdbcTemplate.getJdbcOperations().update("call AP_PSR.PROC_FMS451_REPORT(?)", yyyMM);  
   }  
 }  

After moving Java code to stored procedure, the time spend to generate report from 3 minutes to 12 seconds. 


Reference
[1] http://docs.oracle.com/cd/B19306_01/appdev.102/a58231/ch3.htm

2014/05 Travel

從挑夫古道遠眺望龍埤

望龍埤

基隆外木山

[iReport] Exported Excel Report But Has Missing Data

Problem
I am using iReport 5.5 in my project, the requirement is to use iReport to design report template, and export the report with pdf and xls format.
The problem is as I export pdf format, I can see complete data

but as I export xls format, the xls report has lost data in page header band.

Solution
The missing data problem results from the overlapping elements (even 1 pixel).

After I fixed the overlapping problems (add one more space between elements)

I can see complete data in xls report.

How do I sort an array of files according to their last modified dates?

Apache commons IO provide LastModifiedFileComparator to do file sorting based on its last modified date/time.
It provide two instance:
1. LASTMODIFIED_COMPARATOR: Last modified comparator instance
2. LASTMODIFIED_REVERSE: Reverse last modified comparator instance

Example
1. Read files from old to new

   /**  
    * The main method.  
    *   
    * @param args  
    *      the arguments  
    */  
   public static void main(String args[]) {  
     Collection files =   
       FileUtils.listFiles(new File(FmsBatchConfig.INPUT_FOLDER_PATH), // assign file path  
                 FileFilterUtils.prefixFileFilter("COP0407"), // assign file name  
                 FalseFileFilter.FALSE);// include subdirectories  
     File[] fileArr = files.toArray(new File[files.size()]);  
     Arrays.sort(fileArr, LastModifiedFileComparator.LASTMODIFIED_COMPARATOR);//from old to new  
     // print file name  
     for (File file : fileArr) {  
       System.out.println(file.getName()+"["+new Date(file.lastModified())+"]");  
     }  
   }  

The console will print

 COP0407_01_1030501.txt[Thu May 08 16:45:01 CST 2014]  
 COP0407_01_1030502.txt[Fri May 23 10:31:45 CST 2014]  

2. Read files from new to old

   /**  
    * The main method.  
    *   
    * @param args  
    *      the arguments  
    */  
   public static void main(String args[]) {  
     Collection files =   
       FileUtils.listFiles(new File(FmsBatchConfig.INPUT_FOLDER_PATH), // assign file path  
                 FileFilterUtils.prefixFileFilter("COP0407"), // assign file name  
                 FalseFileFilter.FALSE);// include subdirectories  
     File[] fileArr = files.toArray(new File[files.size()]);  
     Arrays.sort(fileArr, LastModifiedFileComparator.LASTMODIFIED_REVERSE);//from new to old  
     // print file name  
     for (File file : fileArr) {  
       System.out.println(file.getName()+"["+new Date(file.lastModified())+"]");  
     }  
   }  

The console will print

 COP0407_01_1030502.txt[Fri May 23 10:31:45 CST 2014]  
 COP0407_01_1030501.txt[Thu May 08 16:45:01 CST 2014]  

Reference
[1] http://commons.apache.org/proper/commons-io/apidocs/org/apache/commons/io/comparator/LastModifiedFileComparator.html

How to get all files with specific name in a directory(including subdirectories) using Commons IO

Requirement
1. Search specific directory called "COP0407" 
2. Search through all directories called "COP0407" 

Supposed the directory is D:\psrdata\transfer\ftp\tofms, and has one subdirectory D:\psrdata\transfer\ftp\tofms\done

Solution
Apache commons IO package provide FileUtils for general file manipulation, here has two sample code
1. Search specific directory called "COP0407"  (exclude subdirectory)

   public static void main(String args[]) {  
     Collection files=  
     FileUtils.listFiles(new File(FmsBatchConfig.INPUT_FOLDER_PATH), //assign file path  
                    FileFilterUtils.prefixFileFilter("COP0407"), //assign file name  
                    FalseFileFilter.FALSE);//exclude subdirectories  
     //print file name  
     for(File file : files) {  
       System.out.println(file.getName());  
     }  
   }  

The console will print

 COP0407_01_1030501.txt  
 COP0407_01_1030502.txt  

2. Search through all directories called "COP0407" 

   public static void main(String args[]) {  
     Collection files=  
     FileUtils.listFiles(new File(FmsBatchConfig.INPUT_FOLDER_PATH), //assign file path  
                    FileFilterUtils.prefixFileFilter("COP0407"), //assign file name  
                    TrueFileFilter.TRUE);//include subdirectories  
     //print file name  
     for(File file : files) {  
       System.out.println(file.getName());  
     }  
   }  

The console will print

 COP0407_01_1030501.txt  
 COP0407_01_1030502.txt  
 COP0407_01_1030501 (2).txt  

Reference
[1] http://commons.apache.org/proper/commons-io/apidocs/org/apache/commons/io/FileUtils.html#listFiles(java.io.File, org.apache.commons.io.filefilter.IOFileFilter, org.apache.commons.io.filefilter.IOFileFilter)