Cross-Database Queries with Oracle in API Maker — Find & Join Across Cross Database Engines

Overview

API Maker’s Find & Join feature lets you seamlessly connect Oracle with any other supported database and perform real-time, schema-driven joins with advanced conditional filtering — all without writing complex SQL queries or maintaining ETL pipelines.

Whether you need to combine Oracle sales data with customer profiles from MySQL, or merge Oracle purchase orders with live inventory from MongoDB, Find & Join delivers fast, low-code, and scalable results.

This makes it an ideal solution for teams managing multi-database architectures, enabling you to:

Run a single query that pulls Oracle data alongside MySQL, PostgreSQL, MongoDB, SQL Server, MariaDB, or any other supported database — all in one response.
Use cross-database conditional filters to instantly refine results based on fields from either side of the join.
Get live, up-to-date data every time — no redundant copies, no outdated snapshots.

Why Use Find & Join with Oracle?

Query multiple databases in one call— Run a single API request that combines Oracle data with MySQL, PostgreSQL, MongoDB, SQL Server, MariaDB, or any other supported source, without juggling multiple service calls.

Smart, schema-aware joins— API Maker uses configured table schema relationships to understand how Oracle data connects to other databases, so you don’t need to manually write or maintain complex JOIN statements.

Always live, real-time results— Data is pulled directly from Oracle and the connected source at query time, avoiding stale ETL snapshots or outdated exports.

Low-code and developer-friendly— Define queries using simple JSON request objects; no deep SQL knowledge required.

Connecting Oracle in API Maker

API Maker connects to Oracle databases using a secure connection string, managed through the platform’s Secret Manager. This ensures sensitive details — such as the host, port, service name/SID, username, password, and optional privilege level (e.g., SYSDBA) — are stored safely and never exposed in plain text.

If you’re new to setting up connection strings, see the Database Connection String reference for syntax, supported parameters, and examples. A typical Oracle connection string might look like:

oracle: "localhost:1521/service",
oracle_username: "sys",
oracle_password: "PASSWORD",
oracle_privilege: "SYSDBA"

Once the connection is active, API Maker automatically:

Fetches the Oracle schema, mapping tables, columns, and relationships.
Enables Oracle to participate in cross-database joins with MySQL, PostgreSQL, MongoDB, SQL Server, and other supported databases.
Generates ready-to-use APIs for querying Oracle data.

For details on how these APIs are generated and for exploring the endpoints, see the Auto Generated APIs and Schema APIs documentation.

Example: Joining Oracle Exam Results with MySQL Student Profiles

Use Case:
Student Data is stored in an Oracle database, while Exam results are kept in a MySQL system. You need to fetch the top 5 students who scored above 85, along with their names and departments.

Schema Setup in Oracle Table (students):

student_id: <ISchemaProperty> {
  __type: EType.number,
  instance: "MySQL",
  database: "students",
  table: "exam_results",
  column: "id"
}

Here, the student_id field in Oracle is linked to the id column in the MySQL exam_results table.
When you reference student_id.score in a query, API Maker uses this schema mapping to know that:

It must first match student_id values in Oracle’s students table with the id field in MySQL’s exam_results table.
Then, it can fetch the score field from MySQL for the matching students and apply the condition (e.g., > 85).

Request Endpoint:

POST /api/find/students

REST Request Example:

{
  "find": {
    "student_id.score": { "$gt": 85 }
  },
  "deep": {
    "s_key": "student_id"
  },
  "limit": 5,
  "sort": { "score": -1 }
}

How It Works

The student_id field in students is mapped (via the schema) to the id column in the MySQL exam_results table.
When the request is executed, API Maker first looks up the matching student IDs in the MySQL database and retrieves their profile fields (such as full_name and department).
The deep object tells API Maker to retrieve nested MySQL data based on this relationship.
The join is performed automatically in real time — no SQL JOIN statements or ETL pipelines are required.

Additional capabilities

Request and response caching can speed up repeated queries — see Automatic Caching for details.
To retrieve additional nested fields from other databases during joins as given in the above example, refer to the Deep Populate documentation.

Performance Tips

Index your join keys
Ensure that the columns used for linking datasets (e.g., student_id, id) are indexed in both Oracle and the linked database to speed up join resolution.

Project only what you need
Use the select option to return only the required fields instead of full records — it reduces payload size and improves query speed.

Paginate large results
Apply limit and skip to handle large datasets efficiently, keeping API responses fast and lightweight.

Keep joins shallow when possible
Deep or multi-level joins are powerful but can increase latency. Only retrieve deeply nested data when it’s truly necessary.

FAQ's

1. Can Oracle be joined with MongoDB in API Maker?

Yes. Oracle tables can be joined with MongoDB collections in the same way as with relational databases. A schema reference defines the relationship, and API Maker automatically translates the query across systems.

2. Is it necessary to write SQL JOIN queries manually?

No. Once schema relationships are configured, joins are executed automatically. A REST request with the required filters, projections, and limits is sufficient.

3. Is data duplicated during joins?

No. Find & Join retrieves live data directly from Oracle and the linked database. There is no intermediate storage, ETL process, or duplication involved.

4. Can Oracle data be filtered using fields from another database?

Yes. Dot-notation (.score) can be used in filters to reference fields from the joined dataset.
For example, the students table in Oracle can be filtered using students.score from a MongoDB exam_results collection.

5. How many databases does API Maker support?

API Maker supports 8 database types, including MongoDB, Oracle, Microsoft SQL Server, MySQL, MariaDB, PostgreSQL, TiDB and Percona XtraDB. All can participate in cross-database joins when schema relationships are defined.

Real-Time Cross-Database Find & Join and Conditional Filtering with Oracle in API Maker