| BLOB Column with Zip File Convert to CLOB [message #689608] |
Tue, 20 February 2024 14:06  |
Duane
Messages: 581
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 #689624 is a reply to message #689610] |
Wed, 28 February 2024 14:50  |
Duane
Messages: 581
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 |
Duane
Messages: 581
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;
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
