Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

File only records 33Kb

DARLY AGOSTINHOMar 24 2025 — edited Mar 24 2025

My file records only 33KB. I would like to know why it happens. My code call utl_file.put when my variable Length is 30000 and after that it cleans and start to accumulate more lines and the file still opened.

This is my partial code:

OPEN cr_id_apolice;
LOOP
    FETCH cr_id_apolice BULK COLLECT INTO id_apolice LIMIT v_lines_limit;
    EXIT WHEN id_apolice.COUNT = 0;
        
    FOR i IN id_apolice.FIRST .. id_apolice.LAST
    LOOP
        OPEN cr_apolices(id_apolice(i).cod_org_prod
                        ,id_apolice(i).cod_ramo
                        ,id_apolice(i).apolice);
                        
         FETCH cr_apolices BULK COLLECT INTO reg;
         
         v_file := 'en'||
                   ltrim(to_char(id_apolice(i).cod_org_prod, '00'))     ||
                   ltrim(to_char(id_apolice(i).cod_ramo, '000'))        ||
                   ltrim(to_char(id_apolice(i).apolice, '0000000000'))  ||
                   '.txt';
         v_file_handle  := UTL_FILE.FOPEN(v_dir, v_file, 'W');
         v_lines := '';
         
         FOR idx IN reg.FIRST .. reg.LAST
         LOOP
            v_lines := v_lines || reg(idx).linha || CHR(10);
            
            -- Buffer Size
            IF LENGTH(v_lines) > 30000 THEN
                UTL_FILE.PUT(v_file_handle , v_lines);
                v_lines := '';
            END IF;
         END LOOP;
         CLOSE cr_apolices;
         
        -- Record remain lines
        IF LENGTH(v_lines) > 0 THEN
            UTL_FILE.PUT(v_file_handle , v_lines);
        END IF;
        UTL_FILE.FCLOSE(v_file_handle );
    END LOOP;
END LOOP;
CLOSE cr_id_apolice;
This post has been answered by Paulzip on Mar 24 2025
Jump to Answer
Comments
Post Details
Added on Mar 24 2025
2 comments
150 views