ORDS Connector - Setup Instructions 📋 Prerequisites
Oracle Database Requirements
Before using this plugin, your Oracle database must be configured with ORDS:

1. ORDS Installed (version 19.2 or higher)

2. RESTful Services Enabled for your schema

3. OAuth Client Registered with client credentials

4. Tables Exposed via REST endpoints

Sample Application
A complete sample Bubble app is available that demonstrates all functionality:

Editor Link

https://bubble.io/page?id=oracle-ords-connector&test_plugin=1773661929182x860700353080066000_current&tab=Design&name=index

Demo Link:
https://oracle-ords-connector.bubbleapps.io/version-test/index?debug_mode=true

View this app to see working workflows and UI examples

🔧 Oracle Configuration Steps
Step 1: Enable REST for Your Schema
Run this SQL in your Oracle database:

BEGIN
  ORDS.ENABLE_SCHEMA(
     p_schema => 'YOUR_SCHEMA_NAME',
     p_url_mapping_type => 'BASE_PATH',
     p_url_mapping_pattern => 'your_schema/',
     p_auto_rest_auth => TRUE
  );
  COMMIT;
END;


Step 2: Create OAuth Client (Using ORDS Administration Interface)

In Oracle Cloud environments (such as Autonomous Database), OAuth clients are typically created using the ORDS Administration UI, not the OAUTH.CREATE_CLIENT PL/SQL procedure.

Steps

1. Open your ORDS Administration interface:

https://your-server/ords/_/landing

Example:

https://your-server.oraclecloudapps.com/ords/_/landing
https://gb95ddd6cd00382-nadeematp.adb.me-dubai-1.oraclecloudapps.com/ords/sql-developer

2. Login using your database schema credentials.

3. Navigate to:

REST Services
  → Security
       → OAuth Clients

4. Click Create OAuth Client

5. Fill in the following fields:

Field Example
Name Bubble App Client
Grant Type Client Credentials
Privileges Select or create privilege
Owner Your schema
Support Email [email protected]

6. Click Create

7. Oracle will generate:

Client ID
Client Secret

Save these credentials — they are required in Bubble.

Step 2A: Create OAuth Client (for self-hosted ORDS Servers)

BEGIN
  OAUTH.CREATE_CLIENT(
     p_name => 'Bubble App Client',
     p_grant_type => 'client_credentials',
     p_privilege_names => 'your.privilege.name',
     p_support_email => '[email protected]',
     p_owner => 'APP_OWNER'
  );
  COMMIT;
END;

Save the generated Client ID and Client Secret - you'll need them in Bubble.

Step 3: Enable REST for Your Tables
For each table you want to access:

BEGIN
  ORDS.ENABLE_OBJECT(
     p_object => 'YOUR_TABLE_NAME',     -- e.g., 'EMPLOYEES'
     p_object_type => 'TABLE',
     p_object_alias => 'table_alias'     -- e.g., 'emp'
  );
  COMMIT;
END;

Step 4: Configure CORS (Optional – Required Only for Browser Calls)

BEGIN
  ORDS.CONFIGURE_CORS(
     p_pattern => '*',  -- For development
     p_enabled => TRUE
  );
  COMMIT;
END;

For production, replace '*' with your Bubble app URL.

Step 5: Test Your Configuration
Test the token endpoint using cURL:

curl -X POST https://your-server/ords/your_schema/oauth/token \
 -H "Content-Type: application/x-www-form-urlencoded" \
 -d "grant_type=client_credentials" \
 -u "client_id:client_secret"

Test a table endpoint:

curl -X GET https://your-server/ords/your_schema/table_alias/ \
 -H "Authorization: Bearer YOUR_ACCESS_TOKEN"

🚀 Bubble Setup Guide
Plugin Actions
Action 1: Get Token
Authenticates and retrieves access token

Input Parameters:

