Home » SQL & PL/SQL » SQL & PL/SQL » BLOB Column with Zip File Convert to CLOB (Oracle, 19.0.0.0.0, Linux)
BLOB Column with Zip File Convert to CLOB [message #689608] Tue, 20 February 2024 14:06 Go to next message
Duane
Messages: 557
Registered: December 2002
Senior Member
Need some more help here.

Users are able to upload multiple files that are stored in a BLOB column as a zip file (multiple-files.zip = multiple-files-1.csv; multiple-files-2.csv; multiple-files-3.csv; multiple-files-4.csv; multiple-files-5.csv)

I need to take each file and convert that BLOB to a CLOB.  


  • I'm guessing the data is compressed.
  • I'm guessing the data contains the file name.
  • I'm guessing I'll need to take each file and convert that BLOB to a CLOB so I can process the data.
Any ideas on how to approach this?



Partial Raw Data:


PK.........CTX................multiple-files-5.csv....-Ir.vo...w..^dF._..mC$eA.-Cw..!..a......w..<.....bN7.......2.w..?......._..?....._..............._~............O......?|.........?}.._.......?..^Z....gR...g...?.../

Re: BLOB Column with Zip File Convert to CLOB [message #689609 is a reply to message #689608] Tue, 20 February 2024 15:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68633
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If APEX is installed in your database you can use APEX_ZIP, see examples there.

[Updated on: Wed, 21 February 2024 00:42]

Report message to a moderator

Re: BLOB Column with Zip File Convert to CLOB [message #689610 is a reply to message #689609] Tue, 20 February 2024 15:45 Go to previous messageGo to next message
Duane
Messages: 557
Registered: December 2002
Senior Member
We do not.

I think I might play around with the utl_compress procedure.  Can't hurt.

Anything else?

Thank you.
Re: BLOB Column with Zip File Convert to CLOB [message #689624 is a reply to message #689610] Wed, 28 February 2024 14:50 Go to previous messageGo to next message
Duane
Messages: 557
Registered: December 2002
Senior Member
Ok, need some help here.

I'm sure I'm doing something stupid but I can't see to figure it out.  I'm sure someone here can spot what I'm doing wrong.

Problem:

I have a ZIP File that includes 5 file names (multiple-files-1.csv, multiple-files-2.csv, multiple-files-3.csv, multiple-files-4.csv, multiple-files-5.csv).  The code correctly returns all 5 file names.  The problem is it keeps returning the same data for the first file name it returns.  The code loops through the 5 file names but if multiple-files-5.csv is returned first then the CLOB data is for that file for 5 times.  If I change the statement to read the data for multiple-files-1.csv then the data returned is for multiple-files-1.csv 5 times.  That should be the case since that file is hard-coded into the statement.

What statements do I need to grab the data for the file being processed and then clear that data and grab the data for the next file being processed?  In the end, ClobDataAppend should contain the data for all 5 files.

Solution:

Loop through the Zip File and return the File Names.  Loop through the File Names and return the data as a Clob.  Append all the Clobs into one Clob and return that Clob.


Current code finds the File Names.  Current code loops through those file names but only returns the Clob data for the first File Name in the list.



declare

  ClobOffset      integer default 1;
  BlobOffset      integer default 1;
  LanguageContext integer default dbms_lob.default_lang_ctx;
  Warning         integer;
  
  BlobFile        blob;
  BlobData        blob;
  ClobData        clob;
  ClobDataAppend  clob;
  
  type UnZipFileNameList is table of varchar2(100);
  
  FileName        UnZipFileNameList;
  

  begin   
    select file
      into BlobFile
      from Files
        where sequence = '1';
   
    dbms_lob.createTemporary(BlobData, false);
    dbms_lob.createTemporary(ClobData, false);
    dbms_lob.createTemporary(ClobDataAppend, false);
    
    FileName := UnZipGetFileList(BlobFile);
        
    for i in 1..FileName.count
      loop
        BlobData := zip.ZIPFile(BlobFile, FileName(i));
        --BlobData := zip.ZIPFile(BlobFile, 'multiple-files-1.csv');
        
        dbms_lob.converttoclob(ClobData, BlobData, dbms_lob.lobmaxsize, ClobOffset, BlobOffset, dbms_lob.default_csid, LanguageContext, Warning);
        dbms_lob.append(ClobDataAppend, ClobData);
        
        dbms_output.put_line(FileName(i));
      end loop;
      
      dbms_lob.freetemporary(BlobData);
      dbms_lob.freetemporary(ClobData);
      dbms_lob.freetemporary(ClobDataAppend);

   end;

Re: BLOB Column with Zip File Convert to CLOB [message #689625 is a reply to message #689624] Wed, 28 February 2024 15:20 Go to previous messageGo to next message
Duane
Messages: 557
Registered: December 2002
Senior Member
Never mind.  I got it.

Revised Code:


declare

  ClobOffset      integer default 1;
  BlobOffset      integer default 1;
  LanguageContext integer default dbms_lob.default_lang_ctx;
  Warning         integer;
  
  BlobFile        blob;
  BlobData        blob;
  ClobData        clob;
  ClobDataAppend  clob;
  
  type UnZipFileNameList is table of varchar2(100);
  
  FileName        UnZipFileNameList;
  

  begin   
    select file
      into BlobFile
      from Files
        where sequence = '1';
   
    FileName := UnZipGetFileList(BlobFile);

    dbms_lob.createTemporary(ClobDataAppend, false);
        
    for i in 1..FileName.count
      loop
        ClobOffset := 1;
        BlobOffset := 1;
        LanguageContext := dbms_lob.default_lang_ctx;
        Warning := null;
        
        dbms_lob.createTemporary(BlobData, true);
        dbms_lob.createTemporary(ClobData, true);
        BlobData := zip.ZIPFile(BlobFile, FileName(i));
        --BlobData := zip.ZIPFile(BlobFile, 'multiple-files-1.csv');
        
        dbms_lob.converttoclob(ClobData, BlobData, dbms_lob.lobmaxsize, ClobOffset, BlobOffset, dbms_lob.default_csid, LanguageContext, Warning);
        dbms_lob.append(ClobDataAppend, ClobData);
        
        dbms_lob.freetemporary(BlobData);
        dbms_lob.freetemporary(ClobData);
        
        dbms_output.put_line(FileName(i));
      end loop;
      
      dbms_lob.freetemporary(ClobDataAppend);

   end;



Re: BLOB Column with Zip File Convert to CLOB [message #689626 is a reply to message #689625] Thu, 29 February 2024 00:51 Go to previous messageGo to next message
Michel Cadot
Messages: 68633
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What are "UnZipGetFileList" procedure and "zip" package?

Re: BLOB Column with Zip File Convert to CLOB [message #689627 is a reply to message #689626] Thu, 29 February 2024 15:30 Go to previous messageGo to next message
Duane
Messages: 557
Registered: December 2002
Senior Member
UnZipGetFileList - Is a local procedure that pulls the file names from a ZIP File.

ZIP - Is a local Package that contains the UnZipGetFileList.
Re: BLOB Column with Zip File Convert to CLOB [message #689628 is a reply to message #689627] Fri, 01 March 2024 00:45 Go to previous messageGo to next message
Michel Cadot
Messages: 68633
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Can you post them to help other people having the same issue.
Thanks.

[Updated on: Fri, 01 March 2024 00:46]

Report message to a moderator

Re: BLOB Column with Zip File Convert to CLOB [message #689629 is a reply to message #689628] Fri, 01 March 2024 09:33 Go to previous messageGo to next message
Duane
Messages: 557
Registered: December 2002
Senior Member
I can't post them but modeled after https://github.com/antonscheffer/as_zip.  I found that package after doing a search.

That should help others looking to do the same thing.


[Edit MC: fix url]

[Updated on: Fri, 01 March 2024 10:44] by Moderator

Report message to a moderator

Re: BLOB Column with Zip File Convert to CLOB [message #689630 is a reply to message #689629] Fri, 01 March 2024 10:44 Go to previous message
Michel Cadot
Messages: 68633
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

OK, thanks.

Previous Topic: update table attribute help!
Next Topic: Prevent user from executing a script until they will have read and understood it
Goto Forum:
  


Current Time: Sat Apr 13 09:00:40 CDT 2024