Manejo De Datos Blob En Oracle

--set serveroutput on

--create table compress_blob (indx integer, y blob);

--create directory MYDIR as 'C:\temp';

--We assume the user doing this will have read/write on MYDIR

--Copy a file into the directory, e.g., A_57KB_Word_doc.doc

--This block will take care of the insert for you, or you

--could create a separate procedure to do this

--This is simple code, does not address other PL/SQL errors

DECLARE

ablob blob;

abfile bfile := bfilename('CARGUE_ARCHIVOS', 'Formato1_2013_09_01102013_081618.csv');

-- Gets a pointer to the file.

a_compressed_blob blob;

amount integer;

asize integer;

quality integer := 9;

cursor blob_cur is select * from compress_blob;

BEGIN

--

-- compress_blob table is initialized with one record because

-- the PL/SQL BLOB locator (ablob) must point to a specific

-- EXISTING NON-NULL database BLOB.

--

-- initialize the blob locator

insert into compress_blob values (1, empty_blob());

select y into ablob from compress_blob where indx = 1;

-- open the bfile and get the initial file size

dbms_lob.fileopen(abfile);

asize := dbms_lob.getlength(abfile);

dbms_output.put_line('Size of input file: ' || asize);

-- load the file and get the size

dbms_lob.loadfromfile(ablob, abfile, asize);

dbms_output.put_line('After loadfromfile');

asize := dbms_lob.getlength(ablob);

dbms_output.put_line('Size of blob: ' || asize);

-- compress the blob

-- you can experiment with varying the quality

a_compressed_blob := utl_compress.lz_compress(ablob, quality);

-- insert the compressed blob

insert into compress_blob values (2, a_compressed_blob);

-- compare the sizes of the blobs in the table

dbms_output.put_line

('Sizes before and after insertion/compression -->');

for c1_rec in blob_cur

loop

asize := dbms_lob.getlength(c1_rec.y);

dbms_output.put_line(asize);

end loop;

end;

...