2 Replies Latest reply: May 1, 2012 12:28 PM by damorgan RSS

    Problem with BFILENAME

    nk_arw
      Hi,

      I am trying to get a file from file system through BFILENAME

      Oracle directory is

      OWNER:     SYS
      DIRECTORY_NAME:     WORKING_DIR
      DIRECTORY_PATH:     /quote/working

      File name is: TEST_FILE.xml

      Script:
      ---------
      set serveroutput on
      DECLARE
      l_bfl_file_handle bfile := null;
      BEGIN
      L_BFL_FILE_HANDLE := BFILENAME('WORKING_DIR','TEST_FILE.xml');
      dbms_output.put_line('File handle: '||to_char(dbms_lob.fileexists(l_bfl_file_handle)));
      EXCEPTION
      when OTHERS then
      dbms_output.put_line(SQLERRM);
      END;

      Result:
      ---------
      anonymous block completed
      File handle: 0

      This result means that BFILENAME was not able to find the file at location WORKING_DIR (/quote/working). I am absolutely certain that file TEST_FILE.xml exists at correct location /quote/working

      This unit of code was working a month back and after database refresh it has stopped working. DBA team is not able to identify the issue. The permission on file is rwxrwxrwx.

      Can anybody please provde a clue why this might be happening?

      Cheers!
      -----------------------------------------------------------------------------------------
      Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
      PL/SQL Release 10.2.0.3.0
      IBM/AIX RISC System
        • 1. Re: Problem with BFILENAME
          sb92075
          nk_arw wrote:
          Hi,

          I am trying to get a file from file system through BFILENAME

          Oracle directory is

          OWNER:     SYS
          DIRECTORY_NAME:     WORKING_DIR
          DIRECTORY_PATH:     /quote/working

          File name is: TEST_FILE.xml

          Script:
          ---------
          set serveroutput on
          DECLARE
          l_bfl_file_handle bfile := null;
          BEGIN
          L_BFL_FILE_HANDLE := BFILENAME('WORKING_DIR','TEST_FILE.xml');
          dbms_output.put_line('File handle: '||to_char(dbms_lob.fileexists(l_bfl_file_handle)));
          EXCEPTION
          when OTHERS then
          dbms_output.put_line(SQLERRM);
          END;
          remove, eliminate, delete the whole & complete EXCEPTION handler

          re-run code then COPY & PASTE latest results
          • 2. Re: Problem with BFILENAME
            damorgan
            *NIX operating systems are case sensitive.

            Is your file really mixed case? "'TEST_FILE.xml"?

            You've checked file permissions ... but how about the entire directory tree leading to it?