Field Description Example
host Your ORDS server hostname your-server.oracle.com
schema Oracle schema name hr
endpoint Token endpoint oauth/token
client_id From OAuth client q7w8e9r0t1y2...
client_secret From OAuth client u3i4o5p6a7s8...
Returns:

access_token: Use this in subsequent API calls

expires_in: Token lifetime in seconds

Action 2: Manage Table Data
Perform CRUD operations on your tables

Input Parameters:

Field Description Example
host Your ORDS server hostname your-server.oracle.com
schema Oracle schema name hr
endpoint Table endpoint employees/ or employees/101
method HTTP method GET, POST, PUT, DELETE
access_token From Get Token action eyJhbGciOiJ...
body JSON for POST/PUT {"name":"John"}
Endpoint Examples:

GET all: employees/

GET one: employees/101

POST new: employees/ (with body)

PUT update: employees/101 (with body)

DELETE: employees/101

Returns:

data: Array of records formatted for Repeating Groups

success: Boolean indicating success/failure

status: HTTP status code

error: Error details if any

💡 Sample Workflows
Basic Workflow: Fetch Records

The workflow is triggered when the user clicks the Fetch Data button.

Step 1 – Get Access Token (Conditional)

Action: getToken (testing)

Condition:
Run only when:

Current User's api_token is empty, or

Current User's token_expiry < Current date/time

Purpose

This step ensures the system has a valid OAuth access token before calling the ORDS API.

If the token is missing or expired, the workflow calls the getToken API to request a new access token from the authentication endpoint.

Step 2 – Store Token in User Record

Action: Make changes to current user

Condition:
Run only when:

Result of step 1's access_token is not empty

Purpose

Once a token is returned, the system stores it in the Current User's fields, typically:

api_token

token_expiry

This allows subsequent API calls to reuse the token without requesting a new one each time.

Step 3 – Fetch Table Data

Action: manageTableData (testing)

Condition:
Run only when:

Current User's api_token is not empty

Purpose

This step calls the ORDS endpoint to retrieve the requested data.

The API request includes the stored token in the Authorization header, for example:

Authorization: Bearer <api_token>

The API returns the dataset which will be used to populate the interface.

Step 4 – Store API Response in Page State

Action:
Set state table_data... of main

Purpose

The returned dataset is stored in a custom state on the page (for example table_data).

Using a custom state allows the application to:

Temporarily hold API results

Transform or filter data if needed

Pass the data to UI elements such as repeating groups

Step 5 – Display Data in Repeating Group

Action:
Display list in RepeatingGroup

Purpose

The stored data (table_data) is bound to a Repeating Group element.

Each row returned from the API becomes a cell in the repeating group, allowing the user to view the results directly in the interface.

Workflow Summary

1. The complete process works as follows:
2. User clicks Fetch Data
3. System checks if a valid API token exists
4. If needed, a new token is requested
5. Token is stored in the user record
6. ORDS API is called using the token
7. Results are saved in page state
8. Data is displayed in a Repeating Group

Create New Record

1. User submits form
  ↓
2. Run "Get Token" action
  ↓
3. Run "Manage Table Data" (method: POST)
     access_token = Step 2's access_token
     body = Form data as JSON
  ↓
4. Show success message or refresh list

Update Record

1. User edits and saves
  ↓
2. Run "Get Token" action
  ↓
3. Run "Manage Table Data" (method: PUT)
     endpoint = "table_name/" + record_id
     access_token = Step 2's access_token
     body = Updated data as JSON
  ↓
4. Confirm update and refresh

Delete Record

1. User confirms deletion
  ↓
2. Run "Get Token" action
  ↓
3. Run "Manage Table Data" (method: DELETE)
     endpoint = "table_name/" + record_id
     access_token = Step 2's access_token
  ↓
4. Remove from display

Additional Query Options for Sorting and Limiting Results (only used in GET method of API call)

When using the Manage Table Data action with the GET method, the plugin provides additional input fields that allow you to:

• Sort the returned records
• Limit the number of rows returned from Oracle

These fields internally translate to ORDS query parameters.

