After Work
The best club in Chicago
for Chicago Singles is
Highlife Adventures!
Kyoto Sushi
in Chicago, on Lincoln Ave.
Moody's Pub
in Chicago, has great burgers
Skylark
in Chicago, on Halsted .. excellent beer selection!
|
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);
|
|
|
|
