1

I would like to store an xml file in oracle database and I did the following :

create table MyTab (filename varchar(64), xml XMLType);

create or replace directory XML_DIR as 'C:\Users\XXX\XXX\Folder\xml';

insert into MyTab VALUES ('file.xml', xmltype(bfilename('XML_DIR','file.xml'),nls_charset_id('AL32UTF8')));

However i have this error : ORA-22288: file or LOB operation FILEOPEN failed, access denied

Can someone explain what is wrong, please?

vvvvv
  • 25,404
  • 19
  • 49
  • 81
Ahmadou Kassoum
  • 59
  • 7
  • You don't have rights for create table – Roland Dec 06 '22 at 23:44
  • @Roland when I create the table it works, the problem is when I execute my insert query – Ahmadou Kassoum Dec 06 '22 at 23:48
  • You don't have rights for insert. With Oracle you have grants for everything. Try to get grants for 'All' – Roland Dec 06 '22 at 23:52
  • And how can I solve the problem of access rights? I tried to change file.xml permission (ex chmod o+r file.xml) with git bash but it did'nt work (i'm on windows os) – Ahmadou Kassoum Dec 06 '22 at 23:58
  • @Roland i connected with sysdba and i execute this query : Grant all on directory XML_DIR to MyUser; but i still get error – Ahmadou Kassoum Dec 07 '22 at 00:28
  • 1
    The account that the DB server runs under does not have access to your C:\Users folder, so it can't access the file there. – Ken White Dec 07 '22 at 02:33
  • 1
    @AhmedK Try right-clicking on the "xml" folder, select Properties, Security, Edit.., Add.., Advanced..., Find Now..., double-click on name like "ORA_OraDB19Home1_SVCACCTS" (will be different depending on how you installed Oracle, I think that "ORA_DBA" may also work for some versions), OK, OK, OK. – Jon Heller Dec 07 '22 at 03:04

1 Answers1

0

To give Oracle permission to use a directory on Windows:

  1. Open Windows Explorer
  2. Right-click on the relevant folder
  3. Click Properties
  4. Click Security
  5. Click Edit...
  6. Click Add...
  7. Click Advanced...
  8. Click Find Now...
  9. Double-click on a name like "ORA_OraDB19Home1_SVCACCTS" (this will be different depending on your version and on how you installed Oracle, and I think that "ORA_DBA" may also work for some versions)
  10. Click OK three times
Jon Heller
  • 34,999
  • 6
  • 74
  • 132