Error using UTL_FILE Procedure

[megatron33]

When I run the following program I get the error message
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "SYS.UTL_FILE", line 98
ORA-06512: at "SYS.UTL_FILE", line 157
ORA-06512: at line 30


************************************************************
declare
v_tab varchar2(10) := ' ';
v_imperfect_ctr number(10) := 0;
v_file_name varchar2(50);
v_file utl_file.file_type;

cursor c_imperfect is
select BKTXT_PK ,
MATNR_KMC ,
ERFMG_MC ,
ERFME_MC ,
CHARG_MC ,
HSDAT_DMC ,
MHD_IO_DMC ,
VERAB_DMC ,
ZUSTD ,
FVDT1_DMC ,
VML ,
BWLVS ,
QMART
from CP48_FP01
where format_rule1||format_rule2||format_rule3||format_rule4||format_rule5||format_rule6 > 0;

begin
for rec1 in c_imperfect
loop
v_imperfect_ctr := v_imperfect_ctr + 1;
if v_imperfect_ctr = 1 then
v_file_name := 'ScunthorpeImperfect'||v_imperfect_ctr;
v_file := utl_file.fopen('/usr/users/awilli02/sql',v_file_name,'W');
utl_file.put_line(v_file,'NLENR'||v_tab);
end if;
end loop;
utl_file.fclose(v_file);
end;
/

 

[sem]

You're trying to open an already opened file in the loop but close it only once. Try to close it before opening a new one.

 

[mesuj]

Either the directory '/usr/users/awilli02/sql' doesn't have permission or the file. change the permission and check.

 

[JFlack]

sem probably has your answer, but it is a good idea to include an exception handler, especially when you use UTL_FILE. Do this:

EXCEPTION
WHEN UTL_FILE.INVALID_PATH THEN
DBMS_OUTPUT.PUT_LINE('Invalid path: /usr/users/awilli02/sql');
...
END;

Check the supplied packages manual for the other exceptions that UTL_FILE can raise.