How to Insert Photos into an Oracle Database Step-by-Step

Here are the steps to insert a image photo in Oracle Database.

Step 1:  Assume that you have already created table with image or photo column having BLOB data type.

SQL> desc TEST.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: Now create logical directory structure to upload an Image or photo.

SQL> create or replace directory Image_Upload as '/u01/app/grid/product/11.2.0/dbhome_1/rdbms/image_upload'; Directory created. SQL> grant read,write on directory Image_Upload to TEST; Grant succeeded.

Step 3: Transfer your image to the target server via ftp or any file transfer tool (Ex: File Zilla or Winscp).

[grid@osd2 image_upload]$ ls -ltr -rw-r--r-- 1 grid oinstall 11338 Aug 25 19:35 oracle.png File Name : oracle.png Size of file in bytes on Windows Server :  11338 bytes Size of file in bytes on Oracle Server         :  11338 bytes

#Check the record before inserting. SQL>  select * from TEST.photo  where id=99999; no rows selected Note :You must need to insert photo or an image by creating below procedure. DECLARE src_lob  BFILE := BFILENAME('IMAGE_UPLOAD','oracle.png'); dest_lob BLOB; BEGIN insert into TEST.photo values(99999,1196, EMPTY_BLOB(),sysdate,1,'DataMigration1',10,20,'DataMigration2','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; / #To get the inserted image details SQL>  select * from TEST.photo  where id=99999;                 ID USERS_SYS_ID         PHOTO                                        LASTMODIF  PHOTOTYPE MIMETYPE                               WIDTH     HEIGHT PHOTO_NAME                                                                           I          STATUS                 ---------- -------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------- --------- ---------- -------------------------------- ---------- ---               ------- -------------------------------------------------------------------------------------------------------------------------------- - ----------                  99999 1196                89504E470D0A1A0A0000000D4948445200000097000000970806000000189FC979000000017352474200AECE1CE90000000467414D410000B18F0BFC6105000000097048597300000EC400000EC40195 26-AUG-15       1 DataMigration1               10          20 DataMigration2                                                                                                                   N      0 #Check the size of the image. SQL> select dbms_lob.getlength(PHOTO) from TEST.photo where id=99999;   DBMS_LOB.GETLENGTH(PHOTO)   -------------------------   11338

       

Thanks for reading this post ! Please comment if you like this post ! Click on FOLLOW to get future blog updates !