| Extraction from BLOB data type [message #639774] |
Thu, 16 July 2015 01:14  |
 |
ANILKUYI
Messages: 1
Registered: July 2015
Location: DUBAI
|
Junior Member |
|
|
We have table with BLOB data type and stored with different types of documents (Eg. PDF, excel,word, txt etc). This documents are retrieved to the server directories successfully but upon opening them shows junk characters in all of them except PDF documents apparently looks corrupted. Our retrieval script looks good and seems working fine. Can any of you let me know how to ensure that they are stored in the database successfully in the first place ?
-> Oracle version is 11.2.0.3.0 (Linux machine)
-> Data is stored in ORACLE BLOB data type through application
-> Retrieved using Oracle pl/sql script to the database server (Script is available below)
-> Data retrieved in Linux machine is transported to the D: of desktop
-> Datafile is opened
Issue:
Pdf files are extracted properly. But, other files like jpeg, xls, doc, txt etc..are extracted but the files are corrupted & contains
junk characters. jpeg files cannot be viewed.
CREATE OR REPLACE PROCEDURE extract_script IS
vblob BLOB;
vstart NUMBER := 1;
bytelen NUMBER := 32000;
len NUMBER;
my_vr RAW(32000);
x NUMBER;
filename varchar2(100);
l_output utl_file.file_type;
BEGIN
SELECT X.REFERENCE || '_' || Z.FILE_NAME AS FILE_NAME,
DBMS_LOB.GETLENGTH(Z.FILE_CONTENT) AS FILE_LEN,
Z.FILE_CONTENT AS FILE_CONT
INTO filename, len, vblob
FROM TDR_DETAILS X, TDR_ATTACHMENT Y, JF_FILEUPLOAD_REPOS Z
WHERE X.CASEID = Y.CASE_ID
AND X.TDR_ISADTDR = 'YES'
AND Z.FILE_ID = Y.ATTACHMENT
AND X.CASEID = '72633620150119100546';
-- define output directory
l_output := utl_file.fopen('EXTRACT', filename, 'wb', 32760);
vstart := 1;
bytelen := 32000;
-- save blob length
x := len;
-- if small enough for a single write
IF len < 32760 THEN
utl_file.put_raw(l_output, vblob);
utl_file.fflush(l_output);
ELSE
-- write in pieces
vstart := 1;
WHILE vstart < len and bytelen > 0 LOOP
dbms_lob.read(vblob, bytelen, vstart, my_vr);
utl_file.put_raw(l_output, my_vr);
utl_file.fflush(l_output);
-- set the start position for the next cut
vstart := vstart + bytelen;
-- set the end position if less than 32000 bytes
x := x - bytelen;
IF x < 32000 THEN
bytelen := x;
END IF;
null;
end loop;
utl_file.fclose(l_output);
END IF;
end;
/
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
