Hello Experts
Database version 19c.
Facing an issue in SQL Plus due to special characters in file name.
The same query which fails below in SQL Plus, works well in SQL Developer.
Both the files are present in directory used in SQL query.
-rwxr-xr-x 1 appt apps 2311 Nov 29 02:19 Rēz.xml
-rwxr-xr-x 1 appt apps 2311 Nov 29 02:20 Rez.xml
SQL>SELECT xmltype(bfilename('DIR_BARMAN_INV','Rēz.xml'),nls_charset_id('AL32UTF8')) FROM DUAL;
ERROR:
ORA-22288: file or LOB operation FILEOPEN failed
No such file or directory
ORA-06512: at "SYS.XMLTYPE", line 296
ORA-06512: at line 1
no rows selected
Elapsed: 00:00:00.89
SQL>SELECT xmltype(bfilename('DIR_BARMAN_INV','Rez.xml'),nls_charset_id('AL32UTF8')) FROM DUAL;
XMLTYPE(BFILENAME('DIR_BARMAN_INV','REZ.XML'),NLS_CHARSET_ID('AL32UTF8'))
--------------------------------------------------------------------------------
<?xml version="1.0" encoding="WINDOWS-1252"?>
<dataroot>
<tjDocument Version="
Elapsed: 00:00:01.10
SQL>
These files I am getting from one sftp server and need to load data from xml into Oracle Table.
One solution I can think of is renaming the files while copying from sftp server and create a mapping between original name and new name for reference.
However is there any solution to use the file name as-it-is or some function which replaces special characters with some meaningful values.
File names are not constant. It can be any name only constant value is file extension which is '.xml'
Regards
Arun
Edit: Added Correct image from SQL Developer.