Access Data in an Existing Oracle Database Using ORDS

If want to expose data from existing Oracle databases, you’ll need to expose the database tables and views through a REST layer. To do this, you can use your own database schema to create business objects and service connections in Visual Builder. Alternatively, you can connect to Oracle REST Data Services (ORDS), which makes it easy to expose your tables and other database objects as REST services.

To expose your data through ORDS, you must first enable ORDS access for tables and views in the schema associated with your database connection. If you're using Oracle SQL Developer, you can enable REST access either to the schema or to the individual table or view. See Automatically Enabling REST Access to a Schema, Table, or View (AutoREST) in Oracle SQL Developer User's Guide for more information.

Note:

If your database is in a private subnet or behind a firewall, your ORDS endpoint must have a public IP address to be accessible by Visual Builder.

Choose Between Business Objects and ORDS

Business objects are fully integrated into Visual Builder, making it fairly easy to create business rules, relationships, REST services, and so on. In addition, you can apply the same security roles defined in Visual Builder to business objects to protect them. Finally, business objects automatically provide REST API transforms (vb/BusinessObjectTransforms) for filtering, sorting, and pagination.

With ORDS, hosting and creating the REST service layer is done outside of Visual Builder. By enabling ORDS on your database, you can use the ORDS application—along with any other supported applications needed— to create REST services. (See Getting Started with Oracle REST Data Services for examples of using your version of ORDS with other Oracle applications). Unlike business objects, ORDS uses its own security roles and privileges, and you must create your own filter, sort, and pagination transform functions. The benefit of ORDS, however, is that it lets you take full advantage of Oracle DB SQL and PL/SQL capabilities to create a middle layer with the business logic you need. ORDS also enables you to use more database features, such as BLOB/CLOB fields, functions, and procedures.

Map ORDS Endpoints in Visual Builder

When your database table or view is REST-enabled, you can use either the individual endpoint or the OpenAPI catalog to map the ORDS endpoint as a REST service in your application.

• To map the individual endpoint, you need the endpoint URL, which may be something like https://serverName/ords/schemaName/tableName/. Now create a service connection from this endpoint. You may need to define multiple endpoints that map to each REST operation (GET, POST, DELETE, etc.)
• To map the OpenAPI specification, which allows you to create many endpoints all at once, add open-api-catalog to the object's URL, which may be something like https://serverName/ords/schemaName/open-api-catalog/tableName/. Now create a service connection using the OpenAPI specification. See this blog for a demo.

Note:

Make sure CORS settings for the ORDS endpoint and the service connection match; in other words, both ORDS and Visual Builder must have CORS enabled, or neither must have CORS enabled. For an example of how to configure an ORDS endpoint to support CORS, see About Browser Origins in ORDS documentation.

Sort, Query, and Paginate ORDS Data

To filter, sort, and paginate the data returned by the ORDS backend, you'll need to provide a transform function as part of your service connection definition in Visual Builder. Find sample code for the transform function in the Visual Builder cookbook's ORDS integration recipe.

Connect to ORDS APIs Using Fixed Credentials

To secure access to the ORDS endpoint using fixed credentials, you use OAuth 2.0 Client Credentials for authentication.

When creating the service connection, you can use the following authentication method for the service connection:

Authentication method	Details
OAuth 2.0 Client Credentials	This is the recommended authentication option. To use this option you need to provide the following details: Client ID and Secret. From ORDS Token URL. From ORDS, for example, https://example.com/ords/ordstest/oauth/token Scope. This is blank.

1. Before creating a connection to ORDS, a role and privilege to protect your REST service must be created and the OAuth client must be registered in the ORDS service. The following steps briefly describe this process; for detailed information, see Protecting and Accessing Resources in Developing Oracle REST Data Services Applications.
   1. Create a role and privilege to protect your REST service in ORDS:

      begin  ords.create_role('HR Administrator');
              ords.create_privilege(
            p_name => 'example.employees',
            p_role_name => 'HR Administrator',
            p_label => 'Employee Data',
            p_description => 'Provide access to employee HR data');
        commit;end;

   2. Associate the privilege with resources (i.e. your ORDS REST APIs):

      begin ords.create_privilege_mapping(
            p_privilege_name => 'example.employees',
            p_pattern => '/examples/employees/*');
            commit;end;

      Accessing the /example/employees REST resource should now result in a 401 unauthorized as shown here:

      curl -i https://example.com/ords/ordstest/examples/employees/
      HTTP/1.1 401 Unauthorized
      Content-Type: text/html
      Transfer-Encoding: chunked
        
      <!DOCTYPE html>
      <html>
      ...
      </html>

   3. Register the OAuth client with grant type Client Credentials:

      begin oauth.create_client(
            p_name => 'Client Credentials Example',
            p_grant_type => 'client_credentials',
            p_privilege_names => 'example.employees',
            p_support_email => '[e-mail]');
       commit;end;

   4. Grant this newly created client the required role:

      begin oauth.grant_client_role(
            p_client_name => 'Client Credentials Example',
            p_role_name => 'HR Administrator' );
       commit;
      end;

   5. Check the registered client ID and secret:

      select client_id,client_secret from user_ords_clients where name = 'Client Credentials Example';

2. Now create a connection to ORDS using fixed credentials:
   1. Open Services in the Navigator, click the + sign, and select Service Connection.
   2. In the Create Service Connection wizard, click Define by Endpoint, select the HTTP method and enter the URL of the ORDS endpoint.
      You can choose Define by Specification and use the URL of the ORDS' OpenAPI specification for your object to define the service connection for multiple endpoints.
   3. In the Authentication section of the Server tab, select OAuth 2.0 Client Credentials as the authentication method.
   4. Provide the details for the Client Id, Secret, and Token URL fields based on your ORDS configuration.
   5. Test the service connection.