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.

{Update June 2020 - You can improve the integration between ORDS and VB with the new OpenAPI support covered in this blog}

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 : 2:00.

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 ( 6 )
  • 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
  • Bhavesh Wednesday, January 20, 2021
    Hi Shay,
    Thanks for this great tutorial. Running into an issue with pagination. Copied transform JavaScript code as it is. The behavior I am observing is that it loads all rows at the same time even though ORDS REST is paganiation enabled (25 rows). I was expecting a behavior like loadMoreOnScroll where it calls next set of rows only when scroll bar reaches the end.

    It would be great if you could provide a working example of loadMoreOnScroll behavior with ORDS REST.
  • Shay Shmeltzer Sunday, January 31, 2021
    Bhavesh, technical questions should go onto our forum - https://cloudcustomerconnect.oracle.com/resources/e610f4723c/summary
    You need to set properties on the table/list to tell it to do pagination see- https://blogs.oracle.com/shay/table-pagination-and-scrolling-in-visual-builder-explained
  • Nikolay Wednesday, March 3, 2021
    Hi,

    The transform functions do not seem to work for filtering. I added a Select Single component to a page, and configured it to use options from an ORDS service. The search in the LOV does not seem to work, when I enter data in the LOV it does not filter out records that do not match the criteria. However the pagination seems to work. Was something changed in ORDS/transform?

    Regards,
    Nikolay
  • shay Wednesday, March 3, 2021
    Nikolay - check the network tab in your browser to see what format of query does the selectSingle send to the ORDS layer.
    Then if you can't figure out what is wrong - try posting your findings on our forum 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.