0

I have an SQL file which contains 2 block of code, both using UTL_FILE functions to write 2 different files in same directory.

The issue I encounter is the first block of code executes successfully but when executing the second block of code I encounter ORA-29283: invalid file operation.

My code is as follows:

DECLARE
   fileHandler UTL_FILE.FILE_TYPE;
   vline varchar2(4000);
 BEGIN
   fileHandler := UTL_FILE.FOPEN('STAGING_REPORT', 'Report_1.csv', 'W',4000);

    UTL_FILE.FCLOSE(fileHandler);

end;
/

DECLARE
   fileHandler UTL_FILE.FILE_TYPE;
   vline varchar2(4000);
 BEGIN
   fileHandler := UTL_FILE.FOPEN('STAGING_REPORT', 'Report_2.csv', 'W',4000);

   UTL_FILE.FCLOSE(fileHandler);

end;
/

When I try to execute this on SQLDeveloper I firstly executed two block separately. In that scenario, thew first block executed successfully but the second block threw ORA-29283.

In second attempt, I tried to execute both block simultaneously i.e selecting both of them and hitting RUN. In this scenario too, my first block executes successfully but my second block doesn't.

After numerous such attempts, currently, both of the blocks throw the ORA_29283 error.

There are no report files(REPORT_1 and REPORT_2) already in the STAGING_REPORT. The UTL_FILE.FOPEN create them in runtime.

What seems peculiar is this code executed successfully just half-week before and suddenly it is behaving abnormally.

I am running on Oracle 12c. Is there anything specific I am missing here?

(I have already checked the permissions and other basic things like permission to directory and if directory exists , as I earlier mentioned this code was executing half a week before and it partially executed when I execute it manually )

I have also tried using two different handler names for both the block but no success came my way

APC
  • 144,005
  • 19
  • 170
  • 281
Mighty God Loki
  • 135
  • 3
  • 10
  • 1
    Are you running RAC? And if so do the directories exist on all nodes; and the files don't exist on any nodes? I suspect you're seeing this a little intermittently as you hit different instances. I would expect one session to be consistent though. (Your second attempt is still running the blocks in sequence, not in parallel, incidentally.) – Alex Poole Sep 17 '19 at 17:56
  • I am not sure what RAC means at this point but the directory is on remove linux server. This directory has a symlink. "(Your second attempt is still running the blocks in sequence, not in parallel, incidentally.) " I already suspected this but was not sure how can I mitigate this can you please give some tips? – Mighty God Loki Sep 18 '19 at 07:57
  • 1
    RAC is [Real Application Clusters](https://docs.oracle.com/en/database/oracle/oracle-database/12.2/racad/introduction-to-oracle-rac.html#GUID-D04AA2A7-2E68-4C5C-BD6E-36C62427B98E). Essentially your database is shared across multiple servers. If you have access, run `select * from v$active_instances;` and see how many rows are returned - if it's only one then this isn't the issue. But you may have *multiple* remote Linux servers, and the directory would have to exist on all of them. – Alex Poole Sep 18 '19 at 08:10
  • Hi Alex thank you for explaining. I tried the query and found that there are two active servers and cross-checked the directory. The directory is present on both the servers. There are two servers 41 and 42(which I saw in query result) but the connection string used before these blocks refers to 42 i.e only 42 is connected, would still the presence or absence of dir on 41(the other server) affect this code(which runs on 42)? – Mighty God Loki Sep 18 '19 at 09:42
  • 1
    You're connecting to a specific instance? (And do you reconnect between them?) This isn't really an area I know a lot about, I'm unclear if it could still hand off to a different instance - even min-session. It might be worth checking/asking DBAs if anything unusual is happening, maybe one node is overloaded? (Also, I guess, check the privs are the same for the whole path down to the directory on both servers, so whether the Oracle account can read/write to it; and whether the target file already exists on either server...) – Alex Poole Sep 18 '19 at 10:02
  • If you have access, you might also want to look at [MoS doc ID 1313404.1](https://support.oracle.com/epmos/faces/DocContentDisplay?id=1313404.1), and there are other documents about investigating this error further if it doesn't turn out to be something simple. – Alex Poole Sep 18 '19 at 15:14

1 Answers1

0

In second query you missed the file extension. Add .csv or some other extension hence invalid file operation.

Himanshu
  • 3,830
  • 2
  • 10
  • 29
  • Actually the original code has the proper extension. I made a mistake in the mentioned code. But I have corrected it now. – Mighty God Loki Sep 17 '19 at 17:37
  • 1
    Can you share the output by running these 2 again or may be the file has not been closed the reason you are getting the second block failed. Try to run the second block first I guess that ll not fail – Himanshu Sep 17 '19 at 17:38
  • My output is as follows: `Error report: ORA-29283: invalid file operation ORA-06512: at "SYS.UTL_FILE", line 536 ORA-29283: invalid file operation ORA-06512: at line 5 29283. 00000 - "invalid file operation" *Cause: An attempt was made to read from a file or directory that does not exist, or file or directory access was denied by the operating system. *Action: Verify file and directory access privileges on the file system, and if reading, verify that the file exists.` – Mighty God Loki Sep 18 '19 at 08:00
  • Also as you mentioned I had already tried executing second block first but still I got error in that block. Also I tried by inserted a new anonymous block between the two with `utl_file.fcloseall` function but that too didnt work. I dont understand why the file from first block will hinder the execution of second block because both of them have different names – Mighty God Loki Sep 18 '19 at 08:02