Shay Shmeltzer's Oracle Development Tools Tips

Storing Image Files in Your Database from a Visual Builder App

Shay Shmeltzer
Director of Product Management - Oracle

The business objects in Oracle Visual Builder let you store textual data, but what if you need to store some binary data such as images? You'll need to find a separate location to store those. Oracle has various solutions for this including the storage cloud and the content and experience cloud, in this blog we'll show you how to do this using Oracle ATP Database (or any other Oracle DB).

The concept is simple, you create a table with a Blob type column in your DB, in that column you are going to store the images. You now need to expose that table through a REST service - and call that REST service from your Visual Builder app to load the images. In your Visual Builder app you can have a business object where one of the fields will contain the URL that is used to fetch the image back. In the video demo below I show you how to build this solution.

The Database Part - ATP+ORDS

I'm using an ORDS based module that exposes the get and post methods on the table. Blain Carter pointed me to this tutorial that covers creating the table and the ORDS interfaces. A couple of tips as you work through the tutorial:

You need to first complete the method for getting a specific image by ID, before you can test the POST method (which depends on that GET method for the return value).

The PL/SQL code I used is:

 image_id integer;
 insert into gallery (title,content_type,image) 
             values  (:title,:content_type,:body)
             returning id into image_id;
 :status := 201;
 :location := image_id;

And the parameters are:

4 parameters to method

Note that I also needed to whitelist my VB instance on the ORDS side - so I'll be allowed to access that end point.

Now that you have the backend services, it's a simple matter of building the VB based UI.

The Visual Builder Part

In VB you'll define service connections to work with the ORDS endpoints. Note that you'll need to switch the MediaType of the endpoints to accept image/png instead of the regular application/json.

New Media Type setting for endpoint

Then we use the take picture action to allow us to pick an image either from the hard-drive when running as a web app, or from the device when running as a mobile app.

action chain for taking a picture

We then pass the file to the REST endpoint which creates a record in the DB storing the image. The REST service returns a URL address for the image location as the text value we store in the VB business object. The value is returned as part of the header - so we use this expression to get it: $chain.results.callRestEndpoint1.headers.get('location')

Check out the video below to see the development process in VB:

  • 0:00-1:30 Review the Database and ORDS structure
  • 1:30-3:30 Define REST service connections in Visual Builder
  • 3:30-5:30 Create Business Object and basic UI for mobile app
  • 5:30-7:40 Creating an action chain to take a picture and load to DB
  • 7:30-8:45 Test run in emulator
  • 8:45-end PWA enable the app and test on device

Join the discussion

Comments ( 8 )
  • Guy Scudamore Saturday, June 20, 2020
    Fantastic intro for creating the app which I'll have a go at soon.
    How would a GPS reference be created with the photo when returning into the database?
  • Shay Shmeltzer Sunday, June 21, 2020
    Guy - you should be able to use the get location action that VB offers in the same action chain that takes the picture to get the location.
  • Aditya Anand Friday, July 3, 2020
    Can you please tell how do you get that URL ?? what if i just simply have to take picture and add it to a avatar.
  • Shay Shmeltzer Tuesday, July 14, 2020
    Aditya, A better place for questions is our forum here: https://cloudcustomerconnect.oracle.com/resources/e610f4723c/summary
    I'm not sure what URL you are referring to. For updating an image on your page see - https://blogs.oracle.com/vbcs/say-cheese-take-picture-action-improvements-in-visual-builder-1913-release
  • Geert Engbers Thursday, July 23, 2020
    As Aditya I am also having problems with the URL. In the PL/sql code you assign the id of the record that has just been inserted into to location bind variable. In the Assign Variable Contacts you retrieve this location using the ....get('location') and this places a url into the picture field of the contact table.


    The url I get does not show the picture I just uploaded.
  • Shay Shmeltzer Thursday, July 23, 2020
    Geert - did you check what value is returned from the REST call in the browser's network tab? If you call the REST service from POSTMAN - does it return the right value?
    Please post on our forum if you want to discuss further.
  • Geert Engbers Monday, July 27, 2020
    Hello Shay. Thank you for taking time to respond! I have been posting on https://community.oracle.com/thread/4340076 . Is this the forum you refer to? If not could you let me know that the correct forum is?
  • Shay Shmeltzer Monday, July 27, 2020
    Geert - no that's an old one - use the one linked from the help options in VB - at https://cloudcustomerconnect.oracle.com/resources/e610f4723c/summary
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.