0

I am running into a scenario like when I am writing a XML file to a folder using PL/SQL it works, whereas when writing using stored procedure it fails and throwing an error as below

ORA-29289: directory access denied
ORA-06512: at "SYS.UTL_FILE", line 41
ORA-06512: at "SYS.UTL_FILE", line 478
ORA-06512: at "XDB.DBMS_XSLPROCESSOR", line 317
ORA-06512: at "TESTPROC", line 5
ORA-06512: at line 1

PL/SQL

declare
l_xmldoc clob;
begin
select XMLElement("dateval", sysdate).getClobVal() into l_xmldoc from dual;
dbms_xslprocessor.clob2file(l_xmldoc, 'XML_EDI_FILES', 'file1.xml', nls_charset_id('UTF8'));
end;

SP

create or replace procedure testproc is
l_xmldoc clob;
begin
select XMLElement("dateval", sysdate).getClobVal() into l_xmldoc from dual;
dbms_xslprocessor.clob2file(l_xmldoc, 'XML_EDI_FILES', 'file1.xml', nls_charset_id('UTF8'));
end;

I am sure that all the privileges are granted to the directory that I am writing to and that is why it works while using PL/SQL I believe.

Any help on this is much appreciated.

Thanks in Advance

Deepan Maheswaran
  • 73
  • 1
  • 13

2 Answers2

0

The error disappeared after grant of read, write privileges from "sys" user to "user1"

However, my question is that the directory was created from user "user1", so I assume that all the privileges are granted to that directory by default. So why should it throw an exception when executing procedure? when it works with PL/SQL

Deepan Maheswaran
  • 73
  • 1
  • 13
0

I know that this question was made sometime ago, but, I think I can help a little.

As @jose mentioned in his answer, that he created the directory on Oracle using the user1, although, when you call then dbms_xslprocessor.clob2file it uses the SYS.UTL_FILE PL/SQL Package internally to save the file on disc.

Take a look if the user1 has the permission to execute the SYS.UTL_FILE.

You can find more about SYS.UTL_FILE here.

There's also a similar question here.

Community
  • 1
  • 1
aledpardo
  • 761
  • 9
  • 19