Access Data in an Existing Oracle Database Using ORDS

If you're looking to expose data from existing Oracle databases, you can use your own database schema to create business objects in VB Studio. You can also 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 VB Studio.

Map ORDS Endpoints in VB Studio

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 VB Studio 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 VB Studio. 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.