Home » SQL & PL/SQL » SQL & PL/SQL » BFILENAME with filenames containing accents (Oracle Database, 11.1.0.7.0, Windows 32 bits)
BFILENAME with filenames containing accents [message #422709] Fri, 18 September 2009 08:17 Go to next message
brlav35
Messages: 4
Registered: September 2009
Location: QUEBEC
Junior Member
Hello

I need to load a bunch of files from a DIRECTORY under a windows environment and everything is going fine, but some of these files contains accented characters like 'é'.

I've done all the necessary checks (Directory access, file existence, etc.) and execute this script:

DECLARE
   l_bfile   BFILE;
BEGIN
   -- Non accented filename...
   l_bfile := BFILENAME ('DOCDIR', 'Alena.txt');
 
   IF DBMS_LOB.fileexists (l_bfile) = 1
   THEN
      DBMS_OUTPUT.put_line ('File exists.');
   ELSIF DBMS_LOB.fileexists (l_bfile) = 0
   THEN
      DBMS_OUTPUT.put_line ('File does not exist.');
   ELSE
      DBMS_OUTPUT.put_line ('Unable to test existence.');
   END IF;
 
   -- Accented filename...
   l_bfile := BFILENAME ('DOCDIR', 'Aléna.txt');
 
   IF DBMS_LOB.fileexists (l_bfile) = 1
   THEN
      DBMS_OUTPUT.put_line ('File exists.');
   ELSIF DBMS_LOB.fileexists (l_bfile) = 0
   THEN
      DBMS_OUTPUT.put_line ('File does not exist.');
   ELSE
      DBMS_OUTPUT.put_line ('Unable to test existence.');
   END IF;
END;


the script output is :
File exists.
File does not exist.


The two files are there, but I can't figure how to load files with accented characters in their filename.

Thanks in advance...
Re: BFILENAME with filenames containing accents [message #422713 is a reply to message #422709] Fri, 18 September 2009 08:30 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
I think, Q quote literals can help you.
Google for that.

regards,
Delna
Re: BFILENAME with filenames containing accents [message #422718 is a reply to message #422713] Fri, 18 September 2009 08:47 Go to previous messageGo to next message
brlav35
Messages: 4
Registered: September 2009
Location: QUEBEC
Junior Member
No luck Sad

DECLARE
   l_bfile BFILE;
BEGIN   
   -- Accented filename...
   l_bfile := BFILENAME ('DOCDIR', q'[Aléna.txt]');

   IF DBMS_LOB.fileexists (l_bfile) = 1
   THEN
      DBMS_OUTPUT.put_line ('File exists.');
   ELSIF DBMS_LOB.fileexists (l_bfile) = 0
   THEN
      DBMS_OUTPUT.put_line ('File does not exist.');
   ELSE
      DBMS_OUTPUT.put_line ('Unable to test existence.');
   END IF;
END;


Output:
File does not exist.


Re: BFILENAME with filenames containing accents [message #422827 is a reply to message #422718] Sun, 20 September 2009 03:07 Go to previous message
_jum
Messages: 508
Registered: February 2008
Senior Member
After building such a file, no problem to access the file (on the SERVER DIRECTORY) (ORACLE 10.2.0.1.0, WINDOWS)
SET SERVEROUTPUT ON SIZE 100000;

DECLARE
   l_bfile      DBMS_LOB.BFILE;
  
   fileHandler  UTL_FILE.FILE_TYPE;
   file_length  NUMBER;
   fexists      BOOLEAN;
   block_size   BINARY_INTEGER;

BEGIN
   -- Accented filename... test
   l_bfile := BFILENAME ('DC_DATA_DIR', 'aléna.txt');

   IF DBMS_LOB.fileexists (l_bfile) = 1
   THEN
      DBMS_OUTPUT.put_line ('File exists.');
   ELSIF DBMS_LOB.fileexists (l_bfile) = 0
   THEN
      DBMS_OUTPUT.put_line ('File does not exist.');
   ELSE
      DBMS_OUTPUT.put_line ('Unable to test existence.');
   END IF;

  	--build File with accented filename.
   fileHandler := UTL_FILE.FOPEN('DC_DATA_DIR' , 'aléna.txt' , 'w');

   UTL_FILE.PUT_LINE(fileHandler,'200812105879000026473156947416324265744393861862');
   UTL_FILE.PUT_LINE(fileHandler,'200812110009536106473100000006834265760373938618');	
   UTL_FILE.PUT_LINE(fileHandler,'200812125899536826633156947416914260000000061837');

   UTL_FILE.FCLOSE(fileHandler);
 
   UTL_FILE.FGETATTR('DC_DATA_DIR','aléna.txt', fexists, file_length,block_size);
 
   IF fexists THEN
     dbms_output.put_line('FILE length='||file_length||' BlockSize='||block_size);
   ELSE
     dbms_output.put_line('File does not exist');
   END IF;

   -- Accented filename... test again
   l_bfile := BFILENAME ('DC_DATA_DIR', 'aléna.txt');

   IF DBMS_LOB.fileexists (l_bfile) = 1
   THEN
      DBMS_OUTPUT.put_line ('File exists.');
   ELSIF DBMS_LOB.fileexists (l_bfile) = 0
   THEN
      DBMS_OUTPUT.put_line ('File does not exist.');
   ELSE
      DBMS_OUTPUT.put_line ('Unable to test existence.');
   END IF;
   
END;

Script output:
File does not exist.
FILE length=150 BlockSize=0
File exists.
PL/SQL procedure successfully completed.
Previous Topic: convert oracle to ms server 2005
Next Topic: Need help in sql query
Goto Forum:
  


Current Time: Sat Dec 03 08:16:40 CST 2016

Total time taken to generate the page: 0.08137 seconds