ORA-22285 for DBMS_XSLPROCESSOR.CLOB2FILE-CodePudding

I am trying to export clob data to a json file in Oracle 19c: *

Declare
   Cursor c_emp_files
      is
    SELECT id, clob_data        
    FROM table;        
Begin
   for c in c_emp_files loop
  
    DBMS_XSLPROCESSOR.CLOB2FILE(c.clob_data,'c:\temp\','out.json');
    
   end loop;
End;

However, it failed with following error:

ORA-22285: non-existent directory or file FILEOPEN operation
ORA-06512: at "SYS.DBMS_LOB", line 1481 
ORA-06512: at "XDB.DBMS_XSLPROCERSSOR", line 117

Any suggestion is welcome!

CodePudding user response：

The "flocation" argument to CLOB2FILE is not the name of a directory on your disk - it's the name of a DIRECTORY object in the database. You need to use a CREATE DIRECTORY statement to create the DIRECTORY object in the database which "points to" the directory on the disk, and then you can use it (assuming your user has the appropriate permissions).

Note that the directory on disk must be on the machine where the database server is running, so if you're connecting to a remote database you cannot write the data to your local machine. If your database server is running on your local machine you'll be fine.

Also, if your database is running on Oracle v12 or later you should call DBMS_LOB.CLOB2FILE rather than using the deprecated version of CLOB2FILE in XDB.DBMS_XSLPROCESSOR.