CODEWITHSUNDEEP
oracle-databaseoracle-sqldeveloper
Alok Ranjan Swain
Alok Ranjan Swain

Reputation: 119

converting blob data column to varchar type for the whole table

I have a table with blob column in it. The blob data is of char type of length 8, I want to convert it to varchar of 8 bit length for the whole table (either by redefining the table structure or casting). However I don't want to lose any data previously stored in it. I am using oracle-sql-developer.enter image description here enter image description here

I want to permanently change the data type (not for only select statements). Any help is highly appreciated.

Upvotes: 0

Views: 2415

Answers (1)

Roberto Hernandez
Roberto Hernandez

Reputation: 8518

Taking in consideration that BLOB should not be used for this purpose, one way to do it is to create a function to convert the blob into a clob ( which is a character string ).

To be able to insert a string into a BLOB column, I need to use utl_cast_to_raw

Let me show you

SQL> create table test_blob ( c1 number, c2 blob ) ;

Table created.

SQL> insert into test_blob values ( 1 , utl_cast_to_raw('AAAABBBB') ) ;

1 row created.

SQL> commit ;

Commit complete.

SQL> select * from test_blob ;

        C1
----------
C2
--------------------------------------------------------------------------------
         1
4141414142424242

Now, we create a function to convert the BLOB into a CLOB

create or replace function blob_to_char (b blob)
return clob is
  v_clob    clob;
  n         number;
  v_start   pls_integer := 1;
  v_buffer  pls_integer := 32767;
  v_varchar varchar2(32767);
begin
  if (b is null) 
  then
    return null;
  end if;
  if (dbms_lob.getlength(b)=0) 
  then
    return empty_clob();
  end if;
  dbms_lob.createtemporary(v_clob,true);
  for i in 1..ceil(dbms_lob.getlength(b) / v_buffer)
  loop
    v_varchar := utl_raw.cast_to_varchar2(dbms_lob.substr(b, v_buffer, v_start));
    dbms_lob.writeappend(v_clob, length(v_varchar), v_varchar);
    v_start := v_start + v_buffer;
  end loop;
RETURN v_clob;
end blob_to_char;
/

Once we have the function, let's check it

SQL> select blob_to_char(c2) from test_blob ;

BLOB_TO_CHAR(C2)
--------------------------------------------------------------------------------
AAAABBBB

Now, we add a new column and we update the new column as varchar with the blob source column

SQL> alter table test_blob add c3 varchar2(8) ;

Table altered.

SQL> update test_blob set c3 = blob_to_char(c2) ;

1 row updated.

SQL> select * from test_blob ;

        C1
----------
C2
--------------------------------------------------------------------------------
C3
----------------
         1
4141414142424242
AAAABBBB

Upvotes: 1

Related Questions