How to convert CLOB to BLOB?

[nir_s]

Hi falks,

I need to convert huge text files to BLOB or converting CLOB to BLOB.
Does anyone know a good way to do it?

Regards,
Nir

[gandolf989]

Hi Nir_s,

Have you looked at the DBMS_LOB package. There is no direct conversion, but you might be able to write some PL/SQL code to do the conversion. i.e. read the clob one chunk at a time and write it to the blob. Why are you converting the data anyway? If it is character data, you should keep it as a clob.

[nir_s]

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

[nir_s]

Hi,

I fond a way to upload text files into BLOB:

CREATE OR REPLACE PROCEDURE Load_BLOB_From_TXT_File
IS

dest_blob BLOB;
src_blob BFILE := BFILENAME('EXAMPLE_LOB_DIR', 'sui_treat_codes_data.txt');
dst_offset number := 1 ;
src_offset number := 1 ;
lang_ctx number := DBMS_LOB.DEFAULT_LANG_CTX;
warning number;

BEGIN

DBMS_OUTPUT.ENABLE(100000);

-- -----------------------------------------------------------------------
-- THE FOLLOWING BLOCK OF CODE WILL ATTEMPT TO INSERT / WRITE THE CONTENTS
-- OF AN TXT FILE TO A CLOB COLUMN. IN THIS CASE, I WILL USE THE NEW
-- DBMS_LOB.LoadCLOBFromFile() API WHICH *DOES* SUPPORT MULTI-BYTE
-- CHARACTER SET DATA. IF YOU ARE NOT USING ORACLE 9iR2 AND/OR DO NOT NEED
-- TO SUPPORT LOADING TO A MULTI-BYTE CHARACTER SET DATABASE, USE THE
-- FOLLOWING FOR LOADING FROM A FILE:
--
-- DBMS_LOB.LoadFromFile(
-- DEST_LOB => dest_clob
-- , SRC_LOB => src_clob
-- , AMOUNT => DBMS_LOB.GETLENGTH(src_clob)
-- );
--
-- -----------------------------------------------------------------------

INSERT INTO test_txt_blob(id, file_name, TXT_file, timestamp)
VALUES(1002, 'sui_treat_codes_data.txt', empty_blob(), sysdate)
RETURNING TXT_file INTO dest_blob;


-- -------------------------------------
-- OPENING THE SOURCE BFILE IS MANDATORY
-- -------------------------------------
DBMS_LOB.OPEN(src_blob, DBMS_LOB.LOB_READONLY);

DBMS_LOB.LoadBLOBFromFile(
DEST_LOB => dest_blob
, SRC_BFILE => src_blob
, AMOUNT => DBMS_LOB.GETLENGTH(src_blob)
, DEST_OFFSET => dst_offset
, SRC_OFFSET => src_offset
);

DBMS_LOB.CLOSE(src_blob);

COMMIT;

DBMS_OUTPUT.PUT_LINE('Loaded TXT File using DBMS_LOB.LoadBLOBFromFile: (ID=1002).');

END;
/


Regards,
Nir