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
