How to write hex FF symbols into file from CLOB-type field using UTL_FILE?

Question

Do anybody know how to insert into table with fields type «clob» a data which after using utl_file will look in the result file like FF FF FF FF in hex-editor? I did the next (and a lot of other attempts):

INSERT INTO DATA.FILE_TAB
(A1 --CLOB type field
)
select 
LPAD(chr(to_number('FF', 'XX')), 22, chr(to_number('FF', 'XX'))) A1
from DUAL;

after inserting I use utl_file (put_line) to create a .dat file in the directory. When I open it, I saw anything but not needed chars. =( In hex-editor I need to see symbols FF FF FF FF FF FF like via the link.

Run the following query: `SELECT * FROM NLS_DATABASE_PARAMETERS` and tell me what it returns for `PARAMETER_NAME = 'NLS_CHARACTERSET'` and `PARAMETER_NAME = 'NLS_SAVED_NCHAR_CS'`. — Bob Jarvis - Слава Україні, Aug 21 '12 at 11:24
NLS_CHARACTERSET = AL32UTF8 and NLS_NCHAR_CHARACTERSET = AL16UTF16 — May12, Aug 21 '12 at 11:47
Note that if you change A1 to BLOB the following will work: `INSERT INTO DATA.FILE_TAB(A1) VALUES (LPAD(HEXTORAW('FF'), 22, HEXTORAW('FF')))`. — Bob Jarvis - Слава Україні, Aug 21 '12 at 11:47
LPAD(HEXTORAW('FF'), 22, HEXTORAW('FF')) doesn't work in my case (NLS_CHARACTERSET = AL32UTF8) — May12, Aug 21 '12 at 11:57
Given your character sets I'd expect this to work. I can't test it, though, as the databases I have available all use US7ASCII. It seems that you want to store binary data - could you convert A1 to be a BLOB instead of a CLOB? Also, as I noted above, `LPAD(HEXTORAW('FF'), 22, HEXTORAW('FF'))` only works if A1 is a `BLOB`. — Bob Jarvis - Слава Україні, Aug 21 '12 at 11:58
a cann't convert clob to blob because in this case i cann't conncatenate fields. i've tryied it. — May12, Aug 21 '12 at 12:10

score 0 · Answer 1 · answered Aug 21 '12 at 11:12

0

You can use UTL_RAW package for this purpose

insert into data.file_tab(a1)
values (lpad(utl_raw.cast_to_varchar2('FF'), 22, utl_raw.cast_to_varchar2('FF')));

answered Aug 21 '12 at 11:12

Marcin Wroblewski

3,491
18
27

The results from your query appear to be dependent on the character set in use. On our database, with NLS_CHARACTERSET = 'US7ASCII', I get '7F7F7F...' - i.e. the high-order bit gets truncated. – Bob Jarvis - Слава Україні Aug 21 '12 at 11:29

How to write hex FF symbols into file from CLOB-type field using UTL_FILE?

1 Answers1