Results 1 to 3 of 3

Thread: Text to BLOB

  1. #1
    Join Date
    Dec 2010
    Posts
    4

    Question Unanswered: Text to BLOB

    Hi all,
    I need to convert a text to a blob data type and insert in the blob field in a table.
    can any body please help me with the query for this.


    Thanks in advance

  2. #2
    Join Date
    Jul 2004
    Posts
    3
    Quote Originally Posted by Mabel View Post
    Hi all,
    I need to convert a text to a blob data type and insert in the blob field in a table.
    can any body please help me with the query for this.


    Thanks in advance
    This is for Oracle... but the concepts may be similar


    Code:
    CLOB to BLOB
    
    
    CREATE OR REPLACE FUNCTION clob2blob (
       p_clob      IN       CLOB DEFAULT EMPTY_CLOB (),
       p_success   OUT      VARCHAR2
    )
       RETURN BLOB
    IS
    -- transforming CLOB a BLOB
       bdoc           BLOB             := EMPTY_BLOB ();
       cdoc           CLOB             := p_clob;
       document       CLOB             := EMPTY_CLOB ();
       cdoc_size      NUMBER;
       BLOCK          NUMBER           := 10000;
       blockcount     NUMBER;
       rawbuff        RAW (32000);
       pos            NUMBER;
       charbuff       VARCHAR2 (32000);
       rawbuff_size   NUMBER;
    BEGIN
       DBMS_LOB.createtemporary (bdoc, TRUE, DBMS_LOB.CALL);
    -- recast the CLOB to a BLOB
       cdoc_size := DBMS_LOB.getlength (cdoc);
    
       IF BLOCK < cdoc_size
       THEN
          blockcount := ROUND ((cdoc_size / BLOCK) + 0.5);
       ELSE
          blockcount := 1;
       END IF;
    
       pos := 1;
    
       FOR i IN 1 .. blockcount
       LOOP
          DBMS_LOB.READ (cdoc, BLOCK, pos, charbuff);
          rawbuff := UTL_RAW.cast_to_raw (charbuff);
          rawbuff_size := UTL_RAW.LENGTH (rawbuff);
          DBMS_LOB.writeappend (bdoc, rawbuff_size, rawbuff);
          pos := pos + BLOCK;
       END LOOP;
    
       p_success := 'OK';
       RETURN bdoc;
    EXCEPTION
       WHEN OTHERS
       THEN
          p_success := 'ERROR::' || SQLCODE || ' -' || SQLERRM;
          RETURN EMPTY_BLOB ();
    END clob2blob;
    
    BLOB to CLOB
    
    CREATE OR REPLACE FUNCTION blob2clob (
       p_blob                     IN       BLOB DEFAULT EMPTY_BLOB ( )
    ,  p_success                  OUT      VARCHAR2 )
       RETURN CLOB
    IS
       bdoc                          BLOB := p_blob;
       cdoc                          CLOB := EMPTY_CLOB ( );
       document                      CLOB := EMPTY_CLOB ( );
       lob_id                        NUMBER;
       amount                        NUMBER;
       bdoc_size                     NUMBER;
       BLOCK                         NUMBER := 10000;
       blockcount                    NUMBER;
       rawbuff                       RAW ( 32000 );
       pos                           NUMBER;
       charbuff                      VARCHAR2 ( 32000 );
       charbuff_size                 NUMBER;
    BEGIN
       dbms_lob.createtemporary ( cdoc
    ,                             TRUE
    ,                             dbms_lob.CALL );
    -- recast the BLOB to a CLOB
       bdoc_size := dbms_lob.getlength ( bdoc );
    
       IF BLOCK < bdoc_size
       THEN
          blockcount := ROUND ( ( bdoc_size / BLOCK ) + 0.5 );
       ELSE
          blockcount := 1;
       END IF;
    
       pos := 1;
    
       FOR i IN 1 .. blockcount
       LOOP
          dbms_lob.READ ( bdoc
    ,                     BLOCK
    ,                     pos
    ,                     rawbuff );
          charbuff := utl_raw.cast_to_varchar2 ( rawbuff );
          charbuff_size := LENGTH ( charbuff );
          dbms_lob.writeappend ( cdoc
    ,                            charbuff_size
    ,                            charbuff );
          pos := pos + BLOCK;
       END LOOP;
    
       p_success := 'OK';
       RETURN cdoc;
    EXCEPTION
       WHEN OTHERS
       THEN
          p_success := 'ERROR::' || SQLCODE || ' -' || SQLERRM;
          RETURN EMPTY_CLOB ( );
    END blob2clob;

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    ...and this is for SQL:
    Code:
    create table #t(f1  image null)
    go
    insert  #t
    select  BulkColumn  from openrowset(bulk'C:\WINDOWS\explorer.exe',single_blob)  x
    go
    select * from #t
    go
    drop table  #t
    go
    
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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