I have a simple table where I want to insert an image that exists on my machine.I would like to insert the picture into my table's BLOB column. Just wondering how can I do it. I understand that there are some existing solutions which are related to BLOB but none have helped me directly by using INSERT SYNTAX.
CREATE TABLE test(id int,photo BLOB);
INSERT INTO test VALUES(1,'Path of the picture\filename');
First of all, create a directory to store images and grant read, write permission to the user. Then you can use BFILENAME function to insert the image.
SQL> conn / as sysdba
SQL> create directory image_dir as '/home/oracle/Desktop/';
Directory created.
SQL> grant read, write on directory image_dir to jay;
Grant succeeded.
SQL> conn jay
Enter password:
Connected.
SQL> CREATE TABLE test(id number, image blob);
Table created.
Now, to store the give image can use the following insert statement.
[oracle@myserver Desktop]$ ls -l | grep abc
-rw-r--r-- 1 oracle oinstall 269748 Apr 16 01:23 abc.png
SQL> INSERT INTO test VALUES(1,bfilename('IMAGE_DIR','abc.png'));
1 row created.
Reference: BFILENAME
photocolumn value. Insert blob value, not the path.