Visual Builder provides developers with the capability to create applications utilizing an external Oracle database. For organizations prioritizing data security, the preference is often to maintain their database within a private subnet, avoiding exposure on public networks. With the recent ability to provision a Visual Builder instance in a private subnet, developers can now just as easily create apps exposing data from a database in a secure, private network.

There are two common methods to accomplish this: replacing the built-in database with an external one and generating VB business objects, or utilizing VB to connect to ORDS exposing the database as RESTful services.

This blog post will guide you through the configuration process and demonstrate how to create an application in this context, offering a practical example.

Included in the latest release is the ability to create a new Visual Builder instance using a Private Subnet.  This means you can connect your Private VB instance directly to your Private Autonomous Database! 

Oracle always tries to make our products as secure as possible, and second we try to make working with them as easy as we can without compromising the security.  The new Private Visual Builder feature accomplishes both.

In this post I'll walk through the steps to attach a Database to Visual Builder, both on a private subnet.

TLDR; This is the same process as connecting with Public resources.

–>

Quick Links

Prerequisites

  • You have created an Oracle ATP Database that only uses a private subnet
  • You have created an Oracle Visual Builder instance using the same private subnet
  • You have used one of the methods in the docs to configure access to the private VB instance.  (For my examples I'm using the Load Balancer method)

Autonomous Database Details

First you'll need a couple things from the Autonomous Database details page.

  1. Open the Autonomous Database details page for your private DB
  2. Copy the OCID for your Database and save it for later
    Database OCID
  3. Click the "Database Connection" button
    Database Connection
  4. Click the "Download Wallet" button, fill in the details, save the file and close the connections frame
    Download Wallet
  5. Switch to the "Tool Configuration" tab
    Tool Configuration
  6. Locate the "Web Access (ORDS) section, copy and save the "Private access URL" (You'll use this to test the service connection)
    Copy Web Access URL

Connect Visual Builder to the Autonomous Database

Now you'll replace the Database include with your VB instance with your Private Autonomous Database.

  1. Open the Service Homepage for your VB instance
  2. Expand the side menu
    Side Menu
  3. Open the "Settings" page
    Settings
  4. Switch to the "Tenant Database" tab and click "Use Different Database"
    Use Different Database
  5. Set the "Connection Type" to "Oracle Autonomous Transaction Processing Cloud Wallet"
  6. Drag the wallet zip file from above, drop it into the "Upload Wallet" box and enter the password for the Zip file in "Wallet Password"
  7. Populate the remaining fields with your Database connection values
  8. Click "Next"
    Database Details
  9. Click "Finish"
    Database Finish Setup
  10. Once the connection is complete click "Reload"
    Reload

Your Visual Builder instance is now using your Private Autonomous Database. You can now either create new business objects that will store data in tables in the private database, or map business objects to existing tables in the database.

If you're already familiar with Visual Builder, you can stop here and go work with your applications.  If you still aren't sure it's the same as working with a public instance, you can follow the below examples to create a Proof of Concept Application.

Visual Builder Application with Private ORDS services

New VB application

  1. Open the Service Homepage for your VB instance
  2. Click the "New" button
    New VB Application
  3. Enter an "Application Display Name" and click Finish
    VB Application Details

Create a Service Connection for your ORDS API

  1. Open the "Services" panel
    Services Panel
  2. Click the "+ Service Connection" button
    New Service Connection
  3. Click "Define by Specification"
    Define By Specification
  4. Populate the details
    If you already have an ORDS API in your Private Database, replace the following information with yours.
    If you need one, you can follow these instructions.
        Name: Gifts
        URL: "<ORDS URL copied from above>vb_demo/open-api-catalog/gifts/"
        Metadata Retrieval Option: "Copy full OpenAPI to the application"
        Connection Type: "Dynamic, the service does not support CORS"
  5. Click "Create Backend"
    Service Connection Details
  6. Select "Compute missing operantionIds"
  7. Click "OK"

    Compute Missing OperantionIds

  8. Enter the "Backend Name"
    'Gifts'
  9. Click "Create" 
    Backend Details

Create a Web Application

  1. Open the "Web Apps" panel
    Web Apps Panel
  2. Click the "+ Web Application" button
    New Web Application
  3. Enter a name and click "Create"
    Web Application Name
  4. Open the "Data" tab and expand Services / Gifts / Other Endpoints
    Expand Services
  5. Drag "Get Many" and drop it into the application page
    DnD Service
  6. Select Render as "Table"
    Select Table
  7. Select all of the objects under "{ } item[i]" then click "Next"
    Select Columns
  8. Click "Finish"
    Finish Table

The data from your ORDS API will be displayed in your application
App With Data

 Open the Cloud Shell in your Private Subnet

The following sections are only needed if you don't already have an ORDS API.

  1. In your OCI Console, open the "Cloud Shell"
    Open Cloud Shell
  2. Expand the "Network" list and select "Ephemeral private network setup"
    Setup Private Cloud Console
  3. Select your VCN and your Private Subnet then click "Use as active network"
    Private Network Details

    Note: Steps 2 & 3 are the only difference between using a Public and Private subnet for these examples.

If you started reading this post thinking this was going to be a difficult process, this Proof of Concept should alleviate your concerns. Your new Private Visual Builder instance should function the same as a Public instance with the added security of being inside of a Private Subnet.

Create Database Objects and an ORDS API

  1. Copy the OCID for your new database that you saved above
  2. In the cloud shell enter the following commands
    Replace the <text> items with your values 
    export DB_OCID=<your copied ocid>
mkdir -p ~/wallets/vbdemo
cd ~/wallets/vbdemo
oci db autonomous-database generate-wallet --autonomous-database-id ${DB_OCID} --password Pw4ZipFile --file Wallet_VBDEMO.zip
unzip Wallet_VBDEMO.zip
export TNS_ADMIN=~/wallets/vbdemo
  3. Connect to SQLcl 
    sql admin/<Your Admin Password>@<your TNS Name>
  4. Create a new schema 
    CREATE USER VB_DEMO IDENTIFIED BY Tester1tester;
ALTER USER VB_DEMO TEMPORARY TABLESPACE temp;
GRANT CONNECT, RESOURCE, CREATE SESSION, UNLIMITED TABLESPACE TO VB_DEMO;
GRANT CREATE TABLE,
CREATE VIEW,
CREATE SEQUENCE,
CREATE PROCEDURE,
CREATE TYPE,
CREATE SYNONYM
TO VB_DEMO;
  5. REST enable the schema 
    BEGIN
    ORDS.ENABLE_SCHEMA(
        p_enabled => TRUE,
        p_schema => 'VB_DEMO',
        p_url_mapping_type => 'BASE_PATH',
        p_url_mapping_pattern => 'vb_demo',
        p_auto_rest_auth=> FALSE
    );
    commit;
END;
/
  6. Create the gifts table 
    CREATE TABLE VB_DEMO.GIFTS (
  ID NUMBER GENERATED ALWAYS AS IDENTITY,
	PRODUCT VARCHAR2(20 BYTE) COLLATE "USING_NLS_COMP",
	COST NUMBER,
	SKU NUMBER,
	PICTURE VARCHAR2(4000 BYTE) COLLATE "USING_NLS_COMP",
  PRIMARY KEY (ID)
   )  DEFAULT COLLATION "USING_NLS_COMP";
  7. REST enable the table 
    BEGIN
    ORDS.ENABLE_OBJECT(p_enabled => TRUE,
                       p_schema => 'VB_DEMO',
                       p_object => 'GIFTS',
                       p_object_type => 'TABLE',
                       p_object_alias => 'gifts',
                       p_auto_rest_auth => FALSE);
    commit;
END;
/
  8. Insert data 
    SET DEFINE OFF
INSERT INTO VB_DEMO.GIFTS (PRODUCT, COST, SKU, PICTURE)
VALUES ('USB Drive', 40, 1234, 'https://easydrive.com.my/wp-content/uploads/2015/08/Mini-Clip-USB-Flash-Drive-V1-Red.jpg');
INSERT INTO VB_DEMO.GIFTS (PRODUCT, COST, SKU, PICTURE)
VALUES ('Pen', 10, 1236, 'https://blogs.oracle.com/content/published/api/v1.1/assets/CONT81990C8032A24DB1833F3A04FFD142CE/Medium?cb=_cache_ee8b&format=jpg&channelToken=8c4b6a04814448d6bbe9b2bc6ba48c32');
INSERT INTO VB_DEMO.GIFTS (PRODUCT, COST, SKU, PICTURE)
VALUES ('Notebook', 20, 1238, 'https://blogs.oracle.com/content/published/api/v1.1/assets/CONT50B4BD64848547B8A3AC5D3C3D147B23/Medium?cb=_cache_ee8b&format=jpg&channelToken=8c4b6a04814448d6bbe9b2bc6ba48c32');
INSERT INTO VB_DEMO.GIFTS (PRODUCT, COST, SKU, PICTURE)
VALUES ('USB Charger', 30, 1240, 'https://cdna.4imprint.com/prod/300/458202.jpg');
INSERT INTO VB_DEMO.GIFTS (PRODUCT, COST, SKU, PICTURE)
VALUES ('USB Drive', 40, 1242, 'https://easydrive.com.my/wp-content/uploads/2015/08/Mini-Clip-USB-Flash-Drive-V1-Red.jpg');
INSERT INTO VB_DEMO.GIFTS (PRODUCT, COST, SKU, PICTURE)
VALUES ('Pen', 10, 1244, 'https://blogs.oracle.com/content/published/api/v1.1/assets/CONT81990C8032A24DB1833F3A04FFD142CE/Medium?cb=_cache_ee8b&format=jpg&channelToken=8c4b6a04814448d6bbe9b2bc6ba48c32');
INSERT INTO VB_DEMO.GIFTS (PRODUCT, COST, SKU, PICTURE)
VALUES ('Notebook', 20, 1246, 'https://blogs.oracle.com/content/published/api/v1.1/assets/CONT50B4BD64848547B8A3AC5D3C3D147B23/Medium?cb=_cache_ee8b&format=jpg&channelToken=8c4b6a04814448d6bbe9b2bc6ba48c32');
INSERT INTO VB_DEMO.GIFTS (PRODUCT, COST, SKU, PICTURE)
VALUES ('USB Charger', 30, 1248, 'https://cdna.4imprint.com/prod/300/458202.jpg');
INSERT INTO VB_DEMO.GIFTS (PRODUCT, COST, SKU, PICTURE)
VALUES ('USB Drive', 40, 1250, 'https://easydrive.com.my/wp-content/uploads/2015/08/Mini-Clip-USB-Flash-Drive-V1-Red.jpg');
INSERT INTO VB_DEMO.GIFTS (PRODUCT, COST, SKU, PICTURE)
VALUES ('Pen', 10, 1252, 'https://blogs.oracle.com/content/published/api/v1.1/assets/CONT81990C8032A24DB1833F3A04FFD142CE/Medium?cb=_cache_ee8b&format=jpg&channelToken=8c4b6a04814448d6bbe9b2bc6ba48c32');
INSERT INTO VB_DEMO.GIFTS (PRODUCT, COST, SKU, PICTURE)
VALUES ('Notebook', 20, 1254, 'https://blogs.oracle.com/content/published/api/v1.1/assets/CONT50B4BD64848547B8A3AC5D3C3D147B23/Medium?cb=_cache_ee8b&format=jpg&channelToken=8c4b6a04814448d6bbe9b2bc6ba48c32');
INSERT INTO VB_DEMO.GIFTS (PRODUCT, COST, SKU, PICTURE)
VALUES ('USB Charger', 30, 1256, 'https://cdna.4imprint.com/prod/300/458202.jpg');
INSERT INTO VB_DEMO.GIFTS (PRODUCT, COST, SKU, PICTURE)
VALUES ('Mug', 25, 3322, 'https://i.ebayimg.com/images/g/M48AAOSwgyxWW5M9/s-l640.jpg');
commit;
  9. Exit SQLcl 
    exit