Good morning/afternoon guys.

I am loading XML files from Windows into the database as BLOBs via an Apex front-end, however the performance leaves much to be desired (unsurprising, due to the 1Gb file sizes).

I have a cunning plan to first zip the files (with Windows 7zip) down to a far more manageable 16K-ish and then uncompress them with UTL_COMPRESS once loaded.

I'm pretty ignorant when it comes to anything to do with compression, so I had a good hunt around the docs. & forums, but cannot find anything that indicates this may be possible.


Could you tell me if/how it is possible to do this. Is UTL_COMPRESS even compatible with such external compression algorithms? Note, any solution must be entirely Windows-based as the users need to manually do this compression from their PCs. If 7zip is a no-go, I'm perfectly prepared to swap to another.

(Sadly, I'm also still stuck on 11g2).

[oracle@localhost tmp]$ cat test.txt
1,xxxxxxx,yyyyyyy
2,xxxxxxx,yyyyyyy
3,xxxxxxx,yyyyyyy
4,xxxxxxx,yyyyyyy
5,xxxxxxx,yyyyyyy
6,xxxxxxx,yyyyyyy
[oracle@localhost tmp]$ gzip test.txt 
[oracle@localhost tmp]$ ls test.txt*
test.txt.gz

create or replace directory exec_dir as '/bin';
grant execute on directory exec_dir to chris;

conn chris/chris

create table t (
  c1 int, 
  c2 varchar2(20), 
  c3 varchar2(20)
) organization external (
  default directory tmp
  access parameters ( 
    records delimited by newline
    disable_directory_link_check
    preprocessor exec_dir:'zcat'
    fields terminated by ',' (
      c1, c2, c3
    )
  )
  location ( 'test.txt.gz' )
);

select * from t;

C1   C2        C3        
   1 xxxxxxx   yyyyyyy   
   2 xxxxxxx   yyyyyyy   
   3 xxxxxxx   yyyyyyy   
   4 xxxxxxx   yyyyyyy   
   5 xxxxxxx   yyyyyyy   
   6 xxxxxxx   yyyyyyy