Step by step insert image file in database
Step 1: Create table with image or photo column having BLOB data type.
SQL> desc IMAGE.photo
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(38)
USERS_SYS_ID VARCHAR2(100)
PHOTO BLOB
LASTMODIFIED DATE
PHOTOTYPE NOT NULL NUMBER(8)
MIMETYPE VARCHAR2(32)
WIDTH NOT NULL NUMBER(8)
HEIGHT NOT NULL NUMBER(8)
PHOTO_NAME VARCHAR2(128)
IS_DEFAULT CHAR(1)
STATUS NUMBER(38)
Step 2: create logical directory structure to upload an Image or photo.
SQL> create or replace directory Upload as '/oracle/app/orawork/product/12.1.0.2/db_1/rdbms/upload';
Directory created.
SQL> grant read,write on directory Upload to IMAGE;
Grant succeeded.
Step 3: Move your image to the target server via ftp or any file movement tool (Ex: Winscp).
[oracle@dm01db01 image_upload]$ ls -ltr
-rw-r--r-- 1 oracle oinstall 12348 May 09 14:28 ora_1.png
File Name : ora_1.png
Size of file in bytes on Windows Server : 12348 bytes
Size of file in bytes on Oracle Server : 12348 bytes
check the record before inserting:
SQL> select * from IMAGE.photo where id=65414;
no rows selected
Note :You must need to insert photo or an image by creating below procedure.
DECLARE
src_lob BFILE := BFILENAME('UPLOAD','ora_1.png');
dest_lob BLOB;
BEGIN
insert into IMAGE.photo
values(65414,1575, EMPTY_BLOB(),sysdate,1,'DataMig01',10,20,'DataMig02','N',0)
RETURNING photo INTO dest_lob;
DBMS_LOB.OPEN(src_lob, DBMS_LOB.LOB_READONLY);
DBMS_LOB.LoadFromFile( DEST_LOB => dest_lob,
SRC_LOB => src_lob,
AMOUNT => DBMS_LOB.GETLENGTH(src_lob) );
DBMS_LOB.CLOSE(src_lob);
END;
/
SQL> select * from IMAGE.photo where id=65417;
ID USERS_SYS_ID PHOTO LASTMODIF PHOTOTYPE MIMETYPE WIDTH HEIGHT PHOTO_NAME I STATUS
---------- -------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------- --------- ---------- -------------------------------- ---------- --- ------- ----------------------------------
65414 1575 69404E440D0A1A0A0000000D4948445200000097000000970806000000189FC979000000017352774200AECE1CE900000004674147410000B18F0BFC6105000000097048597300000EC400000EC40195 09-MAY-20 1 DataMig01 10 20 DataMig02 N 0
Check the size of the image.
SQL> select dbms_lob.getlength(PHOTO) from TEST.photo where id=65414;
DBMS_LOB.GETLENGTH(PHOTO)
-------------------------
12348
ConversionConversion EmoticonEmoticon