Custom Search
www.rocket99.com : Technical Guides Sybase Oracle UNIX Javascript


Technical Guides
Sybase
Oracle
UNIX
Javascript




Of Interest

Business Intelligence and Analytics



Oracle Training





Oracle » PL-SQL » Design »

Using Blobs

     



Blob variables require special handling in PL-SQL. When reading from a
file to a blob, only one statement is required. When reading from a
blob field to a PL-SQL variable, only 32k blocks can be processed, thus
necessitating a loop construct.




/*---------------------------------------*/
/* Read a blob from a file, and write */
/* it to the database. */
/*---------------------------------------*/

set serveroutput on size 500000 ;

truncate table image_test ;

create or replace directory image_dir as '/apps/temp/images' ;


create or replace procedure proc_imp_jpg
(fname1 in varchar2, image_id1 in numeric) is

file1 bfile ;
lblob blob ;
len int ;
e_blob blob ;

begin

file1 := bfilename('IMAGE_DIR',fname1);

e_blob := empty_blob();

insert into image_test (image_id, image_data)
values (image_id1, e_blob )
returning image_data into lblob ;

dbms_lob.fileopen(file1);

len := dbms_lob.getlength(file1) ;

dbms_lob.loadfromfile(lblob,file1,len);

dbms_lob.filecloseall();

commit;


exception
when others then
begin
dbms_output.put_line(sqlerrm);
dbms_lob.filecloseall();
commit;
end;

end ;
/


call proc_imp_jpg('jada.jpg',101)
/


/*-----------------------------*/
/* determine the length of */
/* a blob field */
/* by reading it */
/*-----------------------------*/
CREATE OR REPLACE PROCEDURE PROC_BLOB_LENGTH
(PART_ID1 NUMBER)
IS
SRC_LOB BLOB;
BUFFER RAW(100);
AMT BINARY_INTEGER := 100;
POS INTEGER := 1;
COUNTER INTEGER :=0;

BEGIN
SELECT PART_PHOTO INTO SCR_LOB
FROM PARTS
WHERE PART_ID=PART_ID1 ;


IF (SRC_LOB IS NOT NULL) THEN

LOOP
DBMS_LOB.READ (SRC_LOB, AMT, POS, BUFFER);
POS := POS + AMT;
COUNTER:=COUNTER+1;
END LOOP;

ELSE
DBMS_OUTPUT.PUT_LINE('** Source is null');
END IF;


EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('End of data, total bytes:');
DBMS_OUTPUT.PUT_LINE(POS);
END;

/* ============ Other blob length examples ====== */
/**** Note ! These functions may return null, if the column is null ... an Oracle bug */

X := UTL_RAW.LENGTH(RPT_BODY) ;

Y := DBMS_LOB.GETLENGTH(LONG_RAW_COL);












Oracle : Related Topics


Sybase Web Site
Sybase iAnywhere Mobile Web Site
Oracle Enterprise Web Site



Get the latest Rocket99 news and tech tips via






Site Index About this Guide to Sybase, Oracle, and UNIX Contact Us Advertise on this site




Copyright © 2016 Stoltenbar Inc All Rights Reserved.