4 Replies Latest reply on May 8, 2007 4:19 PM by 576498

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

      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 .
        • 1. Re: converting datatype from Varchar2 to BLOB ...how..???
          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..???
            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..???
              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..???

                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