CODEWITHSUNDEEP
oracle-databaseoracle11gblob
Naidu5
Naidu5

Reputation: 17

I needs to export Oracle BLOB column data to csv or text file

I needs to export Oracle BLOB column data to csv or text file.

select Blob1 from table it gives me in binary format.

Please give me the solution to export BLOB data to either csv or text file in normal readable format.

Upvotes: 0

Views: 3984

Answers (3)

Naidu5
Naidu5

Reputation: 17

Thanks Roberto it works like a charm. SQL> SET LONG 99999999 SQL> SELECT blob_to_char(blobcolum) from yourtable ;

Upvotes: 0

Roberto Hernandez
Roberto Hernandez

Reputation: 8528

Normally BLOB datatypes are used for storing binary objects, such as images, videos, etc. However, if you have a BLOB that contains characters instead of binary data, then you can do the following:

1.Creates a function to return 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;

2.Then you can use the function in any select statement to recover your blob as a character string. Obviously, I am assuming the BLOB is in reality a string.

select column1, column2 , blob_to_char(your_blob_column) from your table ;

Example

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

Table created.


SQL> insert into t values ( 1 , utl_raw.cast_to_raw('ppppppppppppppppppppppppppdoekkkkkkkkkkkkkkkkkkffj') ) ;

1 row created.

SQL> commit;

SQL> create or replace function blob_to_char
  2  ( b blob)
return clob is
  v_clob    clob;
  n         number;
  v_start   pls_integer := 1;
  v_buffer  pls_integer := 32767;
  v_varchar varchar2(327  3  67);
begin
  if (b is null)
  then
  4    5    6    7    8    9   10   11   12      return null;
  end if;
 13   14    if (dbms_lob.getlength(b)=0)
  then
    return empty_clob();
 15   16   17    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));
 18   19   20   21   22         dbms_lob.writeappend(v_clob, length(v_varchar), v_varchar);
        v_start := v_start + v_buffer;
  end loop;
RETURN v_clob;
 23   24   25   26  end;
 27  /

Function created.

SQL> select c1 , blob_to_char(c2) from t ;

        C1
----------
BLOB_TO_CHAR(C2)

--------------------------------------------------------------------------------
         1
ppppppppppppppppppppppppppdoekkkkkkkkkkkkkkkkkkffj

Nevertheless, is a very bad practice to store huge text or strings using BLOB, for that you must use always CLOB, which is was it's intended for.

Update

If your BLOB is a very big, try to increase the output by

 SQL> SET LONG 99999999
 SQL> SELECT blob_to_char(blobcolum) from yourtable ;

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 143083

You can't. BLOB is a Binary Large Object. It can contain e.g. images, music files, videos, ...

How do you plan to export a movie as a CSV or text file? What would you do with it, then?

Think it over, as it seems that you got something wrong.

Upvotes: 0

Related Questions