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!

Help on usage of chr(13) in utl_file

Satyam ReddyOct 13 2010 — edited Oct 14 2010
Got the help or code from the forum only but i need to make a small modification to the code.sample code goes something like this .
SET serveroutput ON
DECLARE
        vstart  NUMBER := 1;
        bytelen NUMBER := 32000;
        LEN     NUMBER;
        my_vr RAW (32000);
        x NUMBER;
        l_output UTL_FILE.FILE_TYPE;
BEGIN
        -- define output directory
        l_output := UTL_FILE.FOPEN ('CWFSTORE_RESPONCE_XML', 'extract5001.txt', 'wb', 32760);
        vstart   := 1;
        bytelen  := 32000;
        --select bob locator
        FOR rec IN
        (SELECT (response_file) vblob
        FROM    clarity_response_log
        WHERE   TRUNC(date_crated) = '08-oct-10'
        )
        LOOP
                --get length of the blob
                LEN := DBMS_LOB.getlength (rec.vblob);
                DBMS_OUTPUT.PUT_LINE (LEN);
                x := LEN;
                -- if small enough for a single write
                IF LEN < 32760 THEN
                        UTL_FILE.put_raw (l_output, rec.vblob);
                        UTL_FILE.PUT_LINE(l_output, CHR(13));
                        UTL_FILE.FFLUSH (l_output);
                ELSE -- write in pieces
                        vstart      := 1;
                        WHILE vstart < LEN
                        AND bytelen  > 0
                        LOOP
                                DBMS_LOB.READ (rec.vblob, bytelen, vstart, my_vr);
                                UTL_FILE.put_raw (l_output, my_vr);
                                UTL_FILE.PUT_LINE(l_output,CHR(13));
                                UTL_FILE.FFLUSH (l_output);
                                -- set the start position for the next cut
                                vstart := vstart + bytelen;
                                -- set the end position if less than 32000 bytes
                                x               := x - bytelen;
                                IF x             < 32000 THEN
                                        bytelen := x;
                                END IF;
                        END LOOP;
                END IF;
        END LOOP;
        UTL_FILE.FCLOSE (l_output);
END;
My requirement goes something like this on the usage of
UTL_FILE.PUT_LINE(l_output, CHR(13));
do we need to use this code above or below the
 UTL_FILE.FFLUSH (l_output);
moreover can we replace
UTL_FILE.PUT_LINE(l_output, CHR(13);
with
UTL_FILE.PUT_LINE(l_output, CHR(13)||chr(10));
Please advise
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 11 2010
Added on Oct 13 2010
5 comments
1,257 views