Results 1 to 10 of 10

Thread: CLOB to BLOB

  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: CLOB to BLOB

    Has anyone written PL/SQL which converts a CLOB into a BLOB? We're designing an external ColdFusion application which saves PDF files into the database, and ColdFusion will only save CLOB files to Oracle. We're also building an internal Oracle Forms application which needs to save documents (if they're mailed in we scan them), as well as retrieve the docs - whether they're saved via ColdFusion or Oracle Forms.

    The problem is that with Oracle Forms, the Webutil function DB_TO_CLIENT() only works with BLOB fields.

    So, we've chosed to tackle the problem of converting CLOB's to BLOB's, for those docs coming in from ColdFusion. Does anyone have some sample code, or suggestions on an approach?

    Thanks,
    Chuck

  2. #2
    Join Date
    Dec 2003
    Posts
    1,074
    I found this online

    Code:
    create or replace function clob_to_blob (p_clob_in in clob)
    return blob
    is
    v_blob blob;
    v_offset integer;
    v_buffer_varchar varchar2(32000);
    v_buffer_raw raw(32000);
    v_buffer_size binary_integer := 32000;
    begin
    --
      if p_clob_in is null then
        return null;
      end if;
    -- 
      DBMS_LOB.CREATETEMPORARY(v_blob, TRUE);
      v_offset := 1;
      FOR i IN 1..CEIL(DBMS_LOB.GETLENGTH(p_clob_in) / v_buffer_size)
      loop
        dbms_lob.read(p_clob_in, v_buffer_size, v_offset, v_buffer_varchar);
        v_buffer_raw := utl_raw.cast_to_raw(v_buffer_varchar);
        dbms_lob.writeappend(v_blob, utl_raw.length(v_buffer_raw), v_buffer_raw);
        v_offset := v_offset + v_buffer_size;
      end loop;
      return v_blob;
    end clob_to_blob;
    --=cf

  3. #3
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by chuck_forbes
    Has anyone written PL/SQL which converts a CLOB into a BLOB?
    Yes Oracle did: http://download.oracle.com/docs/cd/B...htm#sthref3637

  4. #4
    Join Date
    Dec 2003
    Posts
    1,074
    Cool! So I tried making a function out of this, so I could use it in an SQL statement

    Code:
    create or replace function CLOB_TO_BLOB (p_clob CLOB) return BLOB
    as
     l_blob          blob;
     l_dest_offset   integer := 1;
     l_source_offset integer := 1;
     l_lang_context  integer := DBMS_LOB.DEFAULT_LANG_CTX;
     l_warning       integer := DBMS_LOB.WARN_INCONVERTIBLE_CHAR;
    BEGIN
      DBMS_LOB.CONVERTTOBLOB
      (
       dest_lob    =>l_blob,
       src_clob    =>p_clob,
       amount      =>DBMS_LOB.LOBMAXSIZE,
       dest_offset =>l_dest_offset,
       src_offset  =>l_source_offset,
       blob_csid   =>DBMS_LOB.DEFAULT_CSID,
       lang_context=>l_lang_context,
       warning     =>l_warning
      );
      return l_blob;
    END;
    But when I try pulling one record out of a table

    Code:
    select clob_to_blob (document) from efl_e_docs where tk = 81;
    I get

    Code:
    ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified: ORA-22275
    I'm not sure how to debug this, since I can't step into DBMS_LOB. Does something stand out to you?

  5. #5
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Your function works on my 10.2 Installation.

    What's the definition of the table efl_e_docs. Especially what is the datatype of the column document?

    Where/how do you run the statement (Programming language, IDE, ..)

  6. #6
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Are you sure you won't ever need to index these documents ?

    You can index CLOB fields with Oracle Text (including PDFs), but you won't be able to do so anymore with a BLOB field (BLOBs are just bytes ), so think carefully about it before converting.

    Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  7. #7
    Join Date
    Dec 2003
    Posts
    1,074
    We won't need to index them, just retrieve the entire document associated with a particular account.

    The problem turned out that I didn't have the CREATETEMPORARY procedure included:

    Code:
    CREATE OR REPLACE function FORBESC.CLOB_TO_BLOB (p_clob CLOB) return BLOB
    as
     l_blob          blob;
     l_dest_offset   integer := 1;
     l_source_offset integer := 1;
     l_lang_context  integer := DBMS_LOB.DEFAULT_LANG_CTX;
     l_warning       integer := DBMS_LOB.WARN_INCONVERTIBLE_CHAR;
    BEGIN
      DBMS_LOB.CREATETEMPORARY(l_blob, TRUE);
      DBMS_LOB.CONVERTTOBLOB
      (
       dest_lob    =>l_blob,
       src_clob    =>p_clob,
       amount      =>DBMS_LOB.LOBMAXSIZE,
       dest_offset =>l_dest_offset,
       src_offset  =>l_source_offset,
       blob_csid   =>DBMS_LOB.DEFAULT_CSID,
       lang_context=>l_lang_context,
       warning     =>l_warning
      );
      return l_blob;
    END;
    One of our developers thinks we have to take this a step further, which is in unfamiliar territory for me. I'll post back if we have to change anything:

    "The current clob - > blob function won't work because it doesn't deal with decoding the clob data. You'd get gibberish back that is 33% too large.

    Because the only built-in functions that know about base64 encoding work on RAW's instead of LOB's, I believe we need a function which would chunk it, decode the chunk and stuff the decoded binary data into the blob."

    --=Chuck

  8. #8
    Join Date
    Dec 2003
    Posts
    1,074
    So it turns out that we did have to decode the CLOB from BASE64 before converting it to a BLOB. In case it helps someone else:

    Code:
    CREATE OR REPLACE function FORBESC.BASE64CLOB_TO_BLOB (p_clob_in in clob)
    return blob
    is
    
    -- v_buffer_varchar and v_buffer_size are a multiple of 4
    -- as the data coming in from the CLOB field is Base64 encoded,
    -- which means that each 3 bytes from the original file have
    -- been converted to 4 bytes
    
    v_blob blob;
    v_offset integer;
    v_buffer_varchar varchar2(32000);
    v_buffer_raw raw(32000);
    v_buffer_size binary_integer := 32000;
    begin
    --
      if p_clob_in is null then
        return null;
      end if;
    -- 
      DBMS_LOB.CREATETEMPORARY(v_blob, TRUE);
      v_offset := 1;
      FOR i IN 1..CEIL(DBMS_LOB.GETLENGTH(p_clob_in) / v_buffer_size)
      loop
        dbms_lob.read(p_clob_in, v_buffer_size, v_offset, v_buffer_varchar);
        v_buffer_raw := utl_encode.base64_decode(utl_raw.cast_to_raw(v_buffer_varchar));
        dbms_lob.writeappend(v_blob, utl_raw.length(v_buffer_raw), v_buffer_raw);
        v_offset := v_offset + v_buffer_size;
      end loop;
      return v_blob;
    end BASE64CLOB_TO_BLOB;
    /

  9. #9
    Join Date
    Dec 2007
    Posts
    1
    You can always use dbms_lob.converttoblob or convertot clob. This is if uoe are having Oracle 10g or above.

  10. #10
    Join Date
    Dec 2003
    Posts
    1,074
    If you look at some of the earlier code samples, I did use DBMS_LOB successfully. But, since we needed to decode the CLOB from BASE64, we had to take one of the more manual approaches. --=cf

Posting Permissions

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