This discussion is archived
4 Replies Latest reply: May 8, 2007 9:19 AM by 576498 RSS

converting datatype from Varchar2 to BLOB ...how..???

573154 Newbie
Currently Being Moderated
Hi i have to convert the datatype of a varchar2 column in one of my tables to BLOB...and i also have to preserve the data existing in the column in the process.

Please guide .
Thanks.
  • 1. Re: converting datatype from Varchar2 to BLOB ...how..???
    520094 Newbie
    Currently Being Moderated
    I think You may convert it.
    I am going to mention a sql statement.
    I have worked on Blob and Column's datatype changing.
    But have never ever experience to change a column into Blob.

    --Now write me have you tried it?

    Alter Table Table_Name
    modify Col_varchar Blob;

    Be careful when you are using Advanced datatype like Blob you can not select all table. OR You can not run below mention sql statement.

    Select[b] * From Table_Name;

    You may run in this scenario
    Select count(*)
    From Table_Name
    Where Col_Blob is not null;

    Waiting for your response.
  • 2. Re: converting datatype from Varchar2 to BLOB ...how..???
    520094 Newbie
    Currently Being Moderated
    I would say even recomend to Add a New column in this table.

    --Add New column in a table.
    Alter Table Table_Name
    Add New_Col Blob
    --


    One more important thing, if you want to convert data type of a column Then "You CAN NOT preserve your data in this scenario.
    Before running below statement

    --Convert data type of a column.
    Alter Table Table_Name
    modify Col_varchar Blob;

    Your column that has varchar2 datatype MUST be empty for conversion into Blob.

    Hope this helps.
  • 3. Re: converting datatype from Varchar2 to BLOB ...how..???
    549930 Newbie
    Currently Being Moderated
    first of all, I don't think that BLOB and VARCHAR2 are compatible, logically that is. BLOB is binary and VARCHAR2 is mere text.

    The way I'd do it is to have all the VARCHAR2 saved into text files and then stored in BLOB. Here is a hint:

    let's call your VARCHAR2 column 'X'
    1- create a new column in the table of type BLOB and call it 'Y'.
    2- using pl/sql text I/O, save each VARCHAR2 in a text file using the primary key of the table as file name.
    3- upload each file back to the table in it's corresponding record in column 'Y'.
    4- update the table setting 'X' in each table to NULL.
    5- alter table to delete column 'X'.
    6- alter table to rename 'Y' ---> 'X'

    finally, before doing any of these steps make sure you have enough disk space and that you really know how to deal with BLOB's as they need special insert and update commands.

    hope that helped

    Nasser Asiri
  • 4. Re: converting datatype from Varchar2 to BLOB ...how..???
    576498 Newbie
    Currently Being Moderated
    Hi

    i read what you say and i found it interessant for me because i have the same problem

    Si i hope if you can, to help so mine consisting of
    first i have an image (blob) that i can affich here but i would like will be converted to text for using this text as temporary file which can be shown when i want

    and thank you

    Bouhlel Fouad