UTL_FILE create a csv with 1002, 2003 and 3004 rows empty
899582Jan 30 2012 — edited May 17 2012Hi
below code will write almost 3500 records to a csv fie but unfortunately line 1002 , 2003 , and 3004 are empty , my data does not have these empty data.
Its seems like a patterm where every 1001 records has a blank line.. is there anything that i am missing? How can i trouble shoot this,little urgent
<<Code>>
OPEN CUSTODIAN_EXTRACT_DETAILS FOR V_CHR_SQL;
IF UTL_FILE.IS_OPEN (CE_FILE) THEN
LOOP
FETCH CUSTODIAN_EXTRACT_DETAILS bulk collect
INTO L_C1, L_C2, L_C3, L_C4, L_C5, L_C6, L_C7, L_C8, L_C9, L_C10, L_C11, L_C12, L_C13, L_C14, L_C15, L_C16, L_C17, L_C18, L_C19, L_C20 , L_C21, L_C22, L_C23, L_C24, L_C25, L_C26, L_C27, L_C28,
L_C29, L_C30, L_C31, L_C32, L_C33, L_C34, L_C35, L_C36, L_C37, L_C38, L_C39, L_C40, L_C41, L_C42, L_C43, L_C44, L_C45, L_C46, L_C47, L_C48, L_C49, L_C50 , L_C51, L_C52, L_C53, L_C54,
L_C55 limit L_LIMIT;
FOR I IN 1 .. L_C1.COUNT
LOOP
IF INSTR(L_C21(I) ,',') > 0 THEN
L_C21(I) := '"'||L_C21(I)||'"';
END IF;
CE_LINE := L_C1(I) || ',' || L_C2(I) || ',' || L_C3(I)|| ',' || L_C4(I) || ',' || L_C5(I)|| ',' || L_C6(I) || ',' || L_C7(I)|| ',' || L_C8(I) || ',' || L_C9(I) || ',' || L_C10(I) || ',' || L_C11(I) || ',' || L_C12(I)|| ',' || L_C13(I) || ',' || L_C14(I) || ',' || L_C15(I) || ',' || L_C16(I) || ',' || L_C17(I)|| ',' || L_C18(I) || ',' || L_C19(I)|| ',' || L_C20(I) || ',' || L_C21(I)|| ',' || L_C22(I) || ',' || L_C23(I) || ',' || L_C24(I)|| ',' || L_C25(I) || ',' || L_C26(I)|| ',' || L_C27(I) || ',' || L_C28(I) || ',' || L_C29(I) || ',' || L_C30(I) || ',' || L_C31(I)|| ',' || L_C32(I) || ',' || L_C33(I)|| ',' || L_C34(I) || ',' || L_C35(I)|| ',' || L_C36(I) || ',' || L_C37(I) || ',' || L_C38(I) || ',' || L_C39(I) || ',' || L_C40(I)|| ',' || L_C41(I) || ',' || L_C42(I) || ',' || L_C43(I) || ',' || L_C44(I) || ',' || L_C45(I)|| ',' || L_C46(I) || ',' || L_C47(I)|| ',' || L_C48(I) || ',' || L_C49(I)|| ',' || L_C50(I) || ',' || L_C51(I) || ',' || L_C52(I)|| ',' || L_C53(I) || ','
|| L_C54(I)|| ',' || L_C55(I) ;
UTL_FILE.PUT_LINE (CE_FILE, CE_LINE, FALSE);
END LOOP;
UTL_FILE.NEW_LINE(CE_FILE);
EXIT
WHEN CUSTODIAN_EXTRACT_DETAILS%NOTFOUND;
END LOOP;
END IF;
CLOSE CUSTODIAN_EXTRACT_DETAILS;
END IF;
UTL_FILE.FCLOSE (CE_FILE);
<<Code>>