I'm trying to load a LOB file to a table and ORA-22288.

I fail on DBMS_LOB.FILEOPEN(src_clob);

What can be the cause?

The directory exists and file is located in the directory.

I do the following:

Connect as SYSTEM

Execute the following commands:

SQL>create or replace directory MY_DIR as 'C:\oracle\admin\MYDB\create\lob';

SQL>Grant all on directory MY_DIR to MYDBUSER;

Connect as MYDBUSER and call a procedure SQL>LOAD_LOB_FROM_FILE(10,'insert_details_view.xsl','XMLXSL_DATA_T','FILE_ID','LOB_FILE');

The procedure is:

CREATE OR REPLACE PROCEDURE LOAD_LOB_FROM_FILE(p_FileId NUMBER, p_FileName

VARCHAR2,p_TableName VARCHAR2, p_IDColumnName VARCHAR2, p_FileColoumnName VARCHAR2)

IS

dest_clob CLOB;

src_clob BFILE := BFILENAME('MY_DIR', p_FileName);

dest_length number;

str_query CLOB;

BEGIN

-- This procedure handles updates of all files in the databse - LOB, Json and XSL.

-- The procedure recieves dynamic parameters in order to work for all contexts and file types

str_query := 'SELECT ' || p_FileColoumnName || ' FROM ' || p_TableName || ' WHERE ' || p_IDColumnName || ' = ' || p_FileId || ' FOR UPDATE ';

EXECUTE IMMEDIATE str_query INTO dest_clob;

DBMS_LOB.FILEOPEN(src_clob);

-- It is necessary to clear the old clob before updating with the new one to prevent the file destruction.

dest_length := DBMS_LOB.GETLENGTH(dest_clob);

IF dest_length <> 0 THEN

DBMS_LOB.ERASE(dest_clob,dest_length,1);

END IF;

DBMS_LOB.LOADFROMFILE(dest_clob,src_clob,DBMS_LOB.GETLENGTH(src_clob));

str_query := 'UPDATE ' || p_TableName || ' SET ' || p_FileColoumnName || ' = ''' || dest_clob ||''' WHERE ' || p_IDColumnName || ' = ' || p_FileId;

EXECUTE IMMEDIATE str_query;

DBMS_LOB.FILECLOSE(src_clob);

COMMIT;

END;

/

Full error stack:

ERROR at line 1:

ORA-22288: file or LOB operation FILEOPEN failed

The filename, directory name, or volume label syntax is incorrect.

ORA-06512: at "SYS.DBMS_LOB", line 805

ORA-06512: at "VSU22.LOAD_LOB_FROM_FILE", line 16

ORA-06512: at line 3