Join unlike systems without pretending they are the same database.

Execution model

A query that targets one connected source can execute directly on that remote system. When a workflow spans unlike sources, FedCLI can stage source rows into an execution workspace for the final join, filter, or aggregation.

Create the federated links first

A federated link is a saved connection definition with an alias. Use a letter-led alias containing only letters, numbers, and underscores; FedCLI stores it in uppercase. The default command validates the connection before it reports success.

CREATE FEDERATED DATABASE ERP_ORACLE
AS CONNECT 'jdbc:oracle:thin:@//erp-db.example.com:1521/erp'
USER fedcli_reader
IDENTIFIED BY 'replace-with-runtime-secret';

CREATE FEDERATED DATABASE FINANCE_ORACLE
AS CONNECT 'jdbc:oracle:thin:@//finance-db.example.com:1521/finance'
USER fedcli_reader
IDENTIFIED BY 'replace-with-runtime-secret';

Those aliases are now the ERP_ORACLE and FINANCE_ORACLE source names used by the homogeneous reconciliation query below. Use separate, least-privilege accounts for each source; never commit real passwords to a script or repository.

Links for unlike engines

Create each source with its provider-specific connection URL, then use the chosen alias after @ in SQL. For example, a MongoDB source and an AWS-authenticated DynamoDB source can be defined as follows:

CREATE FEDERATED DATABASE CRM_MONGODB
AS CONNECT 'mongodb://mongo.example.com:27017/crm'
USER fedcli_reader
IDENTIFIED BY 'replace-with-runtime-secret';

CREATE FEDERATED DATABASE BILLING_DYNAMODB
AS CONNECT 'dynamodb://us-west-2';

The DynamoDB link uses the AWS credential chain configured for the FedCLI runtime. For every provider, confirm the installed driver or plugin, network path, and source-specific authentication before creating the link.

Verify or defer validation

Inspect the saved aliases after creation. If the target is intentionally unavailable during setup, append NO VALIDATE; it saves the definition without opening a connection, so validate it before running a federated job.

SHOW FEDERATED DATABASES;
SHOW FEDERATED DATABASE ERP_ORACLE;

CREATE FEDERATED DATABASE CRM_MONGODB
AS CONNECT 'mongodb://mongo.example.com:27017/crm'
NO VALIDATE;

FedCLI keeps the catalog in its local state directory and encrypts the saved connection definition at rest. Back up its catalog and matching key together when the links are operationally important.

Homogeneous federation job: reconcile two Oracle estates

Federation can still be useful when the source engines share a dialect but are separate operational systems. This example compares open order records in an ERP Oracle target with invoice state in a finance Oracle target.

-- oracle-order-reconciliation.sql
SELECT e.ORDER_ID, e.STATUS AS order_status, f.STATUS AS invoice_status
FROM ORDERS@ERP_ORACLE e
JOIN INVOICES@FINANCE_ORACLE f ON e.ORDER_ID = f.ORDER_ID
WHERE e.STATUS = 'OPEN';

Both aliases represent Oracle systems, but the workflow crosses independently managed source boundaries. Where an environment already has an appropriate database-native link, compare it with the FedCLI workflow using the real security, network, and operational constraints rather than assuming one is always faster.

Heterogeneous federation job: correlate Oracle, MongoDB, and DynamoDB

For unlike engines, preserve each source's role and use the shared workflow for the final relationship.

-- customer-exposure-review.sql
SELECT o.ORDER_ID, c.name, d.account_tier
FROM ORDERS@ERP_ORACLE o
JOIN users@CRM_MONGODB c ON o.CUSTOMER_ID = c.customer_id
JOIN Accounts@BILLING_DYNAMODB d ON o.CUSTOMER_ID = d.CustomerId
WHERE o.STATUS = 'OPEN';

The alias names, table shapes, credentials, and access controls are specific to your environment. Validate each source query independently before turning a cross-source operation into a scheduled task.

Make either pattern a repeatable job

Keep one federated statement per versioned script, prove it interactively first, and then invoke it through the same CLI surface.

fedcli @oracle-order-reconciliation.sql
fedcli @customer-exposure-review.sql

Schedule the wrapper only after defining the permitted data scope, output destination, timeout, monitoring, and recovery action for that specific job.

Use a safe operator workflow

1. Connect to each intended target and inspect its schema or collection shape.
2. Start with bounded projections and filters rather than broad extraction.
3. Run the federated statement interactively and review the output.
4. Move the proven statement into a versioned script only when its source permissions and data volume are understood.

Next steps

Review the supported database guide for engine coverage and the automation guide before operationalizing a repeatable task.