DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: How to convert CLOB to BLOB?

  1. #1
    Join Date
    Dec 2001
    Location
    Tel-Aviv,Israel
    Posts
    233

    How to convert CLOB to BLOB?

    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

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    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.

  3. #3
    Join Date
    Dec 2001
    Location
    Tel-Aviv,Israel
    Posts
    233
    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

  4. #4
    Join Date
    Dec 2001
    Location
    Tel-Aviv,Israel
    Posts
    233
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width