title: ORA-06512: at "SYS.UTL_FILE", line 536
body: <p>We're getting the following error in an SQL procedure on Ubuntu. The same procedure is executing properly on Windows. The goal of the procedure is to import a <code>.csv</code> file into an external oracle table.</p>
  
  <p>The Oracle error:</p>
  
  <pre><code>ORA-29283: invalid file operation ORA-06512: at "SYS.UTL_FILE", line 536 ORA-29283: invalid file operation
  </code></pre>
  
  <p>This SO question might have been a solution, but even after giving proper permissions <code>chmod 777</code> (which solved the issue in that case) to the files concerned we are facing the same error.</p>
  
  <blockquote>
    <p><a href="https://stackoverflow.com/questions/6014884/ora-29283-invalid-file-operation-ora-06512-at-sys-utl-file-line-536">ORA-29283: invalid file operation ORA-06512: at "SYS.UTL_FILE", line 536</a></p>
  </blockquote>
  
  <p>The concerned portion of the procedure is :</p>
  
  <pre><code>for i in c loop   
      dbms_output.put_line('Inside for loop');
      select count(file_name) into f_n from t_filenames_exp e where i.file_name=e.file_name;
      if f_n=0 then
          dbms_output.put_line('Inside if statement');
          insert into t_filenames_exp(file_name,status) values(i.file_name,'need_to_read');
          select f_id into f_idn from t_filenames_exp e where e.file_name=i.file_name;
          select substr(i.file_name,instr(i.file_name,'exp_'),4) into fchk from dual;
          select to_char(to_date(to_char(SEQ_DATE,'dd-MON-yyyy'),'dd-MON-yyyy'),'D') into v_sufix from M_LAB_ID_SEQ_TAB;
          if(fchk='exp_') then
                file_handle := UTL_FILE.FOPEN('EXP_DATA',i.file_name,'R');
                file_handle1 := UTL_FILE.FOPEN('EXP','staging.txt','W');
              loop
                  begin
                     utl_file.get_line(file_handle,output_line);
                     utl_file.put_line(file_handle1,output_line);  
                     exception when no_data_found then
                      exit;
                  end;  
              end loop;
  
              if utl_file.is_open(file_handle) then
                 utl_file.fclose(file_handle);
              end if;
  
              if utl_file.is_open(file_handle1) then
                 utl_file.fclose(file_handle1);
              end if;  
  
                 /*insert into  t_files_data_exp(f_id,patient_last_name,patient_name,patient_id_code,method_internal_index,method_acronym,index_repeat,result,unit_of_measurement,minimum_reference_value,maximum_reference_value,date_)
                 select f_idn,patient_last_name,patient_name,(patient_id_code ||'H'||v_sufix),method_internal_index,method_acronym,index_repeat,result,  unit_of_measurement,minimum_reference_value,maximum_reference_value,date_ from files_data;*/
                 insert into  t_files_data_exp(f_id,patient_last_name,patient_name,patient_id_code,method_internal_index,method_acronym,index_repeat,result,unit_of_measurement,minimum_reference_value,maximum_reference_value,date_)
                  select f_idn,'Unknown','Unknown',(r.patient_id_code ||'H'||v_sufix),m.CANONICAL_ID,r.METHOD_ACRONYM,0,r.RESULT,r.unit_of_measurement,'','','' from files_data r ,M_METHODS_EXP m where m.METHOD_ACRONYM=r.METHOD_ACRONYM;
  
          else  
             file_handle := UTL_FILE.FOPEN('EXP_DATA',i.file_name,'R');
             file_handle1 := UTL_FILE.FOPEN('EXP','staging1.txt','W');
              loop
                  begin
                     utl_file.get_line(file_handle,output_line);
                     utl_file.put_line(file_handle1,output_line);
                       exception when no_data_found then
                        exit;
                  end;  
              end loop;
  
              if utl_file.is_open(file_handle) then
                 utl_file.fclose(file_handle);
              end if;
  
              if utl_file.is_open(file_handle1) then
                 utl_file.fclose(file_handle1);
              end if;
                   insert into  t_files_data_exp(f_id,patient_last_name,patient_name,patient_id_code,method_internal_index,method_acronym,index_repeat,result,unit_of_measurement,minimum_reference_value,maximum_reference_value,date_)  
                   select f_idn,'Unknown','Unknown',r.PATIENT_ID_CODE,m.CANONICAL_ID,r.METHOD_ACRONYM,0,r.RESULT,r.unit_of_measurement,'','','' from rfiles_data r ,M_METHODS_EXP m where m.METHOD_ACRONYM=r.METHOD_ACRONYM;                 
          end if;          
         -- Check whether the data in csv file is same as data in external table.         
          if trace='on' then
              if(fchk='expr_')  then    
                dbms_output.put_line('**************************************************');
                select i.file_name,count(*) into filename,rows_in_file from files_data;
                dbms_output.put_line('File name :'||filename);
                dbms_output.put_line('Total no of rows :'||rows_in_file);
                select replace(filename,'exp','_exp')  into new_filename from dual;       
                file_handle2 := UTL_FILE.FOPEN('EXP_LOG',new_filename,'W');
                  for i1 in cur loop                      
                    /*data:=to_char (i1.patient_last_name||';'||i1.patient_name||';'||i1.patient_id_code||';'||i1.method_internal_index||';'||i1.method_acronym||';'||i1.index_repeat||';'||i1.result||';'||i1.unit_of_measurement||';'||i1.minimum_reference_value||';'||i1.maximum_reference_value||';'||i1.date_);*/
  
                    data:=to_char(i1.patient_id_code||';'||i1.method_acronym||';'||i1.result||';'||i1.unit_of_measurement);
                    utl_file.put_line(file_handle2,data);
                  end loop;  
              else
                  dbms_output.put_line('**************************************************');
                  select i.file_name,count(*) into filename,rows_in_file from rfiles_data;
                  dbms_output.put_line('File name :'||filename);
                  dbms_output.put_line('Total no of rows :'||rows_in_file);
                  select replace(filename,'expr','_expr')  into new_filename from dual;         
                  file_handle2 := UTL_FILE.FOPEN('EXP_LOG',new_filename,'W');     
                  for i1 in cur1 loop                      
                     /*data:=to_char (i1.m_id||';'||i1.date_||';'||i1.METHOD_ACRONYM||';'||i1.PATIENT_ID_CODE||';'||i1.TEST_TYPE||';'||i1.UNIT_OF_MEASUREMENT||';'||i1.RESULT||';'||i1.RESULT_FLAG); */
                     data:=to_char(i1.patient_id_code||';'||i1.method_acronym||';'||i1.result||';'||i1.unit_of_measurement);
                   utl_file.put_line(file_handle2,data);
                  end loop;  
              end if;
  
              if utl_file.is_open(file_handle2) then
                utl_file.fclose(file_handle2);
              end if;         
                 dbms_output.put_line('*************************************************');
  
          end if;
  
      end if;   
    end loop; 
  </code></pre>
  
  <p>The line on which the error is occurring is:</p>
  
  <pre><code>file_handle := UTL_FILE.FOPEN('EXP_DATA',i.file_name,'R');
  </code></pre>
  
  <p>The permissions given to the file is as follows:</p>
  
  <pre><code>-rwxrwxrwx 1 rama rama 240 Feb 26 11:24 exp_41_02_2016.csv`
  </code></pre>
  
  <p><em>How to trace the problem which is causing this issue?
  Please let me know if you want any other information?</em></p>