Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2006
    Posts
    46

    Unanswered: Writing a CLOB To AN ASCII FILE USING UTL_FILE

    I am trying to write out some fields to a file. I can get the process to work if I use small fields. I must, however, get the PL/SQL script to work for CLOBS which I have loaded into an Oracle database. Below is my code and it crashes on the following line:

    UTL_FILE.PUT_LINE(filehandler, ALL_FIELDS_COMBINED, FALSE) ;

    Can someone please explain what may be going wrong? Again, I can get the sp to work if I am using type Varchar but it crashes with my OCRtext field which can be documents that are about 30 or 40 pages long.

    Thanks. Also note, I tried to write without the TO_CHAR function.


    SPOOL 'd:\test.OUT' ;
    Set ServerOutput on ;
    Create OR Replace
    Procedure TEST_PRINT
    AS
    filehandler UTL_FILE.FILE_TYPE;
    ALL_FIELDS_COMBINED CLOB ;
    CR_FINAL_DATUM_BEGNUM Varchar2(4000);
    CR_FINAL_DATUM_DOCDATE Varchar2(4000) ;
    CR_FINAL_DATUM_DOCTYPE VARCHAR2(4000) ;
    CR_FINAL_DATUM_DESCRIPTION VARCHAR(4000) ;
    CR_FINAL_DATUM_STARTBATES VARCHAR(4000) ;
    CR_OCRTEXT CLOB ;

    CLOB_ALL_DATA CLOB ;

    CURSOR CUR_ALL_DATA IS SELECT
    FINAL_DATUM_BEGNUM, FINAL_DATUM_DOCDATE, FINAL_DATUM_DOCTYPE,
    FINAL_DATUM_DESCRIPTION, FINAL_DATUM_STARTBATES, OCRTEXT
    FROM ALL_EXHIBITS_FINAL ;


    Begin
    filehandler := UTL_FILE.FOPEN('D:\REVERSE', 'sample.out', 'w');

    OPEN CUR_ALL_DATA ;
    LOOP
    FETCH CUR_ALL_DATA INTO CR_FINAL_DATUM_BEGNUM,
    CR_FINAL_DATUM_DOCDATE,CR_FINAL_DATUM_DOCTYPE,
    CR_FINAL_DATUM_DESCRIPTION, CR_FINAL_DATUM_STARTBATES,
    CR_OCRTEXT ;

    ALL_FIELDS_COMBINED := CR_FINAL_DATUM_BEGNUM || '' ||
    CR_FINAL_DATUM_DOCDATE || '' ||
    TO_CHAR(CR_OCRTEXT) || '' ;




    UTL_FILE.PUT_LINE(filehandler, ALL_FIELDS_COMBINED, FALSE) ;

    EXIT WHEN CUR_ALL_DATA%NOTFOUND ;
    END LOOP ;
    /* UTL_FILE.PUTF(filehandler,'THIS IS A TEST!\n') ; */


    CLOSE CUR_ALL_DATA ;

    /*UTL_FILE.PUTF(filehandler,'THIS IS A TEST!!!\n') ;
    UTL_FILE.PUTF(filehandler,'THIS IS A TEST!!!\n') ;
    */




    UTL_FILE.FCLOSE(filehandler) ;


    End;

  2. #2
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Perhaps its reaching the buffer size, which is 32767, according to this.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •