I have a plsql procedure that runs successfully . When i call this procedure into another procedure in a different package, it error's out with invalid file id .
Proc test1(debug varchar2 default 0) as
dt date;
cursor 1
select sysdate as dt from dual;
Begin
IF month(dt) < Dec THEN
update table1
set term_date= sysdate +10
where table1_year='2023'
END IF;
End;
Procedure test2(value varchar2) as
Begin
report_file:=utl_file.fopen(lC_report_dir_obj_out,lC_report_fname,'w');
If value='Y' THEN
BEGIN
Proc test1(1);
dbms_output.put_line('Run test1 success');
lv_msg :=success;
Exception
WHEN OTHERS THEN
dbms_output.put_line('Run test1 success');
lv_msg :=sqlerrm;
END;
utl_file.put_line(report_file,lv_msg,autoflush=>TRUE);
End if ;
End test2;
Error : ORA-29282: invalid file ID ORA-06512: at "SYS.UTL_FILE", line 166 ORA-06512: at "SYS.UTL_FILE", line 866
It has no issues writing to the file if not for the procedure call . The procedure has numerous loops and go by each row from the cursor .
