1

I am trying to open a XLSX file using Anton Scheffer's package (here). I have my path object created, rights etc. And still get error below when I run my query. Tried lots of methods and i couldn't find a way to fix this . Please, any idea can help.

Query:

SELECT * FROM table( as_read_xlsx.read( as_read_xlsx.file2blob( 'FILE_OBJ', 'my_file.xlsx' ) ) );

ORA-22288: file or LOB operation FILEOPEN failed

Tried with a xlsx file that does not exists and I know that it gets to this path and finds my files but somehow I really can't figure out what's wrong.

Update: I have successfully accessed same file using UTL_FILE using the same Object with the same path yesterday but UTL_FILE reads only text files (ex. txt, csv, etc.) and this doesn't helps me. At least I know that this have rights for read.

Adi
  • 311
  • 4
  • 19
  • Is this the complete error message? – HelenA Sep 05 '18 at 08:35
  • Indeed the complete error message – Adi Sep 05 '18 at 08:35
  • Is `FILE_OBJ` an Oracle directory object that you have privileges on, and which points to an OS directory on the database server (not client), and Oracle has permission to read the files in that OS directory? How do you know that "it gets to this path and finds my files"? – Alex Poole Sep 05 '18 at 08:44
  • @AlexPoole If I change filename to something that does not exist, I will get an error that the filename it's not on the server. That's how i figured out. – Adi Sep 05 '18 at 09:19
  • OK, then are you sure the Oracle owner (usually `oracle`) has read permission on the files, not just the directory they are in? – Alex Poole Sep 05 '18 at 09:23
  • @AlexPoole , I have to check because I know i have on object but i don't know if i have on file itself – Adi Sep 05 '18 at 09:25
  • At OS level it doesn't matter what permissions *you* have; the `oracle` account has to be able to see the files. – Alex Poole Sep 05 '18 at 09:26
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/179452/discussion-between-adi-c-n-and-alex-poole). – Adi Sep 05 '18 at 09:27
  • From the chat, it looks like the directory is `\\servername\folder`, which is [a UNC path](https://en.wikipedia.org/wiki/Path_(computing)#Uniform_Naming_Convention). Have a look at [MoS Doc ID 1312842.1](https://support.oracle.com/epmos/faces/DocContentDisplay?id=1312842.1). (or [here](https://support.oracle.com/knowledge/Oracle%20Database%20Products/1312842_1.html) for a summary if you don't have MoS access) That could be the issue here? – Alex Poole Sep 05 '18 at 09:58
  • I will take a look to this . Also tried with path 'C:\temp' and it was the same. I'll take now a look, thank you . – Adi Sep 05 '18 at 10:04

0 Answers0