Available Fields
Field Description Example
order_by Column name used for sorting salary
order_direction Sorting direction asc or desc
limit Maximum number of rows to return 20
1. Sorting Records

You can sort the results by specifying the column name in order_by.

Example:

Field Value
order_by salary
order_direction desc

Result: employees are returned from highest salary to lowest.

2. Ascending Sort Example
Field Value
order_by first_name
order_direction asc

Result: employees sorted alphabetically by name.

If order_direction is not provided, ascending order (ASC) is used by default.

3. Limiting the Number of Records

You can restrict the number of returned rows using the limit field.

Example:

Field Value
limit 10

Result: only 10 rows will be returned.

4. Combining Sorting and Limit

These parameters can be used together.

Example:

Field Value
order_by salary
order_direction desc
limit 5

📚 API Reference
Base URL Structure

https://{host}/ords/{schema}/{endpoint}

HTTP Methods & Use Cases
GET: Retrieve record(s)

POST: Create new record

PUT: Update existing record

DELETE: Remove record

Response Format

{
 "items": [{"id": 1, "name": "John"}, ...],  // Array of records
 "hasMore": false,                            // Pagination flag
 "count": 10                                   // Total count
}


# Displaying Oracle Data in Bubble

The **Manage Table Data** action returns the response from ORDS in JSON format.
This data can be displayed in Bubble using a **Repeating Group**.

Typical ORDS response format:

```json
{
 "items": [
   {"employee_id":101,"first_name":"John","salary":5000},
   {"employee_id":102,"first_name":"Sara","salary":6000}
 ],
 "count":2
}
```

The plugin returns the **items array** as `data`.

---

# Step 1 — Add a Repeating Group

In the Bubble page:

1. Drag a **Repeating Group** onto the page
2. Set **Type of content** to:

```
text
```

3. Leave the data source empty for now.

---

# Step 2 — Fetch Data Using Workflow

Create a workflow such as:

```
Button Click
    ↓
Get Token
    ↓
Manage Table Data (method = GET)
```

Example endpoint:

```
employees/
```

---

# Step 3 — Display the Result in Repeating Group

Add a workflow action:

```
Element Actions → Display List
```

Configure it as:

| Field                      | Value                                    
| ---------------           | -----------------------------------------
| Element               | Repeating Group                          
| Data to display   | Result of step "Manage Table Data"'s data

This populates the repeating group with the returned records.

---

# Step 4 — Display Individual Columns

Since each record is JSON text, you can extract specific values using Bubble's **Extract with Regex** function.

Inside the Repeating Group:

1️⃣ Add a **Group for each column and with in it a Text element**

Example expression:

```
Current cell's text:extract with Regex
```

Example regex:

| Column          | Regex                    
| -----------          |  ------------------------
| employee_id | `"employee_id":(\d+)`    |
| first_name     | `"first_name":"([^"]+)"` |
| salary             | `"salary":(\d+)`         |

---

# Example Layout

```
Repeating Group
  ├── Group (Employee ID)
  ├── Group (First Name)
  └── Group (Salary)
```

Each group extracts the corresponding value from the JSON record.

---

# Alternative: Use the Sample Application

Because Bubble workflows can be difficult to configure correctly, a **sample Bubble application** is provided.

The sample app demonstrates:

• Getting an OAuth token
• Fetching records from Oracle
• Displaying data in a repeating group
• Extracting column values using regex
• Creating, updating, and deleting records

You can import the sample app to see a **complete working implementation**.

🔐 Security Best Practices
Never expose Client Secret in client-side workflows

Use API Workflows for sensitive operations

Implement IP whitelisting on ORDS server

Rotate OAuth credentials periodically

Use HTTPS for all communications

Set appropriate CORS policies - restrict to your Bubble domain

🆘 Support
Sample App: Import the provided sample app for working examples

Debug Mode: Enable browser console for detailed logs

Common Issues: Check the troubleshooting table above

Contact: [email protected]