How to read Write BLOB.

SELECT * FROM DBA_DATA_FILES;

drop TABLESPACE LOB_TBS1 INCLUDING CONTENTS ;

CREATE TABLESPACE LOB_TBS1
DATAFILE '/d01/oracle/oradata/lob_tbs1.dbf' SIZE 800M REUSE
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 64M
SEGMENT SPACE MANAGEMENT AUTO;

--------------------

CREATE TABLE customer_profiles (
id NUMBER,
full_name VARCHAR2(45),
resume CLOB DEFAULT EMPTY_CLOB(),
picture BLOB DEFAULT EMPTY_BLOB())
LOB(PICTURE) STORE AS BASICFILE
(TABLESPACE lob_tbs1);


-----------------

INSERT INTO customer_profiles
(ID, FULL_NAME, RESUME, PICTURE)
VALUES (164, 'Charlotte Kazan', EMPTY_CLOB(), NULL);
----------

UPDATE customer_profiles
SET resume = 'Date of Birth: 8 February 1951',
PICTURE = EMPTY_BLOB()
WHERE ID = 164;
-------------

UPDATE customer_profiles
SET RESUME = 'Date of Birth: 1 June 1956'
WHERE id = 150;

------------

CREATE OR REPLACE PROCEDURE loadLOBFromBFILE_proc(p_dest_loc IN OUT BLOB, p_file_name IN VARCHAR2,p_file_dir IN VARCHAR2) IS
v_src_loc BFILE := BFILENAME(p_file_dir, p_file_name);
v_amount INTEGER := 4000;
BEGIN
DBMS_LOB.OPEN(v_src_loc, DBMS_LOB.LOB_READONLY);
V_AMOUNT := DBMS_LOB.GETLENGTH(V_SRC_LOC);
DBMS_LOB.LOADFROMFILE (p_dest_loc, v_src_loc, v_amount);
DBMS_LOB.CLOSE(V_SRC_LOC);
END loadLOBFromBFILE_proc;


------
exec loadLOBFromBFILE_proc ('a','1',

----------------


CREATE OR REPLACE PROCEDURE write_lob(P_REC_ID NUMBER,p_file_name IN VARCHAR2, p_file_dir IN VARCHAR2)
IS
v_fn VARCHAR2(15);
V_LN VARCHAR2(40);
V_B BLOB;
v_src_loc BFILE := BFILENAME(p_file_dir, p_file_name);
BEGIN
DBMS_OUTPUT.ENABLE;
IF DBMS_LOB.FILEEXISTS(V_SRC_LOC) = 1 THEN
DBMS_OUTPUT.PUT_LINE('Begin Udating rows...');
UPDATE CUSTOMER_PROFILES
SET PICTURE=EMPTY_BLOB()
WHERE ID=P_REC_ID
RETURNING PICTURE INTO V_B;
LOADLOBFROMBFILE_PROC (V_B,P_FILE_NAME, P_FILE_DIR);
--COMMIT;
ELSE
DBMS_OUTPUT.PUT_LINE('File does not exists.');
END IF;
END write_lob;



-----------------


exec write_lob( 164,'g2g.jpg','LOB_READ');

select * from customer_profiles;

---delete  FROM customer_profiles


UPDATE CUSTOMER_PROFILES
SET PICTURE=EMPTY_BLOB()
WHERE ID=164;

--------





create or replace procedure read_lob
IS
V_LOB_LOC BLOB;

CURSOR profiles_cur IS
SELECT id, full_name, resume, picture
FROM customer_profiles;
v_profiles_rec customer_profiles%ROWTYPE;
BEGIN
OPEN profiles_cur;
LOOP
FETCH profiles_cur INTO v_profiles_rec;
v_lob_loc := v_profiles_rec.picture;
DBMS_OUTPUT.PUT_LINE('The length is: '||
DBMS_LOB.GETLENGTH(v_lob_loc));
DBMS_OUTPUT.PUT_LINE('The ID is: '|| v_profiles_rec.id);
DBMS_OUTPUT.PUT_LINE('The blob is read: '||
UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(v_lob_loc,200,1)));
EXIT WHEN profiles_cur%NOTFOUND;
END LOOP;
CLOSE profiles_cur;
END;