How to insert and modify picture BLOB field in database
Insert exampl
1. Insert data by using stored procedures
-- create a table to store pictures
CREATE TABLE IMAGE_LOB (T_ID VARCHAR2 (5) NOT NULL, T_IMAGE BLOB NOT NULL)
-- create a directory to store pictures. I am on the local C disk.
CREATE OR REPLACE DIRECTORY IMAGES AS'C:\'
-- insert through stored procedures
CREATE OR REPLACE PROCEDURE IMG_INSERT (TID VARCHAR2, FILENAME VARCHAR2) AS
F_LOB BFILE;-- File type
B_LOB BLOB
BEGIN
-- insert empty blob: EMPTY_BLOB ()
INSERT INTO IMAGE_LOB (T_ID, T_IMAGE)
VALUES (TID, EMPTY_BLOB ()) RETURN T_IMAGE INTO B_LOB
-- get the files in the specified directory
Favored lobs = BFILENAME ('IMAGES', FILENAME)
-- Open the file as read-only
DBMS_LOB. FILEOPEN (F_LOB, DBMS_LOB. FILE_READONLY)
-- passing object
DBMS_LOB. LOADFROMFILE (B_LOB, F_LOB, DBMS_LOB. GETLENGTH (F_LOB))
-- close the original file
DBMS_LOB. FILECLOSE (F_LOB)
COMMIT
END
/
two。 Call a stored procedure to insert a picture
Call IMG_INSERT ('1', '1.jpg')
3. Verification result
Select * from IMAGE_LOB
Modify exampl
1. Modify through the process
Declare
L_bfile bfile
L_blob blob
Begin
Update IMAGE_LOB set T_IMAGE=empty_blob () where T_ID=1 return T_IMAGE into l_blob
-- the image path is still on disk C, which can be queried through the system table SELECT * FROM dba_directories;
L_bfile:=bfilename ('IMAGES',' 2.jpg')
-- Open the file as read-only
Dbms_lob. Open (l_bfile, dbms_lob. File_readonly)
-- passing object
Dbms_lob. Loadfromfile (l_blob, l_bfile, dbms_lob. Getlength (l_bfile))
-- close the original file
Dbms_lob. Close (l_bfile)
Commit
End
/
two。 Verification result
Select * from IMAGE_LOB
