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

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 ;

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.

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