6 Replies Latest reply: May 17, 2012 12:01 PM by 899582 RSS

    UTL_FILE create a csv with 1002, 2003 and 3004 rows empty

    899582
      Hi

      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>>
        • 1. Re: UTL_FILE create a csv with 1002, 2003 and 3004 rows empty
          908002
          wht is L_LIMIT value getting ?
          UTL_FILE.NEW_LINE(CE_FILE); I suspect this statement causes that empty line after every l_limit number of lines.
          • 2. Re: UTL_FILE create a csv with 1002, 2003 and 3004 rows empty
            Tubby
            Wild guess here since you didn't share the complete code.

            Is your L_LIMIT variable set to 1000?
            896579 wrote:
            FETCH CUSTODIAN_EXTRACT_DETAILS bulk collect
            L_C55 limit L_LIMIT;
            and then when you end the loop (processing the bulk collect you limited) you write a new line
            896579 wrote:
            END LOOP;
            UTL_FILE.NEW_LINE(CE_FILE);
            • 3. Re: UTL_FILE create a csv with 1002, 2003 and 3004 rows empty
              899582
              Thanks so much ..I see some sense now in my code.
              Yes my L_LIMIT NUMBER is set to 1000;

              so what should be the limit to this ? since i really do not foresee the number of records that will be written to this csv file.

              Also is there any max limit to this?
              • 4. Re: UTL_FILE create a csv with 1002, 2003 and 3004 rows empty
                JustinCave
                Somewhere between 100 and 1000 is generally the most efficient limit for a bulk collect.

                If you don't want blank lines, simply remove the UTL_FILE.NEW_LINE call. If you want a blank line at the end of the file, move the NEW_LINE call after the loop rather than putting it inside the loop.

                Justin
                • 5. Re: UTL_FILE create a csv with 1002, 2003 and 3004 rows empty
                  Tubby
                  896579 wrote:
                  Thanks so much ..I see some sense now in my code.
                  Yes my L_LIMIT NUMBER is set to 1000;

                  so what should be the limit to this ? since i really do not foresee the number of records that will be written to this csv file.

                  Also is there any max limit to this?
                  There's nothing wrong with the limit that is there now. Somewhere between 100 and 1000 is usually the "sweet spot" so i wouldn't exceed that unless you have a good reason to.

                  I wasn't suggesting that there is a problem with the LIMIT, rather the bug in your code is affected by it's size (set it to 500 and you'll have an empty line every 500 records instead of 1000). You need to fix your logic, not the LIMIT.
                  • 6. Re: UTL_FILE create a csv with 1002, 2003 and 3004 rows empty
                    899582
                    Here is my code ,I am seeing an unusual problem It was all ok untill two weeks ago . AfterI made somechanges (added || Chr(13)|| Chr(10) to the ce_line at the end) , A csv file that should have more that 500 records is being truncated , after nearly 113 lines , i dont see any data . It looks some thing like Below. It terminates in between the line and no data after that .


                    I looked at the DBM_Output , Its shows me all 500+ records , Looks like the problem while writing to the file some things is going wrong.

                    Or is it because of some data issue that its does'nt consider.


                    "602992121     602992121     DATASCRUB     602992121     W                         3/31/2011     6/1/2011     5/1/2031     4.875     400000
                    602992315      602992315      DATASCRUB     602992315     W     DATASCRUB     602992315     K          3/14/2011     5/1/2011     4/1/2031     4.75     153000
                    602992336      602992336      DATASCRUB     602992336          DATASCRUB     602992336               3/28/2011     5/1/2011     4/1/2031     4.75     278400
                    602992717      602992717     DATASCRU                                                  
                                                                                     
                    "
                    CE_LINE VARCHAR2 (500);
                    CE_FILE UTL_FILE.FILE_TYPE;
                    L_C1 to L_C55 are defined SYS.ODCIVARCHAR2LIST;
                    L_LIMIT NUMBER := 1000;

                    <<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) || Chr(13)|| Chr(10);
                    UTL_FILE.PUT (CE_FILE, CE_LINE);
                    dbms_output.put_line (CE_LINE);
                    DBMS_OUTPUT.NEW_LINE;
                    END LOOP;
                    EXIT WHEN CUSTODIAN_EXTRACT_DETAILS%NOTFOUND;
                    end loop;
                    END IF;
                    CLOSE CUSTODIAN_EXTRACT_DETAILS;
                    END IF;
                    UTL_FILE.FCLOSE (CE_FILE);
                    <<Code>>