X

The Visual Builder Cloud Service Blog

Getting Started with ORDS and Oracle DB/ATP Access in Oracle Visual Builder

Shay Shmeltzer
Director of Product Management - Oracle

Many of our customers are looking to expose data from existing Oracle databases in Visual Builder applications. In order to do this, you'll need to expose a layer of REST services on top of the database so Visual Builder could access and manipulate the data. One approach for doing this is to use ORDS (Oracle REST Data Services) - which provide a declarative way to achieve such exposure. In this blog we'll show you some of the basics of creating this connection.

In the video below we cover a simple flow from creating the table to exposing it with ORDS and consuming in Visual Builder.

The first step is to expose a table through REST services, SQL Developer provides a nice wizard to help you achieve this. Simply right click a table and choose to REST enable it. When you do this you'll also choose the endpoint name for this specific table.

Once you have that end point, you'll usually append it to a URL that has your DB Server name followed by ords/schemaName - so something like: https://yourDBServer/ords/yourschema/yourtable/

In Visual Builder you can add this endpoint in the service connection wizard - you can then define multiple endpoints that will map to each one of the REST operations such as GET, POST, DELETE etc.

Once you have the GET operation you can hook it up to your pages and for example base a table on it - as shown in the video at : XXX.

Transform Functions

The ORDS services have built in capabilities to do advance functionality including operations such as query by example, sorting, and pagination (fetching x amount of records at a time). You'll need to tell Visual Builder how to use those - and the way to achieve this is through the transforms tab in your service definition.

Transform functions let VB know how to transform the info generated from filterCriterion, sort and pagination to the format expected by the REST service you are calling. When you click the checkbox, we'll create a sample piece of code that you'll need to adapt for your specific REST service. Once you define such a transformation VB will know how to do more advance things with your service - for example filter a serviceDataProvider variable using the filterCriterion approach and sort and paginate a table based on this ServiceDataProvider.

Transform Function

The transform functions are generic for ORDS and don't depend on a specific table or service. Below I'm including the transform function I used in the video (which was developed by David Konecny from our VB team). Note that it doesn't cover all the operations available in filterCriterion - so you might need to add some code in there to handle other operators (around row 29).

define(['urijs/URI'], function (URI) {
    'use strict';
 
    var Request = function () {
    };
 
    /**
     * Filter transform function
     * @param configuration
     * @param options the JSON payload that defines the filterCriteria
     * @returns {object} configuration object the url looks like ?filter=foo eq 'bar'.
     */
    Request.prototype.filter = function(configuration, options) {
        var filterCriterion = options;
        if (filterCriterion === undefined) {
          return configuration;
        }
        var operation;
        var criteria = filterCriterion.criteria;
        if (criteria === undefined || criteria.length === 0) {
          if (filterCriterion.attribute && filterCriterion.op && filterCriterion.value) {
            criteria = [filterCriterion];
            operation = "$and";
          } else {
            return configuration;
          }
        }

        function jetFilterOpToScim(fop) {
            switch (fop) {
                case '$co':
                    return '$like';
                case '$le':
                    return '$lte';
                case '$ge':
                    return '$gte';
                default:
                    return fop;
            }
        }
 
        function isEmpty(val) {
            return (val === undefined || val === null || val === '');
        }
 
        if (filterCriterion && criteria && criteria.length > 0) {
          var q = [];
          criteria.forEach(function(crit) {
            if (crit.value === undefined || crit.value === '%%' || crit.value.length === 0) {
              return;
            }
            var v = {};
            if (crit.op === '$co') {
              v[jetFilterOpToScim(crit.op)] = '%'+crit.value+'%';
            } else {
              v[jetFilterOpToScim(crit.op)] = crit.value;
            }
            var vv = {};
            vv[crit.attribute] = v;
            q.push(vv);
          });
          if (q.length > 0) {
            var query = {};
            if (operation === undefined) {
              operation = filterCriterion.op;
            }
            if (operation === undefined) {
              operation = "$and";
            }
            query[operation] = q;
            // ORDS query URL is for example:
            // ...ords/hr/emp?q={"$or":[{"ename":{"$like":"%david%"}},{"job":{"$like":"%developer%"}}]}
            configuration.url = URI(configuration.url).addQuery({ q : JSON.stringify(query)}).toString();
          }
        }
 
        return configuration;
    };
 
    /**
     * Pagination function appends limit and offset parameters to the url
     * @param configuration
     * @param options the JSON payload that defines the pagination criteria
     * @returns {object} configuration object.
     */
    Request.prototype.paginate = function(configuration, options) {
        var newUrl = configuration.url;
        if (options && options.size) {
          newUrl = URI(newUrl).addSearch({limit: options.size, offset: options.offset}).toString();
        }
        configuration.url = newUrl;
        return configuration;
    };
 
    /**
     * Sort transform function
     * @param configuration
     * @param options the JSON payload that defines the sort criteria
     * @returns {object} configuration object. the url looks like ?orderBy=foo:asc
     */
    Request.prototype.sort = function(configuration, options) {
        if (Array.isArray(options) && options.length > 0) {
            var firstItem = options[0];
            if (firstItem.attribute) {
                var dir = firstItem.direction === 'descending' ? 'DESC' : 'ASC';
                var newUrl = configuration.url;
                var sort = '"$orderby":{"'+firstItem.attribute+'":"'+dir+'"}';
                var query = URI(newUrl).search(true);
                if (query.q) {
                  query.q = '{'+sort+','+query.q.substr(1);
                } else {
                  query.q = '{'+sort+'}';
                }
                // ORDS sort URL is for example:
                // ...ords/hr/emp?q={"$orderby":{"sal":"ASC"}}
                // BUT: sorting is applied after filter() method above so sorting
                // needs to be inserted into existing q param if filtering is on
                newUrl = URI(newUrl).search(query).toString();
                configuration.url = newUrl;
            }
        }
        return configuration;
    };
 
    var Response = function() {
 
    };
 
    /**
     * Paginate Response Transform Function Implementation
     */
    Response.prototype.paginate = function(result) {
        var tr = {};
 
        if (result && result.body) {
            var cb = result.body;
            // ORDS does not support "totalCount" but only "hasMore"
            tr.hasMore = cb.hasMore;
        }
        return tr;
    };
 
    return {
        request: Request,
        response: Response
   };
});

Some other things you might need to take care off are - having a valid SSL certificate for your ORDS - we have a blog that covers this here.

You will also need to decide how are you securing your ORDS and providing authentication - for example using OAuth2.

Join the discussion

Comments ( 2 )
  • Baig Friday, November 1, 2019
    Thank you Shay for the nice post.

    I understand that ORDS is one of the option for exposing custom database objects as Rest service.

    But we need your help to identify the best option to expose the custom database objects or custom functionality as a Rest service which can be consumed in VBCS application.

    1. ADF BC Rest - We don't have JCS at moment but if its best option we can get back to customer.
    2. OIC Integrations as Rest - Already subscribed along with VBCS
    3. ORDS - We have custom database of size 15gb

    FYI, we have already developed an application which is in production now, where we used Oracle JET and ADB BC combination for another customer.

    For this new customer, we have adapted the VBCS and looking for best option to expose Rest services for custom db objects/functionality.

    Appreciate your response with cons and pros for each of the above approaches Or any other approach apart from the above three.

    Thank you
  • shay Friday, November 1, 2019
    Baig - A thread discussing this here - https://cloudcustomerconnect.oracle.com/posts/7317e38c93
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.