6 Replies Latest reply on Dec 18, 2007 11:25 AM by 450441

    UTL_FILE => Invalid File Operation

    orclrunner
      I getting the following error:
      SQL> @c:\test_utlfile_read
      BEFORE OPEN CALL e2b_inform_2004012_81090031_10_200511221100.XML
      AFTER OPEN CALL e2b_inform_2004012_81090031_10_200511221100.XML
      BEFORE OPEN CALL e2b_inform_2004012_79980006_1_200508250700.XML
      DECLARE
      *
      ERROR at line 1:
      ORA-20012: ERROR: ORA-29283: invalid file operation
      ORA-06512: at "SYS.UTL_FILE", line 449
      ORA-29283: invalid file operation
      ORA-06512: at line 104

      The directory I am reading from is listed in the UTL_FILE_DIR parameter.
      The user I am logged in as has read/write privileges on the directory.
      On UNIX the WORLD is set to read/execute for the directory.

      It seems to be reading the first file in the cursor, but errors when trying to read the second file in the cursor - which makes no sense.

      Here is my code:
      DECLARE
      l_xml_path         varchar2(500) ;(100) := '/var/opt/clin/argus/ast401/xml/archive' ;
      l_receive_filename varchar2(255)  ;
      l_fileh   utl_file.file_type ;
      l_line varchar2(32767) ;
      l_text clob ;
      l_length number(7) ;
      l_start  number(7) ;
      l_end    number(7) ;
      
      CURSOR cur IS
           SELECT
                    m.receive_filename AS path
           FROM
                    esm_user.messages m ;
      
      BEGIN
      
         OPEN cur ;
            LOOP
              FETCH cur INTO l_receive_filename ;
              EXIT WHEN cur%NOTFOUND ;
              
              -- extract filename 
              l_length             :=   LENGTH(l_receive_filename) ;
              l_start              :=   INSTR(l_receive_filename,'/',-1,1) + 1;
              l_receive_filename   :=   SUBSTR(l_receive_filename,l_start,(l_length - l_start) + 1) ;
                       
              dbms_output.put_line('BEFORE OPEN CALL ' || l_receive_filename) ;
      
              l_fileh := utl_file.fopen(   l_xml_path
                                         , l_receive_filename
                                         , 'r'
                                         , 32767
                                       ) ; 
                                  
              dbms_output.put_line('AFTER OPEN CALL ' || l_receive_filename) ;
         
             LOOP
                BEGIN                       
      
                utl_file.get_line(  l_fileh
                                       ,l_line
                                      ,32767
                                     ) ; 
      
                l_text := l_text || l_line ;
      
              EXCEPTION
                    -- EOF exit loop
                    WHEN NO_DATA_FOUND THEN
                        exit ;
                    WHEN UTL_FILE.ACCESS_DENIED THEN
                        raise_application_error(-20001,'ACCESS DENIED: Access to the file has been denied by the operating system') ;
                    WHEN UTL_FILE.FILE_OPEN THEN
                        raise_application_error(-20002,'FILE OPEN: File is already open') ;
                    WHEN UTL_FILE.INTERNAL_ERROR THEN
                        raise_application_error(-20003,'INTERNAL ERROR: Unhandled internal error in the UTL_FILE package') ;
                    WHEN UTL_FILE.INVALID_FILEHANDLE THEN
                        raise_application_error(-20004,'INVALID FILE HANDLE: File handle does not exist') ;
                    WHEN UTL_FILE.INVALID_FILENAME THEN
                        raise_application_error(-20005,'INVALID FILENAME: A file with the specified name does not exist in the path') ;
                    WHEN UTL_FILE.INVALID_MAXLINESIZE THEN
                        raise_application_error(-20006,'INVALID MAXLINESIZE: The MAX_LINESIZE value for FOPEN() is invalid; it should be within the range 1 to 32767') ;
                    WHEN VALUE_ERROR THEN
                        raise_application_error(-20006,'VAULE ERROR: GET_LINE value is larger than the buffer') ;
                    WHEN UTL_FILE.INVALID_MODE THEN
                        raise_application_error(-20007,'INVALID MODE: The open_mode parameter in FOPEN is invalid') ;
                    WHEN UTL_FILE.INVALID_OPERATION THEN
                        raise_application_error(-20008,'INVALID OPERATION: File could not be opened or operated on as requested') ;
                    WHEN UTL_FILE.INVALID_PATH THEN
                        raise_application_error(-20009,'INVALID PATH: Specified path does not exist or is not visible to Oracle') ;
                    WHEN UTL_FILE.READ_ERROR THEN
                        raise_application_error(-20010,'READ ERROR: Unable to read file') ;
                    WHEN OTHERS THEN
                     raise_application_error(-20011,sqlcode || ', ' || sqlerrm) ;
      
                 END ;
              END LOOP ;
              
            END LOOP ;
              CLOSE cur ;
          
                 -- close the file handle
                 IF       utl_file.is_open(l_fileh) 
                 THEN
                          utl_file.fclose(l_fileh) ;
                 END IF ;
                 
           EXCEPTION
              WHEN OTHERS THEN
                 IF       utl_file.is_open(l_fileh) 
                 THEN
                          utl_file.fclose(l_fileh) ;
                 END IF ;
                 
                 IF     cur%ISOPEN 
                 THEN
                        CLOSE cur ;
                 END IF ;
                 raise_application_error(-20012,'ERROR: ' || sqlerrm) ;
      END ;
      /
      Message was edited by:
      jimmyb
        • 1. Re: UTL_FILE => Invalid File Operation
          John Spencer
          You code is doing something like this:
          OPEN CURSOR
          LOOP
             FETCH CURSOR
             OPEN FILE
             LOOP
                READ FILE
             END LOOP FILE
          END LOOP CURSOR
          CLOSE CURSOR
          CLOSE FILE
          So, when you hit the second record in your cursor, you are trying to open a new file into the same file handle which already has an open file associated with it. You need to close the first file before you try to open the first. The structure of you code needs to be something like:
          OPEN CURSOR
          LOOP
             FETCH CURSOR
             OPEN FILE
             LOOP
                READ FILE
             END LOOP FILE
             CLOSE FILE
          END LOOP CURSOR
          CLOSE CURSOR
          John
          • 2. Re: UTL_FILE => Invalid File Operation
            BluShadow
            FYI
            The directory I am reading from is listed in the UTL_FILE_DIR parameter.
            The user I am logged in as has read/write privileges on the directory.
            On UNIX the WORLD is set to read/execute for the directory.
            The UTL_FILE_DIR parameter has been deprecated by oracle in favour of direcory objects because of it's security problems.

            The correct thing to do is to create a directory object e.g.:
            CREATE OR REPLACE DIRECTORY mydir AS 'c:\myfiles';
            Note: This does not create the directory on the file system. You have to do that yourself and ensure that oracle has permission to read/write to that file system directory.

            Then, grant permission to the users who require access e.g....
            GRANT READ,WRITE ON DIRECTORY mydir TO myuser;
            Then use that directory object inside your FOPEN statement e.g.
            fh := UTL_FILE.FOPEN('MYDIR', 'myfile.txt', 'r');
            Note: You MUST specify the directory object name in quotes and in UPPER case for this to work as it is a string that is referring to a database object name which will have been stored in uppercase by default.

            ;)
            • 3. Re: UTL_FILE => Invalid File Operation
              602965
              1.please check the v$parameter table to check if the path is specified is available
              2.Make sure the file is present at that location
              3. and do a UTL_FILE.FCLOSE_ALL or FCLOSE(urfilehandle name)
              • 4. Re: UTL_FILE => Invalid File Operation
                BluShadow
                1.please check the v$parameter table to check if the path is specified is available
                That's only relevant if using the utl_file_dir parameter which the OP shouldn't be as I've already explained.
                3. and do a UTL_FILE.FCLOSE_ALL or FCLOSE(urfilehandle name)
                The OP is closing the file if you read the code.

                Good to see you've read the contents of the thread before responding.
                • 5. Re: UTL_FILE => Invalid File Operation
                  SamB
                  You can also get rid of most of your exception section. I don't see any reason for catching all of the UTL_FILE exceptions only to re-raise the same error with a user defined code.
                  • 6. Re: UTL_FILE => Invalid File Operation
                    450441
                    There might be a point if the OP was concatenating the filename causing the problem into the error message, but alas no.