Hi gandolf989,

Thanks for your reply!

Unfortunately, I have to maintain poor legacy code.
The application takes text files in CLOBs and with poor and junky pl/sql code transfer the data into a table with BLOB.
I want to change this junky code.
I want to upload directly text files into the database.
I found how to upload text files into CLOB in the database.
In addition ,I found in Metalink a procedure that knows to convert CLOB to BLOB, but when I execute it, I've got an internal error.

The procedure code is:

SQL> create or replace procedure clob_to_blob as
2 c_lob clob;
3 buffer VARCHAR2(32000);
4 g_length number;
5 begin
6 select XML_FILE into c_lob from test_clob;
7 g_length := dbms_lob.getlength(c_lob);
8 dbms_lob.read(c_lob,g_length,1,buffer);
9 insert into test_blob values(buffer);
10 commit;
11 end;
12 /

SQL> exec clob_to_blob;
BEGIN clob_to_blob; END;

*
ERROR at line 1:
ORA-01465: invalid hex number
ORA-06512: at "BCC65.CLOB_TO_BLOB", line 10
ORA-06512: at line 1


The application need to treat text files in BLOB only.
Is there a way to upload text files directly into BLOB and not uploading to CLOB and then concerting to BLOB?

Thanks in advance,
Nir