4 Replies Latest reply: Jun 1, 2011 2:57 AM by BluShadow RSS

    UTL_FILE - reading file in subdirectory

    JackK
      Hi,
      I've got DIRECTORY object (SWDE_DIR) pointing to directory /oradata/mydata. There is also directory /oradata/mydata/dir1 with several files. I am able to run
      create or replace function get_filesize(p_file varchar2) return number is
        v_exists boolean;
        v_length number;
        v_blocksize number;
      begin
        UTL_FILE.fgetattr('SWDE_DIR', p_file, v_exists, v_length, v_blocksize);
        if v_exists then
          return v_length;
        end if;
        return -1;
      end get_filesize;
      When I run
      SELECT get_filesize('1.swd') FROM dual;
      the result is correct size of the file.
      However, when I run
      SELECT get_filesize('dir1/sub1.swd') FROM dual;
      the result is -1. I'm sure, the file exists there.
      What may be wrong?
        • 1. Re: UTL_FILE - reading file in subdirectory
          Saubhik
          Subdirectories are not accessible.
          http://download.oracle.com/docs/cd/E11882_01/appdev.112/e16760/u_file.htm#i1004618
          • 2. Re: UTL_FILE - reading file in subdirectory
            BluShadow
            JackK wrote:
            Hi,
            I've got DIRECTORY object (SWDE_DIR) pointing to directory /oradata/mydata. There is also directory /oradata/mydata/dir1 with several files. I am able to run
            create or replace function get_filesize(p_file varchar2) return number is
            v_exists boolean;
            v_length number;
            v_blocksize number;
            begin
            UTL_FILE.fgetattr('SWDE_DIR', p_file, v_exists, v_length, v_blocksize);
            if v_exists then
            return v_length;
            end if;
            return -1;
            end get_filesize;
            When I run
            SELECT get_filesize('1.swd') FROM dual;
            the result is correct size of the file.
            However, when I run
            SELECT get_filesize('dir1/sub1.swd') FROM dual;
            the result is -1. I'm sure, the file exists there.
            What may be wrong?
            The point of directory objects is that they point to directories that you should have access to. You cannot specify a sub directory as part of the filename. If you want access to that sub directory, then you need another directory object specifically declared to point to that.
            • 3. Re: UTL_FILE - reading file in subdirectory
              JackK
              Saubhik wrote:
              Subdirectories are not accessible.
              http://download.oracle.com/docs/cd/E11882_01/appdev.112/e16760/u_file.htm#i1004618
              BluShadow wrote:The point of directory objects is that they point to directories that you should have access to. You cannot specify a sub directory as part of the filename. If you want access to that sub directory, then you need another directory object specifically declared to point to that.
              What a pity. :(
              • 4. Re: UTL_FILE - reading file in subdirectory
                BluShadow
                JackK wrote:
                Saubhik wrote:
                Subdirectories are not accessible.
                http://download.oracle.com/docs/cd/E11882_01/appdev.112/e16760/u_file.htm#i1004618
                BluShadow wrote:The point of directory objects is that they point to directories that you should have access to. You cannot specify a sub directory as part of the filename. If you want access to that sub directory, then you need another directory object specifically declared to point to that.
                What a pity. :(
                Not a pity. It's called security.

                Previously it was the norm to set the utl_file_dir parameter of the database with accessible paths, but this had a couple of flaws... a) once a path was added then any database user would have access to that path (now directory objects need granting to specific users) and b) people misused it by including a "*" as the path, which gave access to the whole of the operating system which then meant that any user could access any part of the operating system files (o/s file permissions permitting), and that was a major security risk.

                That is why directory objects were introduced and why they are restrictive and explicit in their purpose.