Skip to Main Content

How to select csv data stored in a BLOB column as if it were an external table?

HoekJan 15 2015 — edited Feb 18 2015

Hi all,

(Glad to be back after a while! )

Currently I'm working on a site where users must be able to load csv data (semi-colon is the separator) from their client machines (APEX 3.2 application)  into Oracle Database EE.

My problem is:

I cannot use an external table (for the first time in my life ) so I'm a bit clueless what to do since the csv data is stored by the APEX application into a BLOB column and I'm looking for an elegant way (minimizing PL/SQL/maximizing SQL) to insert the data into the destination table  (performing validations through a MERGE would be the most efficient way to get the job done).

I've found a couple of examples, but I think they're too cumbersome and there might be a more elegant way in Oracle DB 11.2.

Simple testcase:

drop table src purge;

drop table dst purge;

create table src

( myblob blob


create table dst

( num number

, str varchar2(6)


insert into src

select utl_raw.cast_to_raw( '1;AAAAAA;'||chr(10)||



from   dual;

Desired output (ofcourse) based on the data in table SRC:

SQL> select * from dst;

       NUM STR

---------- ------

         1 AAAAAA

         2 BBBBBB

Does anyone know an efficient solution for this?

All ideas/pointers/links/examples are more than welcome!

  /* disclaimer: I've been 'off' for about 3 months, so the Oracle-part of my brain has become a bit 'rusty and I feel this should not be so complicated as the examples I found sofar '*/

This post has been answered by Boneist on Jan 16 2015
Jump to Answer
Post Details
Added on Jan 15 2015