Access Data in an Existing Oracle Database Using ORDS

If you're looking 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 VB Studio. Alternatively, you can connect to Oracle REST Data Services (ORDS).

Here's a brief rundown to help you choose between business objects and ORDS:

Business objects are fully integrated into VB Studio, making it fairly easy to create business rules, relationships, REST services, and so on. You can also apply the same security roles defined in VB Studio to business objects to protect them. Further, 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 VB Studio. 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.

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:

Setting your service connection's Connection Type to Dynamic, the service supports CORS indicates that your ORDS endpoint has CORS support for the VB Studio domain and the environment's Visual Builder domain; in other words, the domains from where the ORDS REST API is being called (for example, https://sample-devcs.developer.oraclecloud.oracle.com and https://sample.builder.oraclecloud.com) are added to the endpoint's list of allowed origins. If there's a typo or a mismatch in the CORS configuration at the ORDS endpoint, you're likely to run into this error:

The REST API that you are connecting to doesn't seem to have Cross Origin Request sharing (CORS) for the Visual Builder domain. 
Try again after adding necessary CORS settings for the REST API you are connecting to.

To enable an ORDS endpoint to support CORS, see the About Browser Origins example in ORDS documentation.
If your ORDS endpoint doesn't have CORS support for the Visual Builder domain, set the Connection Type to Always use proxy, irrespective of CORS support. See Use an Appropriate Connection Type to Handle CORS for REST Services.

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

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.

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.

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.

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;

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>

begin oauth.create_client(
      p_name => 'Client Credentials Example',
      p_grant_type => 'client_credentials',
      p_privilege_names => 'example.employees',
      p_support_email => 'support@example.com');
 commit;end;

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;

Check the registered client ID and secret:

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

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.