SQL ORA-06512：在“SYS.UTL_FILE”，第 536 行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/35656393/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORA-06512: at "SYS.UTL_FILE", line 536
提问by Br. Sayan
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 .csvfile into an external oracle table.
我们在 Ubuntu 上的 SQL 过程中收到以下错误。同样的过程在 Windows 上正确执行。该过程的目标是将.csv文件导入到外部 oracle 表中。
The Oracle error:
甲骨文错误：
ORA-29283: invalid file operation ORA-06512: at "SYS.UTL_FILE", line 536 ORA-29283: invalid file operation
This SO question might have been a solution, but even after giving proper permissions chmod 777(which solved the issue in that case) to the files concerned we are facing the same error.
这个 SO question 可能是一个解决方案，但即使在给予相关文件适当的权限chmod 777（在这种情况下解决了问题）之后，我们也面临同样的错误。
ORA-29283: invalid file operation ORA-06512: at "SYS.UTL_FILE", line 536
The concerned portion of the procedure is :
该程序的相关部分是：
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;
The line on which the error is occurring is:
发生错误的行是：
file_handle := UTL_FILE.FOPEN('EXP_DATA',i.file_name,'R');
The permissions given to the file is as follows:
赋予文件的权限如下：
-rwxrwxrwx 1 rama rama 240 Feb 26 11:24 exp_41_02_2016.csv`
How to trace the problem which is causing this issue? Please let me know if you want any other information?
如何追踪导致此问题的问题？如果您需要任何其他信息，请告诉我？
采纳答案by Beege
It's an o/s file error...likely from permissions. The owner of the database shadow process that is executing the package is the o/s owner that needs permissions -- that's probably 'oracle'. You can test by logging in as 'oracle' on the database server and see if you can read the file in question. @alex poole offers a great thought to check the permissions of all parent directories.
这是一个 o/s 文件错误......可能来自权限。执行包的数据库影子进程的所有者是需要权限的操作系统所有者——这可能是“oracle”。您可以通过在数据库服务器上以“oracle”身份登录来进行测试，看看您是否可以读取有问题的文件。@alex poole 提供了一个很好的想法来检查所有父目录的权限。
