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 11.2.0.4.0 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)||
'2;BBBBBB;'
)
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 '*/