Oracle Database Discussions

Hi.

10g, RH Linux

What am I doing wrong? Do I need to add XMLFILES as an env var to my Linux user .env file?

mkdir XMLFILES
chmod 777 XMLFILES
cp relay.xml XMLFILES
chmod 777 relay.xml

CREATE DIRECTORY XMLFILES AS '/oradata/dbr/action_data/xmlfiles';

GRANT read write ON DIRECTORY XMLFILES TO db_usr;

CREATE TABLE xml_documents (
docname VARCHAR2(200) PRIMARY KEY,
xmldoc CLOB,
timestamp DATE
);

create or replace
PROCEDURE insertXmlFile( dir VARCHAR2,
file VARCHAR2,
name VARCHAR2 := NULL) IS
theBFile BFILE;
theCLob CLOB;
theDocName VARCHAR2(200) := NVL(name,file);
BEGIN
-- (1) Insert a new row into xml_documents with an empty CLOB, and
-- (2) Retrieve the empty CLOB into a variable with RETURNING..INTO

INSERT INTO xml_documents(docname,xmldoc) VALUES(theDocName,empty_clob( ))
RETURNING xmldoc INTO theCLob;

-- (3) Get a BFile handle to the external file
theBFile := BFileName(dir, file);

-- (4) Open the file
dbms_lob.fileOpen(theBFile);

-- (5) Copy the contents of the BFile into the empty CLOB
dbms_lob.loadFromFile(dest_lob => theCLob,
src_lob => theBFile,
amount => dbms_lob.getLength(theBFile));

-- (6) Close the file and commit
dbms_lob.fileClose(theBFile);
COMMIT;
end;

EXEC insertXmlFile('XMLFILES','relay.xml')

Error starting at line 1 in command:
EXEC insertXmlFile('XMLFILES','relay.xml')
Error report:
ORA-22288: file or LOB operation FILEOPEN failed
Permission denied
ORA-06512: at "SYS.DBMS_LOB", line 523
ORA-06512: at "DB_USR.INSERTXMLFILE", line 18
ORA-06512: at line 1
22288. 00000 - "file or LOB operation %s failed\n%s"
*Cause: The operation attempted on the file or LOB failed.
*Action: See the next error message in the error stack for more detailed
information. Also, verify that the file or LOB exists and that
the necessary privileges are set for the specified operation. If
the error still persists, report the error to the DBA.

Thanks