Hello Tom,
it took some time to answer because I wanted to test a bit with different character encodings. Mostly because I have a book where the author (a Mr. Kyte) says not to trust an authority without proof. Therefore I set up a database with AL32UTF8 and loaded a textfile with some exotic characters in three different formats.
Clear text
Testdatei äöü? ¿¿¿¿¿¿¿¿
(Maybe the Ask-Tom does not support UTF-8 as the preview shows only questionmarks)
UTF-8 (HEX)
FF EE 54 00 65 00 73 00 74 00 64 00 61 00 74 00
65 00 69 00 20 00 E4 00 F6 00 FC 00 3F 00 20 00
52 01 A3 03 CE 03 97 04 39 06 DE 06 DC FB FC FE
UTF-16 Big Endian without BOM (HEX)
00 54 00 65 00 73 00 74 00 64 00 61 00 74 00 65
00 69 00 20 00 E4 00 F6 00 FC 00 3F 00 20 01 52
03 A3 03 CE 04 97 06 39 06 DE FB DC FE FC
Unicode ASCII Escapes
Testdatei äöü? \u0152\u03A3\u03CE\u0497\u0639\u06DE\uFBDC\uFEFC
CREATE TABLE t_clob(
file_name VARCHAR2(200)
,file_data CLOB
);
SQL> SELECT file_name
2 ,DUMP(TO_CHAR(file_data)) dump
3 FROM t_clob;
FILE_NAME
--------------------------------------------------------------------------------
DUMP
--------------------------------------------------------------------------------
test_UTF-8.txt
Typ=1 Len=36: 84,101,115,116,100,97,116,101,105,32,195,164,195,182,195,188,63,32
,197,146,206,163,207,142,210,151,216,185,219,158,239,175,156,239,187,188
test_UTF-16_BigEndian_ohneBOM.txt
Typ=1 Len=57: 0,84,0,101,0,115,0,116,0,100,0,97,0,116,0,101,0,105,0,32,0,239,191
,189,0,239,191,189,0,63,0,32,1,82,3,239,191,189,3,239,191,189,239,191,189,6,57,6
,239,191,189,239,191,189,239,191,189
test_Unicode_ASCII-Escaped.txt
Typ=1 Len=63: 84,101,115,116,100,97,116,101,105,32,239,191,189,63,32,92,117,48,4
9,53,50,92,117,48,51,65,51,92,117,48,51,67,69,92,117,48,52,57,55,92,117,48,54,51
,57,92,117,48,54,68,69,92,117,70,66,68,67,92,117,70,69,70,67
DECLARE
v_clob CLOB;
v_filename t_clob.file_name%TYPE;
v_fileHandle utl_file.FILE_TYPE;
BEGIN
FOR r IN (
SELECT file_name
,file_data
FROM t_clob
)
LOOP
dbms_output.put_line(r.file_name);
v_fileHandle := utl_file.fopen ('DOC', 'new_'||r.file_name, 'wb', 32767);
utl_file.put_raw(v_fileHandle, utl_raw.cast_to_raw(v_clob));
END LOOP;
END;
/
The resulting files are byte by byte identically to the original files.
Not a proof for every situation but I think it shows, that put_raw does not change the content of the file.
Regards
Marcus