Results 1 to 14 of 14
  1. #1
    Join Date
    Apr 2007
    Posts
    6

    Unanswered: changing column type from Varchar2 to BLOB

    Hi,

    i have a table with a certain column of type Varchar2 ...i have to change the type of this column to BLOB...and also preserve the data in the table.. .

    I cannot afford to change the varchar2 column to some other type..cos the input data could even be a huge XML file..so the BLOB type would have no limitation.

    Please help. Thanks.

  2. #2
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    A BLOB is a Binary LOB, which means that it is stored uninterpreted, "as-of" in the database, as a RAW column, whereas a CLOB is a Character LOB, which means that the text is interpreted (the character set is changed between client and server, it is full-text indexable...), as a VARCHAR2 column. As you are currently using a VARCHAR2 column, I think you should change to a CLOB column, not a BLOB one.

    LOBs in general are limited to 4 Gb, not exactly unlimited as you suggested .

    Now, concerning how to change the datatype, one solution would be :

    - Add a CLOB column to the table containing your current text data, with a new name
    - Update this table setting CLOB column = VARCHAR2 column
    - Drop the old column
    - Rename the new column to the old column's name

    For example :
    Code:
    rbaraer@Ora10g> CREATE TABLE test(textfield VARCHAR2(4000));
    
    Table created.
    
    rbaraer@Ora10g> INSERT INTO test(textfield) SELECT table_name FROM user_tables;
    
    9 rows created.
    
    rbaraer@Ora10g> SELECT * FROM test;
    
    TEXTFIELD
    -----------------------------------------------------------------------------------------------------------------------------------
    TESTCLOB
    TESTXML
    T
    TESTCHAR
    TABLEDEF
    TEST_TABLE
    DIALINGCODES
    TBL
    TEST
    
    9 rows selected.
    
    rbaraer@Ora10g> DESCRIBE test;
     Name                                                                    Null?    Type
     ----------------------------------------------------------------------- -------- -------------------------------------------------
     TEXTFIELD                                                                        VARCHAR2(4000)
    
    rbaraer@Ora10g> ALTER TABLE test ADD clobfield CLOB;
    
    Table altered.
    
    rbaraer@Ora10g> DESCRIBE test;
     Name                                                                    Null?    Type
     ----------------------------------------------------------------------- -------- -------------------------------------------------
     TEXTFIELD                                                                        VARCHAR2(4000)
     CLOBFIELD                                                                        CLOB
    
    rbaraer@Ora10g> UPDATE test SET clobfield = textfield;
    
    9 rows updated.
    
    rbaraer@Ora10g> ALTER TABLE test DROP COLUMN textfield;
    
    Table altered.
    
    rbaraer@Ora10g> ALTER TABLE test RENAME COLUMN clobfield TO textfield;
    
    Table altered.
    
    rbaraer@Ora10g> SELECT * FROM test;
    
    TEXTFIELD
    --------------------------------------------------------------------------------
    TESTCLOB
    TESTXML
    T
    TESTCHAR
    TABLEDEF
    TEST_TABLE
    DIALINGCODES
    TBL
    TEST
    
    9 rows selected.
    
    rbaraer@Ora10g> DESCRIBE test;
     Name                                                                    Null?    Type
     ----------------------------------------------------------------------- -------- -------------------------------------------------
     TEXTFIELD                                                                        CLOB
    
    rbaraer@Ora10g>
    HTH & Regards,

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

  3. #3
    Join Date
    Apr 2007
    Posts
    6
    Hi,

    Thanks really for such an elaborated reply.

    and i would certainly use this way...if i cannot make the BLOB thing happen.
    Please let me know if its possible to use a BLOB in place of CLOB.

    Because we are supposed to use BLOB for this...is it feasible..?

    Thanks

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Unless you are storing something like an executable or a binary file like word or excel, you really want to use CLOB. Did your manager tell you why a blob is required? Both BLOB and CLOB have a maximum size of 4 gigabytes per row.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Apr 2007
    Posts
    6
    Hi Thanks i think i got it...convert the varchar2 column to CLOB the way u suggested ...then i can convert the CLOB to BLOB ...i found a procedure.

    wel i cant ask him why..!!..i am paid to shut up my mouth and work.. ..

  6. #6
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Yes you can use a BLOB if you think it is the best choice for you.

    In that case you must convert your current character data to binary (raw) data.

    For this, use UTL_RAW.CAST_TO_RAW.

    Code:
    rbaraer@Ora10g> drop table test;
    
    Table dropped.
    
    rbaraer@Ora10g> CREATE TABLE test(textfield VARCHAR2(4000));
    
    Table created.
    
    rbaraer@Ora10g> INSERT INTO test(textfield) SELECT table_name FROM user_tables;
    
    9 rows created.
    
    rbaraer@Ora10g> SELECT * FROM test;
    
    TEXTFIELD
    -----------------------------------------------------------------------------------------------------------------------------------
    TESTCLOB
    TESTXML
    T
    TESTCHAR
    TABLEDEF
    TEST_TABLE
    DIALINGCODES
    TBL
    TEST
    
    9 rows selected.
    
    rbaraer@Ora10g> DESCRIBE test;
     Name                                                                    Null?    Type
     ----------------------------------------------------------------------- -------- -------------------------------------------------
     TEXTFIELD                                                                        VARCHAR2(4000)
    
    rbaraer@Ora10g> ALTER TABLE test ADD blobfield BLOB;
    
    Table altered.
    
    rbaraer@Ora10g> DESCRIBE test;
     Name                                                                    Null?    Type
     ----------------------------------------------------------------------- -------- -------------------------------------------------
     TEXTFIELD                                                                        VARCHAR2(4000)
     BLOBFIELD                                                                        BLOB
    
    rbaraer@Ora10g> UPDATE test SET blobfield = UTL_RAW.CAST_TO_RAW(textfield);
    
    9 rows updated.
    
    rbaraer@Ora10g> ALTER TABLE test DROP COLUMN textfield;
    
    Table altered.
    
    rbaraer@Ora10g> ALTER TABLE test RENAME COLUMN blobfield TO textfield;
    
    Table altered.
    
    rbaraer@Ora10g> SELECT * FROM test;
    SP2-0678: Column or attribute type can not be displayed by SQL*Plus
    rbaraer@Ora10g> SELECT UTL_RAW.CAST_TO_VARCHAR2(textfield) FROM test;
    
    UTL_RAW.CAST_TO_VARCHAR2(TEXTFIELD)
    -----------------------------------------------------------------------------------------------------------------------------------
    TESTCLOB
    TESTXML
    T
    TESTCHAR
    TABLEDEF
    TEST_TABLE
    DIALINGCODES
    TBL
    TEST
    
    9 rows selected.
    
    rbaraer@Ora10g> DESCRIBE test;
     Name                                                                    Null?    Type
     ----------------------------------------------------------------------- -------- -------------------------------------------------
     TEXTFIELD                                                                        BLOB
    
    rbaraer@Ora10g>
    Notice that the BLOB data does NOT display in SQL*Plus. You have to convert it back to text data with UTL_RAW.CAST_TO_VARCHAR2 in order to display it.

    BTW, what makes you think you need a BLOB instead of a CLOB ? Both can store up to 4Gb. In general, BLOBs are used for pure binary data, such as images, and CLOBs for text data, such as long texts, XML files... As you spoke of XML files, I would strongly recommend a CLOB...

    HTH & Regards,

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

  7. #7
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by RBARAER
    BTW, what makes you think you need a BLOB instead of a CLOB?
    In one project we decided to use BLOBs for storing XML data as well. We received XML messages and were storing the raw information as received from the server this way. We wanted to make sure that no change to the data was applied due to character set conversions (the messages were used for tracking orders so this was an important issue). As the message could be transmitted with different character sets this was the only we saw back then.

    The biggest problem was reporting. It is nearly impossible (or at least very hard) to search inside those BLOBs for specific information.

    Additionally most of the SQL tools that you need during development cannot handle this situation very well. So there might be some problems during development and testing as well.

    If I had to do it again, I'd probably choose CLOBs now, accepting the minor changes hat might be applied due to characterset conversion.

  8. #8
    Join Date
    Apr 2007
    Posts
    6

    Hi

    Hi Thanks rabaraer..

    well actually we have some modifications and now in the same table for this field...we would have two kinds of data stored...one would be the XML files...and second would be the Hashtables..which would be now purely binary...hence it was decided to use blob for this field..

  9. #9
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Quote Originally Posted by @developer@
    Hi Thanks rabaraer..
    You're welcome.

    So you would keep VARCHAR2 (or use CLOB) for the XML file field, and use BLOB for the hash tables ? In this case it seems OK to me.

    Shammat, thanks for sharing your experience. I think we agree here, don't we ?

    Regards,

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

  10. #10
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by RBARAER
    Shammat, thanks for sharing your experience. I think we agree here, don't we?
    Yes we do
    I just wanted to point out that there might be good reasons to use BLOB for character data.

  11. #11
    Join Date
    Apr 2007
    Posts
    6
    Hi does anybdy have an idea about this :

    when i am inserting value into the blob...for which like i have written a function...i have to insert it separately for the blob field and separately for the rest of the fields..

    when i insert it for the blob field i am getting the Oracle exception -

    Exception Message - ORA-22920: row containing the LOB value is not locked
    java.sql.SQLException: ORA-22920: row containing the LOB value is not locked

    Also is there a way we can insert values into both types of columns at the same time..??

  12. #12
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Quote Originally Posted by @developer@
    Exception Message - ORA-22920: row containing the LOB value is not locked
    java.sql.SQLException: ORA-22920: row containing the LOB value is not locked

    Also is there a way we can insert values into both types of columns at the same time..??
    There are several ways to insert BLOBs, but the best one is the following one :

    - INSERT the new row with empty_blob() in the BLOB column (you can specify any other column here too) and use the RETURNING INTO clause to get the blob field (RETURNING blobfield INTO blobvariable). Using the RETURNING INTO clause with the blob field implicitly locks the inserted row, otherwise you would have had to do a SELECT FOR UPDATE on this row afterwards, which would affect performance.
    - open the BLOB in read/write mode
    - write data to the blob
    - close the blob, which releases the implicit lock on the row

    Of course I don't speak of a direct insert here (as another column) since this only works if data to be written to the BLOB is less than 2,000 bytes (as in a RAW column).

    HTH & Regards,

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

  13. #13
    Join Date
    Apr 2007
    Posts
    6
    hi OK ..i found it...its cos ...when u use Select to select the rows of blob...u have to use..."select for update" instead..thanks anyws.

  14. #14
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Quote Originally Posted by @developer@
    hi OK ..i found it...its cos ...when u use Select to select the rows of blob...u have to use..."select for update" instead..thanks anyws.
    Right, but as I told you on my previous post, this is not the best way .

    Furthermore, you need the FOR UPDATE only if you want to update the blob. If you just want to read it, a simple SELECT is sufficient.

    Regards,

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

Posting Permissions

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