PL/SQL block fails in job but succeeds in SQL*Plus I created a PL/SQL block which gathers certain data from tables in my Oracle 12c database and writes them into a .csv file.
I cannot share the whole PL/SQL block but can outline the code

--create directory
str := q'{create directory DIR as '/tmp/tmp_dir'}';
execute immediate (str);

--get filename
select 'tmp_stat' || '.csv'
into filename
from dual;

--open file
tmp_file := UTL_FILE.fopen('DIR', filename, 'W', 32767);

--write header
UTL_FILE.put_line (tmp_file, 'col1;col2');

-- gather and write data
for stat in ([select statement]) LOOP
UTL_FILE.put_line (tmp_file, stat.dat);
END LOOP;
--close file
UTL_FILE.fclose (tmp_file);


When I run my code in SQL*Plus it works fine. But when I turn it into a job with the following options


Raise Events: None
Maximum Run Duration (minutes): None
Priority: Medium
Schedule Limit (minutes): None
Maximum Runs: None
Maximum Failures: None
Job Weight: 1
Instance Stickiness: TRUE


When I try to run the job with the exact same code (except the / to mark the end of the block), I get the error

ORA-29283: invalid file operation ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation ORA-06512: at line 61 


I don't have a line 536 in my code, but line 61 is

tmp_file := UTL_FILE.fopen('DIR', filename, 'W', 32767);


Any suggestions on why the error is raised when executing the code is a job?
What happened is that I had all the permissions and privileges set right except I forgot to create the directory on the second node too.

So whenever the job ran on the second instance (which it by chance did) the error was raised that the file could not be written.



Of course it had to be as simple as that but I want to post it as an answer anyway.

When working with an Oracle RAC your mantra should always be "Keep in mind the other instances."