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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

File only records 33Kb

DARLY AGOSTINHO5 days ago — edited 5 days ago

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 5 days ago
2 comments
64 views