This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Mar 10, 2010 1:20 AM by 756740 RSS

utl_file.fopen() gives "ORA-29284: file read error" for MS Word doc

756740 Newbie
Currently Being Moderated
I have two files in the same folder and having the same rights. One file is pdf and the other one is MS Word. These files are stored in a linux server. I can open the pdf file with utl_file.fopen() but can't open Word file. It gives me error ORA-29284 file read error. I can access the MS Word file through oracle text query using "contains" expression. But I want to get a particular string value from that file thats why I have to use utl_file.fopen() and utl_file.get_line() functions.
Is there any issue in accessing MS Word file stored in linux through oracle function? Any help would be appreciated.

Thanks and regards.
  • 1. Re: utl_file.fopen() gives "ORA-29284: file read error" for MS Word doc
    sybrand_b Guru
    Currently Being Moderated
    Why are you assuming utl_file works with binary files? It doesn't!
    You need to treat Word documents like BLOBs or BFILEs and read up on the dbms_lob package in the documentation.


    ----------
    Sybrand Bakker
    Senior Oracle DBA
  • 2. Re: utl_file.fopen() gives "ORA-29284: file read error" for MS Word doc
    Solomon Yakobson Guru
    Currently Being Moderated
    sybrand_b wrote:
    Why are you assuming utl_file works with binary files? It doesn't!
    It does in 10g, where file open mode B was introduced. But even with R mode you should be able to open DOC file. You might (or might not) have problem reading it:
    SQL> declare
      2      fh UTL_FILE.FILE_TYPE;
      3      input_buffer varchar2(4000);
      4  begin
      5      fh := UTL_FILE.FOPEN('TEMP','Stats.doc','r');
      6      UTL_FILE.GET_LINE(fh,input_buffer,4000);
      7      DBMS_OUTPUT.PUT_LINE(input_buffer);
      8      UTL_FILE.FCLOSE(fh);
      9    exception
     10      when others then UTL_FILE.FCLOSE(fh);
     11  end;
     12  /
    ÐÏࡱᠠ              >  þÿ                 2          4      þÿÿÿ    1
    ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ
    ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ
    ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ
    ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ
    ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ
    ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿì¥Á €    ð¿             ¸
    
    PL/SQL procedure successfully completed.
    
    SQL> 
    SY.
  • 3. Re: utl_file.fopen() gives "ORA-29284: file read error" for MS Word doc
    756740 Newbie
    Currently Being Moderated
    Thanks Solomon for your code. but still with your code the word document generates an exception and the pdf file is opened without any problem, even though both the files have the same rights and owner in linux.
  • 4. Re: utl_file.fopen() gives "ORA-29284: file read error" for MS Word doc
    BluShadow Guru Moderator
    Currently Being Moderated
    Is the document locked by another session/process?
  • 5. Re: utl_file.fopen() gives "ORA-29284: file read error" for MS Word doc
    756740 Newbie
    Currently Being Moderated
    nops its not locked and infact is accessible by the following code. The folllowing code gives the output 'Exists'

    declare inp_bfile BFILE;
    begin
    inp_bfile := bfilename( 'DOCUMENT_TAB_DIR', 'doc_name.doc');
    if dbms_lob.fileexists( inp_bfile ) = 1 then
    begin
    dbms_output.put_line( 'Exists!');
    dbms_output.put_line( 'Filesize: '||dbms_lob.getlength( inp_bfile ));
    dbms_lob.fileopen( inp_bfile, dbms_lob.file_readonly );
    dbms_lob.fileclose( inp_bfile );
    end;
    else
    dbms_output.put_line( 'doesn''t exist!');
    end if;
    end;

    -------
    The following code gives the document name "doc_name.doc" as the output which contains the string "search_string"

    SELECT
    doc_id_no
    , doc_name
    , DBMS_LOB.getLength(doc_value) bytes
    , score(1) score
    , TO_CHAR(timestamp, 'DD-MON-YYYY HH24:MI:SS') timestamp
    FROM document_tab
    WHERE contains (doc_value, 'search_string', 1) > 0

    -----

    But the following code gives the error "ORA-29284: file read error"

    DECLARE
    InHandle utl_file.file_type;
    buffer varchar2(4000);
    BEGIN
    InHandle := utl_file.fopen('DOCUMENT_TAB_DIR', 'doc_name.doc', 'R');
    utl_file.get_line(InHandle, buffer);
    dbms_output.put_line('DATA FROM FILE: '||buffer);

    IF utl_file.is_open(InHandle) THEN
    utl_file.fclose_all;
    dbms_output.put_line('Closed All');
    END IF;
    END fopen;
    -------
    However the above code works for "doc_name.pdf" file

    File permissions for both the files are

    doc_name.doc -rw-r-r-- oracle dba
    doc_name.pdf -rw-r-r-- oracle dba

    If you can think of any reason why this is not working then please give me a clue. any help would be appreciated.

    Thanks and regards,
    barq
  • 6. Re: utl_file.fopen() gives "ORA-29284: file read error" for MS Word doc
    BluShadow Guru Moderator
    Currently Being Moderated
    barq wrote:
    But the following code gives the error "ORA-29284: file read error"

    DECLARE
    InHandle utl_file.file_type;
    buffer varchar2(4000);
    BEGIN
    InHandle := utl_file.fopen('DOCUMENT_TAB_DIR', 'doc_name.doc', 'R');
    utl_file.get_line(InHandle, buffer);
    dbms_output.put_line('DATA FROM FILE: '||buffer);

    IF utl_file.is_open(InHandle) THEN
    utl_file.fclose_all;
    dbms_output.put_line('Closed All');
    END IF;
    END fopen;
    -------
    However the above code works for "doc_name.pdf" file
    I'm surprised it works for the PDF file.

    DOC files and PDF files are binary files, not text.

    You should be opening the file with 'RB" not 'R' and you should be using utl_file.get_raw rather than utl_file.get_line.

    You also won't be able to output the data using dbms_output.put_line as that is expecting varchar2 data, not the binary data of the file.
  • 7. Re: utl_file.fopen() gives "ORA-29284: file read error" for MS Word doc
    Marwim Expert
    Currently Being Moderated
    DOC files and PDF files are binary files, not text.
    No, PDF is a text file, you can open it e.g. with Notepad and read the content in plain text if it's not encoded in a stream (http://blog.didierstevens.com/2008/05/19/pdf-stream-objects/)

    Regards
    Marcus
  • 8. Re: utl_file.fopen() gives "ORA-29284: file read error" for MS Word doc
    BluShadow Guru Moderator
    Currently Being Moderated
    Marwim wrote:
    DOC files and PDF files are binary files, not text.
    No, PDF is a text file, you can open it e.g. with Notepad and read the content in plain text if it's not encoded in a stream (http://blog.didierstevens.com/2008/05/19/pdf-stream-objects/)

    Regards
    Marcus
    Most PDF files contain binary data of some sort whether it's encoded or not, e.g. images etc. What i meant, to be more precise, is that PDF's are not made of lines of text that can be easily read using GET_LINE. They should be treated as Binary data, not character data.
  • 9. Re: utl_file.fopen() gives "ORA-29284: file read error" for MS Word doc
    Marwim Expert
    Currently Being Moderated
    What i meant, to be more precise, is that PDF's are not made of lines of text that can be easily read using GET_LINE. They should be treated as Binary data, not character data.
    True, the only useful thing (beside storing them) I found one can do with RTF in the DB is indexing with Oracle Text. So why read them line by line.

    Marcus
  • 10. Re: utl_file.fopen() gives "ORA-29284: file read error" for MS Word doc
    Solomon Yakobson Guru
    Currently Being Moderated
    barq wrote:

    But the following code gives the error "ORA-29284: file read error"
    As I said, "You might (or might not) have problem reading it". What get_line does? It reads file till it either finds EOL or runs out of buffer. In your case you did not specify buffer size (max_linesize) in utl_file.fopen so it defaults to 1024 (including EOL which could be 1 or 2 bytes depending on OS). Try opening file with max_linesize of 32767. But frankly, what is the need to open Word file as text? I think you should open it not in R mode but rather in RB (read byte) mode and use utl_file.get_raw to read Word file as binary file.

    SY.
  • 11. Re: utl_file.fopen() gives "ORA-29284: file read error" for MS Word doc
    610476 Newbie
    Currently Being Moderated
    I had a couple thoughts on this. First, is there some compelling reason to do this from within the Oracle Server? Over the years, about the only thing I've used UTL_FILE for, is writing data, usually log type data. There are many libraries in other languages, for handling files of various sorts, then storing them in the database.

    In general, we use PERL or Java to do this kind of work, but that's just our environment.

    If there is some compelling reason to handle this with UTL_FILE, could you tell us a bit more about the file system in which these files are stored? I've had some weird issues with NFS mounted file systems, and such.
  • 12. Re: utl_file.fopen() gives "ORA-29284: file read error" for MS Word doc
    756740 Newbie
    Currently Being Moderated
    Yes, It works for the PDF file. But it gives me binary characters from the PDF file like '%ÞßÏÓ' etc.
    Similar output is generated when i use utl_file.get_raw function and open the file with 'RB'. It shows complete binary information even when i convert the raw string to the varchar2 using utl_raw.cast_to_varchar2() function.
  • 13. Re: utl_file.fopen() gives "ORA-29284: file read error" for MS Word doc
    756740 Newbie
    Currently Being Moderated
    Actually I have indexed it with Oracle Text. But I have to search the document to get a certain string as well. Like in this case, my documents are stored in the database and I want to search for a particular contract document and in that document I want to extract the name of the first party of the contract. Using Oracle Text, I think I can search the document but can't extract the relevant information from it, or so I understand.
  • 14. Re: utl_file.fopen() gives "ORA-29284: file read error" for MS Word doc
    756740 Newbie
    Currently Being Moderated
    Whether I open it with the 'R' or 'RB' format, it gives the same response. Well If file is opened first then I can go for either 'R' or 'RB' but the problem is that file is not opening in the first place.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points