How to create the table in Oracle that is populated by images(blob fields)

In one of our support cases, the customer had problems when they tried to work in their report with images that were stored in Oracle database.

To create the test environment that could reproduce the customer's environment we needed to install the Oracle database and load into it some images, which we then could use in our test report.

We have decided to describe the process step by step because loading images into the oracle database appears less obvious than we first thought

Steps that should be done before the steps that were described in this article:

1. Oracle XE database should be installed. You can download and install this database from the oracle site. Installation is fairly easy on Windows, and we did not receive any errors during installation process.
2. Oracle provides convenient GUI tool to administer databases, create queries and stored procedures. This is a free tool that is called SQLDeveloper and can also be downloaded from the Oracle site.

Since the previous steps were successfully completed we can start to prepare our test environment.

Lets start the SQLDeveloper, Log in as a SYSTEM user(password for this user should be defined during the installation process) and start our work :)

For our test database we will create a separate user, testuser1, and grant some permissions to him. To do these steps :

1. We should create a user, testuser1, and define a password for them. To do that we execute the SQL command under the SYSTEM user account that is shown below:

   CREATE USER TESTUSER1 IDENTIFIED BY PASSWORD; 
   

   This command creates a user with the name testuser1 and password equal to the word password.

   

2. We should grant to this user permissions to allow him to work with Oracle under his account. To do that we should execute the SQL command under SYSTEM user account that is shown below:

   GRANT CONNECT, RESOURCE, CREATE ANY DIRECTORY TO TESTUSER1;
   

   

3. Next two screenshots show how to create connection for testuser1 account and connect to Oracle XE database.

   

   

4. After we have connected to Oracle under testuser1 we can continue our work: We need to create a local folder which than will be used to store images that should be loaded into Oracle table.

   1. To do this lets create the folder images in drive C('C:images');

   2. We should register this directory in Oracle.To do this we should execute the SQL command under testuser1 account that is shown below:

      CREATE DIRECTORY imgdir AS 'C:images';

      

We continue with creating autoincrement sequence and the table, in which we then upload images:

1. To create sequence in Oracle we should execute the SQL command under testuser1 account that is shown below:

   CREATE SEQUENCE image_id START WITH 1 INCREMENT BY 1 NOMAXVALUE;
   

   

2. To create the table that stores blob field with images we should execute the SQL command under testuser1 account that is shown below:

   CREATE TABLE images(img_id NUMBER, icon BLOB); 
   

   

3. To upload images into our table we have created the stored procedure. This stored procedure has only one input parameter that represents the name of the image, that should be uploaded from C:images directory. To create such stored procedure we should execute the SQL command under testuser1 account that is shown below:

   create or replace PROCEDURE load_file ( pfname VARCHAR2) IS l_size number; l_file_ptr bfile; l_blob blob; begin l_file_ptr := bfilename('IMGDIR', pfname); dbms_lob.fileopen(l_file_ptr); l_size := dbms_lob.getlength(l_file_ptr); insert into images ( img_id, icon ) values ( image_id.nextval, empty_blob() ) returning icon into l_blob; dbms_lob.loadfromfile(l_blob, l_file_ptr, l_size); commit; dbms_lob.close(l_file_ptr); end; 
   

   

4. After this step we can upload test images to the database by calling our stored procedure like shown below:

   EXECUTE LOAD_FILE('img1.jpg');

   

5. Then we can check that image is succsessfully uploaded from SQLDeveloper by browsing row in IMAGES table and clicking the contents of the ICON column

   

   At this point we have created the test environment that includes the Oracle table populated by images(as blob fields) that then can be used for testing Oracle and iReport together.

Thats all. Thank you for reviewing my article :)