3 Replies Latest reply: Oct 22, 2010 4:13 AM by Saubhik RSS

    Difference between BLOB and CLOB

    622930
      Hi,

      THe basic difference between BLOB and CLOB would be BLOB stands for Binary Large Objects and CLOB stands for Character Large Objects. Is there any other specific difference between BLOB and CLOB.

      Can i store XML data or file in BLOB or is it recommended to store in CLOB or XMLTYPE.


      I have a xml file stored in a column of type BLOB . Can i convert that BLOB content to CLOB. if so how.

      table structure goes something like this
      CREATE TABLE clarity_log
                   (
                                request_code NUMBER,
                                response_file BLOB ,
                                date_Created DATE DEFAULT SYSDATE
                   )
      In the above table we have xml data in response_file column

      Please advise

      Edited by: vine on Oct 21, 2010 10:50 PM
        • 1. Re: Difference between BLOB and CLOB
          Saubhik
          vine:Can i convert that BLOB content to CLOB. if so how.
          Yes. You can do that.
          Re: Convert blob to clob and again to blob!
          But, in your case, most probably you need not to do that. If you can give table structure along with sample data (DML) then it can be suggested better.
          • 2. Re: Difference between BLOB and CLOB
            622930
            THe basic table and data goes some t hing like this
            CREATE TABLE CLARITY_RESPONSE_LOG
            (
              REQUEST_CODE   NUMBER,
              RESPONSE_FILE  BLOB,
              DATE_CRATED    DATE                           NOT NULL,
              CREATED_BY     NUMBER                         NOT NULL,
              UPDATED_BY     NUMBER                         DEFAULT 1,
              DATE_UPDATED   VARCHAR2(20 BYTE)              DEFAULT SYSDATE
            )
            The xml content in the insert statement is very small because of some reason and cannot be made public and indeed we have a very big xml file stored in the BLOB column or Response_File column
            Insert into CLARITY_RESPONSE_LOG
               (REQUEST_CODE, RESPONSE_FILE, DATE_CRATED, CREATED_BY, UPDATED_BY, DATE_UPDATED)
             Values
               (5, '<?xml version="1.0" encoding="UTF-8"?><xml-response><phone-number>1212121212</phone-number></xml-response>', TO_DATE('09/23/2010 09:01:34', 'MM/DD/YYYY HH24:MI:SS'), 1, 1, '23-SEP-10');
            
            Insert into CLARITY_RESPONSE_LOG
               (REQUEST_CODE, RESPONSE_FILE, DATE_CRATED, CREATED_BY, UPDATED_BY, DATE_UPDATED)
             Values
               (6, '<?xml version="1.0" encoding="UTF-8"?><xml-response><phone-number>1212121212</phone-number></xml-response>', TO_DATE('09/23/2010 09:01:34', 'MM/DD/YYYY HH24:MI:SS'), 1, 1, '23-SEP-10');
            Insert into CLARITY_RESPONSE_LOG
               (REQUEST_CODE, RESPONSE_FILE, DATE_CRATED, CREATED_BY, UPDATED_BY, DATE_UPDATED)
             Values
               (7, '<?xml version="1.0" encoding="UTF-8"?><xml-response><phone-number>1212121212</phone-number></xml-response>', TO_DATE('09/23/2010 09:01:34', 'MM/DD/YYYY HH24:MI:SS'), 1, 1, '23-SEP-10');
            Insert into CLARITY_RESPONSE_LOG
               (REQUEST_CODE, RESPONSE_FILE, DATE_CRATED, CREATED_BY, UPDATED_BY, DATE_UPDATED)
             Values
               (8, '<?xml version="1.0" encoding="UTF-8"?><xml-response><phone-number>1212121212</phone-number></xml-response>', TO_DATE('09/23/2010 09:01:34', 'MM/DD/YYYY HH24:MI:SS'), 1, 1, '23-SEP-10');
            Insert into CLARITY_RESPONSE_LOG
               (REQUEST_CODE, RESPONSE_FILE, DATE_CRATED, CREATED_BY, UPDATED_BY, DATE_UPDATED)
             Values
               (9, '<?xml version="1.0" encoding="UTF-8"?><xml-response><phone-number>1212121212</phone-number></xml-response>', TO_DATE('09/23/2010 09:01:34', 'MM/DD/YYYY HH24:MI:SS'), 1, 1, '23-SEP-10');
            .




            Please advise

            Edited by: vine on Oct 22, 2010 1:54 AM
            • 3. Re: Difference between BLOB and CLOB
              Saubhik
              This may help...
              SQL> CREATE TABLE test_my_blob_clob(id INTEGER,blob_col BLOB,clob_col CLOB);
              
              Table created.
              
              SQL> DECLARE
                2    v_src_blob_locator BFILE := BFILENAME('TEST_DIR', 'blobtest.xml');
                3    v_amount_to_load   INTEGER := 4000;
                4    dest_lob_loc BLOB;
                5  BEGIN
                6    --Insert a empty row with id 1
                7    INSERT INTO test_my_blob_clob VALUES(1,EMPTY_BLOB(),EMPTY_CLOB())
                8     RETURNING BLOB_COL INTO dest_lob_loc;
                9    DBMS_LOB.open(v_src_blob_locator, DBMS_LOB.lob_readonly);
               10    v_amount_to_load := DBMS_LOB.getlength(v_src_blob_locator);
               11    DBMS_LOB.loadfromfile(dest_lob_loc, v_src_blob_locator, v_amount_to_load);
               12    DBMS_LOB.close(v_src_blob_locator);
               13    COMMIT;
               14  --id=1 is created with blobtest.xml populated in BLOB_COL and CLOB_COL is emp
               15  END;
               16  /
              
              PL/SQL procedure successfully completed.
              
              SQL> /* Updating the CLOB_COL from BLOB_COL using DBMS_LOB.CONVERTTOCLOB
              DOC>   for id=1 */
              SQL> DECLARE
                2    v_amount_to_load INTEGER;
                3    v_clob_locator   CLOB;
                4    v_blob_locator   BLOB;
                5    v_clob_offset    NUMBER := 1;
                6    v_blob_offset    NUMBER := 1;
                7    v_lang_context   NUMBER :=DBMS_LOB.DEFAULT_LANG_CTX;
                8    v_warning        NUMBER;
                9   
               10  BEGIN
               11    SELECT blob_col, clob_col
               12      INTO v_blob_locator, v_clob_locator
               13      FROM test_my_blob_clob
               14     WHERE id = 1
               15       FOR UPDATE;
               16    v_amount_to_load := DBMS_LOB.GETLENGTH(v_blob_locator);
               17   
               18    DBMS_LOB.CONVERTTOCLOB(v_clob_locator,
               19                           v_blob_locator,
               20                           v_amount_to_load,
               21                           v_clob_offset,
               22                           v_blob_offset,
               23                           0,
               24                           v_lang_context,
               25                           v_warning);
               26    COMMIT;
               27    
               28  /* So, Now for id=1, BLOB_COL is populated with blobtest.xml and CLOB_COL is
               29    populated with the converted CLOB from blobtest.xml!. 
               30  */
               31  END;
               32  /
              
              PL/SQL procedure successfully completed.
              
              SQL> SET long 2000000
              
              SQL> SELECT clob_col FROM test_my_blob_clob;
              
              CLOB_COL
              --------------------------------------------------------------------------------
              <?xml version="1.0" encoding="UTF-8"?><xml-response><phone-number>1212121212</ph
              one-number></xml-response>
              
              
              SQL>