Query Remote and Federated Data

10.5 Query Remote and Federated Data

Provides examples of how to use Select AI to generate SQL query for remote and federated data.

Topics:

Example: Use Select AI with Database Links to Query Another Autonomous AI Database
This example shows how to set up a Database Link from Autonomous AI Database to the source database and use Select AI to generate SQL from natural language prompts. Select AI uses the metadata from the source database to generate SQL.
Example: Use Select AI with Database Links to Query Non-Oracle Database
This example shows how Autonomous AI Database works as an Live AI Hub, formerly called AI Proxy Database, and uses Select AI to generate federated SQL that joins local Oracle data with remote PostgreSQL data. Autonomous AI Database support for Oracle-managed heterogeneous connectivity makes it easy to create database links to non-Oracle databases. The PostgreSQL database is the official, authoritative source for the data.
Example: Use Select AI with Cloud Links to Query Another Autonomous AI Database
This example shows how to use Cloud Links to access data stored in another Autonomous AI Database and query it using Select AI.
Example: Use External Table over Table Hyperlink with Select AI
This example shows how an Autonomous AI Database (consumer database) acts as an Live AI Hub, to query remote data hosted in another Autonomous AI Database (provider database) using an External Table over a Table Hyperlink.
Example: Use Federated Table with Select AI
This example shows how an Autonomous AI Database (consumer) uses Select AI to query a Federated Table that automatically connects to a remote Autonomous AI Database (provider). The (consumer database) acts as an Live AI Hub (formerly called AI Proxy Database) to query remote data hosted in another Autonomous AI Database (provider database).

Parent topic: Examples Using Select AI

10.5.1 Example: Use Select AI with Database Links to Query Another Autonomous AI Database

This example shows how to set up a Database Link from Autonomous AI Database to the source database and use Select AI to generate SQL from natural language prompts. Select AI uses the metadata from the source database to generate SQL.

Before You Begin

Review

Perform Prerequisites for Select AI
Download your cloud wallet credentials and upload to an Object Storage bucket:
- Download the wallet (cwallet.sso) from your source database through OCI Console or Cloud Shell. See Download Database Connection Information for more details.
- Upload the wallet file to an Object Storage bucket. See Creating an Object Storage Bucket for more details.

This example shows how to set up a Database Link (DB Link) in an Autonomous AI Database to securely connect with another Autonomous AI Database. However, you can create DB Links to non-Autonomous AI Databases and third-party databases. Database links enable Select AI to query across remote data sets without replicating data through a wallet, credentials, and linked views.

You first create a credential to store your username and password to authenticate the source database. Create a directory to store the wallet files used for authentication when you are connecting to another Autonomous AI Database. Download the source database wallet credentials using GET_OBJECT procedure. Create a secure Database Link from Autonomous AI Database to the source Autonomous Database. You then create views on the remote tables. Create an AI profile with object_list attribute specifying the views as JSON objects and include the view name directly in object_list because Select AI profiles do not recognize database link syntax. Finally, issue any NL2SQL Select AI actions such as runsql, showsql, explainsql, narrate, or chat. This example uses showsql.

--Create Cloud Credential (run in Autonomous AI Database)

BEGIN
DBMS_CLOUD.DROP_CREDENTIAL(credential_name => 'DB_LINK_CRED');
EXCEPTION WHEN OTHERS THEN NULL;
END;
/
BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'DB_LINK_CRED',
username => 'DB_USER',     -- Username on source database
password => '<password>'          -- Password for source database
);
END;
/

--Create Directory (run in Autonomous AI Database)

CREATE DIRECTORY dblink_wallet_dir AS 'DATA_PUMP_DIR';

--Prepare and Upload Source Database Wallet in Object Storage bucket and run in Autonomous AI Database:
BEGIN
DBMS_CLOUD.GET_OBJECT(
credential_name => 'DB_LINK_CRED',
object_uri => 'https://objectstorage.ca-toronto-1.oraclecloud.com/n/namespace-string/b/bucketname/o/data_folder/cwallet.sso/cwallet.sso',
directory_name => 'DBLINK_WALLET_DIR'
);
END;
/

--Create Database Link (Drop dblink if it exists) to Source Database (run in Autonomous AI Database)


BEGIN
DBMS_CLOUD_ADMIN.DROP_DATABASE_LINK(db_link_name => 'MY_DATA_LINK');
EXCEPTION WHEN OTHERS THEN NULL;
END;
/

BEGIN
DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK(
db_link_name => 'MY_DATA_LINK',
hostname => 'adb.<region>-1.oraclecloud.com',             -- Source database hostname
port => '1522',                                           -- Source database port
service_name => 'your_service_name.adb.oraclecloud.com',  -- Source database service
credential_name => 'DB_LINK_CRED',
directory_name => 'DBLINK_WALLET_DIR'
);
END;
/

--Create Views (run in Autonomous AI Database)

CREATE VIEW customer_view AS SELECT * FROM customer@MY_DATA_LINK;
CREATE VIEW streams_view AS SELECT * FROM streams@MY_DATA_LINK;

--Create an AI Profile (run in Autonomous AI Database)

BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'MY_AI_PROFILE',
attributes => JSON_OBJECT(
'provider' => 'openai',
'credential_name' => 'OPENAI_CRED',
'object_list' => JSON_ARRAY(
JSON_OBJECT('owner' => 'SELECT_AI_USER', 'name' => 'CUSTOMER_VIEW'),
JSON_OBJECT('owner' => 'SELECT_AI_USER', 'name' => 'STREAMS_VIEW')
)
)
);
END;
/

--Showsql test:

SELECT AI SHOWSQL how many customers are there;

--Run on Source Database

Copy the generated SQL, remove @MY_DATA_LINK and run the query on your source database to verify.

Parent topic: Query Remote and Federated Data

10.5.2 Example: Use Select AI with Database Links to Query Non-Oracle Database

This example shows how Autonomous AI Database works as an Live AI Hub, formerly called AI Proxy Database, and uses Select AI to generate federated SQL that joins local Oracle data with remote PostgreSQL data. Autonomous AI Database support for Oracle-managed heterogeneous connectivity makes it easy to create database links to non-Oracle databases. The PostgreSQL database is the official, authoritative source for the data.

Before You Begin

Review

Perform Prerequisites for Select AI
Use a PostgreSQL user that has read access to the target schema or table
Confirm network access from Autonomous AI Database to the PostgreSQL endpoint

Use Case Scenario

Autonomous AI Database contains CUSTOMER_REVENUE table.
PostgreSQL contains support_ticket_metrics table.
Select AI generates SQL from a natural language prompt that joins both tables.

Sample CUSTOMER_REVENUE table in Autonomous AI Database:

CREATE TABLE customer_revenue (
customer_id 	NUMBER 		NOT NULL,
customer_name 	VARCHAR2(100) 	NOT NULL,
region 		VARCHAR2(50) 	NOT NULL,
revenue_quarter VARCHAR2(7) 	NOT NULL,
revenue_amount 	NUMBER(15,2) 	NOT NULL,
CONSTRAINT customer_revenue_pk PRIMARY KEY (customer_id, revenue_quarter)
);

Sample support_ticket_metrics table in PostgreSQL:

CREATE TABLE support_ticket_metrics (
ticket_id 		BIGSERIAL 	PRIMARY KEY,
customer_id 		BIGINT 		NOT NULL,
severity 		VARCHAR(20) 	NOT NULL,
opened_at 		TIMESTAMP 	NOT NULL,
resolved_at 		TIMESTAMP,
resolution_time_hours 	NUMERIC(10,2)
);

Create a credential that stores the PostgreSQL username and password.

BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'POSTGRESQL_CRED',
    username        => 'app_user',
    password        => '<postgresql_password>'
  );
END;
/

Create a heterogeneous database link to PostgreSQL. This example uses gateway_params to set the database type and SSL. See Create Database Links to Non-Oracle Databases with Oracle-Managed Heterogeneous Connectivity for more details.

BEGIN
  DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK(
    db_link_name        => 'POSTGRESQL_LINK',
    hostname            => 'primary.***.postgresql.ca-toronto-1.oci.oraclecloud.com',
    port                =>  5432,
    service_name        => 'sales',
    credential_name     => 'POSTGRESQL_CRED',
    gateway_params      => JSON_OBJECT('db_type' VALUE 'postgres', 'enable_ssl' VALUE true),
    ssl_server_cert_dn  => NULL,
    private_target      => true
  );
END;
/

Create a local view on the PostgreSQL table and map the remote PostgreSQL table into the Autonomous AI Database schema with a view.
```
CREATE VIEW support_ticket_metrics AS
SELECT *
FROM "app_schema"."support_ticket_metrics"@postgresql_link;
```
Select AI uses the metadata of the view during NL2SQL generation.

Configure network ACL access for the AI provider endpoint as an ADMIN user.

BEGIN
  DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
    host => 'api.openai.com',
    ace  => xs$ace_type(
              privilege_list => xs$name_list('http'),
              principal_name => 'ADMIN',
              principal_type => xs_acl.ptype_db
            )
  );
END;
/

Create a Select AI profile that includes local and remote objects. List both the local table and the view created on the PostgreSQL table in object_list.

Note:

This step assumes that you have created your OpenAI credentials. See Example: Select AI with OpenAI for more details.

BEGIN
  DBMS_CLOUD_AI.CREATE_PROFILE(
    profile_name => 'OPENAI',
    attributes   => '{
      "provider": "openai",
      "model": "gpt-4.1",
      "credential_name": "OPENAI_CRED",
      "object_list": [
        {"owner":"ADB_USER","name":"SUPPORT_TICKET_METRICS"},
        {"owner":"ADB_USER","name":"CUSTOMER_REVENUE"}
      ]
    }'
  );
END;
/

Set the Select AI profile.
```
EXEC DBMS_CLOUD_AI.SET_PROFILE('OPENAI');
```
The session sets the profile so Select AI uses the correct provider, credentials, and object metadata.

Test with Select AI.

select ai Which customers with over 1M USD in revenue last quarter
had critical support tickets, and what was the average resolution time by region;

RESPONSE:
REGION 	CUSTOMER_NAME 		AVG_RESOLUTION_TIME_HOURS
------  --------------------    -------------------------
MEA 	Customer-5359 		9

Review the generated SQL by using showsql. You see that it is a join between two tables, one in Autonomous AI Database, and one in PostgreSQL.

select ai showsql Which customers with over 1M USD in revenue last quarter
had critical support tickets, and what was the average
resolution time by region;
RESPONSE
--------------------------------------------------------------------------------
SELECT
cr."CUSTOMER_NAME" AS customer_name,
cr."REGION" AS region,
AVG(stm."resolution_time_hours") AS avg_resolution_time_hours
FROM
"ADB_USER"."CUSTOMER_REVENUE" cr
JOIN "ADB_USER"."SUPPORT_TICKET_METRICS" stm
ON cr."CUSTOMER_ID" = stm."customer_id"
WHERE
cr."REVENUE_QUARTER" = (
SELECT MAX(cr2."REVENUE_QUARTER")
FROM "ADB_USER"."CUSTOMER_REVENUE" cr2
)
AND cr."REVENUE_AMOUNT" > 1000000
AND stm."severity" = 'Critical'
GROUP BY
cr."CUSTOMER_NAME",
cr."REGION"

Select AI augments the prompt with table and view metadata, then sends it to the LLM to generate federated SQL. The Live AI Hub coordinates the query and accesses PostgreSQL through the database link.

Parent topic: Query Remote and Federated Data

10.5.3 Example: Use Select AI with Cloud Links to Query Another Autonomous AI Database

This example shows how to use Cloud Links to access data stored in another Autonomous AI Database and query it using Select AI.

Cloud Links provide read-only access to registered tables and views across databases within a tenancy, compartment, or region.

Before You Begin

Review

Perform Prerequisites for Select AI

This example walks through the complete flow required to make data available through Cloud Links and use it with Select AI.

Source Database: Oracle Autonomous AI Database where your data (tables or views) that you want to share resides.

Target Database (receiving side) acts as the Live AI Hub, formerly called AI Proxy Database: Oracle Autonomous AI Database where you configure Select AI and issue natural language queries.

Cloud Links provide a secure, read-only mechanism for sharing data across Autonomous AI Databases without copying data, managing database credentials, or setting up network connections manually.

The ADMIN user authorizes a data owner to register tables and views for remote access. This step controls who can publish data for Cloud Link sharing.
```
--run on SOURCE database as ADMIN
BEGIN
  DBMS_CLOUD_LINK_ADMIN.GRANT_REGISTER(
    username => 'ADB_USER',
    scope    => 'MY$TENANCY'
  );
END;
/
```

The data owner registers a table (CUSTOMERS) and assigns it a namespace, name, and scope. Registration makes the data discoverable to other Autonomous AI Databases within the specified scope (tenancy, compartment, or region).

--run on SOURCE database as the Select AI user
BEGIN
  DBMS_CLOUD_LINK.REGISTER(
    schema_name => 'ADB_USER',
    schema_object => 'CUSTOMERS',                 -- Table or view name
    namespace => 'SALES_DATA',                    -- Namespace the user provides as a name for Cloud Link access 
    name => 'CUSTOMERS',                          -- Name visible to consumers
    description => 'customer data',               -- Table or view description
    scope => 'MY$TENANCY'                         -- MY$COMPARTMENT, MY$TENANCY, or MY$REGION
  );
END;
/

Note:

Metadata sync may take several minutes. During this window, the data set may not immediately appear on the target database.

To verify registrations on the source database as ADMIN, query the data dictionary. This query confirms the namespace, name and the scope where the data set is visible.

select namespace, name
  , json_value(scope,'$.TENANCY[*]') tenancy
  , json_value(scope,'$.COMPARTMENT[*]') compartments
  , json_value(scope,'$.REGION[*]') region
  , description
from dba_cloud_link_registrations;

Returns:

NAMESPACE    NAME        TENANCY              COMPARTMENTS    REGION    DESCRIPTION
SALES_DATA   CUSTOMERS   OCID1.TENANCY....    (null)          (null)    customer data

On the receiving database, the ADMIN user grants read access so that users can consume registered Cloud Link data sets.
```
BEGIN
  DBMS_CLOUD_LINK_ADMIN.GRANT_READ(
    username => 'ADB_USER'
  );
END;
/
```

The target database user can list or search available Cloud Link data sets to confirm access and identify the correct namespace and object names.

-- View all accessible data sets
SELECT NAMESPACE, NAME, DESCRIPTION FROM ALL_CLOUD_LINK_ACCESS;

Returns:
NAMESPACE     NAME         DESCRIPTION
SALES_DATA    CUSTOMERS    customer data

Optionally, search for specific data sets. This enables you to search for data sets using keywords without knowing the exact namespace and name.

-- 
DECLARE
   result CLOB DEFAULT NULL;
BEGIN
   DBMS_CLOUD_LINK.FIND('CUSTOMERS', result);
   DBMS_OUTPUT.PUT_LINE(result);
END;
/

Returns:

[{"name":"CUSTOMERS","namespace":"SALES_DATA","description":"customer data"}]

Create local tables or views using Cloud Link syntax.
```
CREATE VIEW customers_view AS 
SELECT * FROM SALES_DATA.CUSTOMERS@cloud$link;

CREATE TABLE customers_table AS 
SELECT * FROM SALES_DATA.CUSTOMERS@cloud$link;
```
On the target database, create views or tables that reference the remote data using the @cloud$link syntax. These objects behave like local database objects but read data from the source database.

Create a Select AI profile.

Note:

This step assumes that you have created your OCI credentials. See Example: Select AI with OCI Generative AI for more details.

BEGIN
  DBMS_CLOUD_AI.CREATE_PROFILE(
    profile_name => 'MY_AI_PROFILE',
    attributes   => '{"provider": "oci",
                      "credential_name": "MY_AI_CRED",
                      "object_list": [
                        {"owner": "ADB_USER", "name": "CUSTOMERS_VIEW"},
                        {"owner": "ADB_USER", "name": "CUSTOMERS_TABLE"}
                      ]
                     }');
END;
/

A Select AI profile includes the Cloud Link views or tables in its object_list. This step tells Select AI which objects it can use when generating SQL.

Set the Select AI profile.
```
EXEC DBMS_CLOUD_AI.SET_PROFILE('MY_AI_PROFILE')
```
The session sets the profile so Select AI uses the correct provider, credentials, and object metadata.
Test with Select AI.
```
SELECT AI SHOWSQL how many customers do I have;
```
A natural language prompt such as “how many customers do I have” is submitted. Select AI uses the metadata from the Cloud Link table to generate SQL that queries the shared data.
```
RESPONSE
SELECT COUNT("ct"."ID") AS "customer_count" FROM "ADB_USER"."CUSTOMERS_TABLE" "ct"
```

In stateless environments (such as APEX or Database Actions SQL Worksheet), test Select AI using DBMS_CLOUD_AI.GENERATE and pass the profile name directly.

DECLARE
  result CLOB;
BEGIN
  result := DBMS_CLOUD_AI.GENERATE(
    prompt       => 'how many customers do I have',
    profile_name => 'MY_AI_PROFILE',
    action       => 'showsql'
  );
  DBMS_OUTPUT.PUT_LINE(result);
END;
/

Parent topic: Query Remote and Federated Data

10.5.4 Example: Use External Table over Table Hyperlink with Select AI

This example shows how an Autonomous AI Database (consumer database) acts as an Live AI Hub, to query remote data hosted in another Autonomous AI Database (provider database) using an External Table over a Table Hyperlink.

Before You Begin

Review

This example uses the SH schema tables SH.CUSTOMERS and SH.SALES in the provider Autonomous AI Database.

In the provider Autonomous AI Database (data owner), create Table Hyperlink sharing for the required tables.

Create a table hyperlink for CUSTOMERS table.

DECLARE
   hyperlink_status CLOB;
BEGIN
   DBMS_DATA_ACCESS.CREATE_URL(
      schema_name          => 'SH',
      schema_object_name   => 'CUSTOMERS',
      expiration_minutes   => 1440,   -- The hyperlink remains valid for 1440 minutes
      result               => hyperlink_status
   );

   DBMS_OUTPUT.PUT_LINE(hyperlink_status);
END;
/

The result is similar to:

RESULT: 
{
  "status" : "SUCCESS",
  "id" : "LYYPJrNCL-Fa6...9T",
  "preauth_url" : "https://dataaccess.adb.us-chicago-1.oraclecloudapps.com/adb/p/6ya...j9k/data",
  "expiration_ts" : "2026-01-31T08:46:29.250Z"
}

Create a table hyperlink for SALES table.

DECLARE
   hyperlink_status CLOB;
BEGIN
   DBMS_DATA_ACCESS.CREATE_URL(
      schema_name          => 'SH',
      schema_object_name   => 'SALES',
      expiration_minutes   => 1440,   -- The hyperlink remains valid for 1440 minutes
      result               => hyperlink_status
   );

   DBMS_OUTPUT.PUT_LINE(hyperlink_status);
END;
/

The result is similar to:

RESULT:
{
  "status" : "SUCCESS",
  "id" : "ddzdq...",
  "preauth_url" : "https://dataaccess.adb.us-chicago-1.oraclecloudapps.com/adb/p/YvYb8eJ...JQE/data",
  "expiration_ts" : "2026-01-31T08:47:24.823Z"
}

For supported parameters, see Use Table Hyperlinks to Create an External Table.

This procedure generates a Table Hyperlink URL preauth_url (a PAR URL) and this URL exposes read-only access to the table and can be used to create an external table in another database.

In the consumer Autonomous AI Database (AI Proxy), create the External Table. Copy the Table hyperlink URL (PAR URL) generated in the Step 1 from the provider database to define the External Table.
```
BEGIN
   DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
      table_name      => 'CUSTOMERS_EXT',
      credential_name => NULL,
      file_uri_list   => 'https://dataaccess.adb.us-chicago-1.oraclecloudapps.com/adb/p/6ya...j9k/data',
      format          => json_object('type' VALUE 'csv')
   );
END;
/
```
If the two databases are in the same region, the URL points to a local OCID. For cross-region, the URI must reference the remote region’s endpoint.

Repeat the same for SH.SALES:

BEGIN
   DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
      table_name      => 'SALES_EXT',
      credential_name => NULL,
      file_uri_list   => '<preauth_url_for_SALES>',
      format          => json_object('type' VALUE 'csv')
   );
END;
/

Both CUSTOMERS_EXT and SALES_EXT now appear as local tables in the consumer database instance. The data comes from the remote Autonomous AI Database using the Table Hyperlink.

Verify if the external tables are created.

SELECT table_name 
FROM user_tables
WHERE table_name LIKE '%EXT%';

Configure network ACL access for the AI provider endpoint as an ADMIN user.

BEGIN
  DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
    host => 'api.openai.com',
    ace  => xs$ace_type(
              privilege_list => xs$name_list('http'),
              principal_name => 'ADB_USER',
              principal_type => xs_acl.ptype_db
            )
  );
END;
/

Create a Select AI profile that includes the objects. List the local table created in the consumer Autonomous AI Database under object_list.

Note:

This step assumes that you have created your OpenAI credentials. See Example: Select AI with OpenAI for more details.

BEGIN
  DBMS_CLOUD_AI.CREATE_PROFILE(
    profile_name => 'AI_HYPERLINK_PROFILE',
    
    attributes   => '{
      "provider": "openai",
      "credential_name": "OPENAI_CRED",
      "object_list": [
        {"owner":"ADB_USER","name":"CUSTOMERS_EXT"},
        {"owner":"ADB_USER","name":"SALES_EXT"}
      ],
      "conversation": "true"
    }'
  );
END;
/

Set the Select AI profile.
```
EXEC DBMS_CLOUD_AI.SET_PROFILE('AI_HYPERLINK_PROFILE');
```
The session sets the profile so Select AI uses the correct provider, credentials, and object metadata.
Test with Select AI. Select AI runs the prompt on the External Tables.
```
SELECT AI SHOWSQL how many customers do I have;
```
Review the generated SQL.
The output may be similar to:
```
SELECT COUNT("CUST_ID") AS "Total_Customers" FROM "ADB_USER"."CUSTOMERS_EXT"
```

Select AI augments the prompt with table and view metadata, then sends it to the LLM to generate federated SQL. Select AI treats the external tables as local objects while the data remains in the remote Autonomous AI Database.

In stateless environments (such as APEX or Database Actions SQL Worksheet), test Select AI using DBMS_CLOUD_AI.GENERATE and pass the profile name directly.

DECLARE
  result CLOB;
BEGIN
  result := DBMS_CLOUD_AI.GENERATE(
    prompt       => 'how many customers do I have',
    profile_name => 'AI_HYPERLINK_PROFILE',
    action       => 'showsql'
  );
  DBMS_OUTPUT.PUT_LINE(result);
END;
/

Parent topic: Query Remote and Federated Data

10.5.5 Example: Use Federated Table with Select AI

This example shows how an Autonomous AI Database (consumer) uses Select AI to query a Federated Table that automatically connects to a remote Autonomous AI Database (provider). The (consumer database) acts as an Live AI Hub (formerly called AI Proxy Database) to query remote data hosted in another Autonomous AI Database (provider database).

Before You Begin

Review

This example uses the SH schema table SH.CUSTOMERS in the provider Autonomous AI Database. Both databases belong to the same tenancy and compartment.

In the provider Autonomous AI Database (data owner), as an ADMIN, allow the user (data owner) to register tables for federated access.
```
BEGIN
DBMS_DATA_ACCESS_ADMIN.GRANT_REGISTER(
username => 'DATA_OWNER',
scope => 'MY$COMPARTMENT'
);
END;
/
```
This grants the user the ability to register their own tables and views for remote access within the specified scope.
As an ADMIN grant execute privilege on DBMS_DATA_ACCESS_SCOPE. The DATA_OWNER user needs permission to register tables for access scopes.
```
grant execute on DBMS_DATA_ACCESS_SCOPE to DATA_OWNER;
```
As DATA_OWNER, register the schema or specific tables for federated access.

Run this in the provider database as user DATA_OWNER.

You can register all tables in the schema or specify a single table such as CUSTOMERS.
```
BEGIN
  DBMS_DATA_ACCESS_SCOPE.REGISTER_CREATION_SCOPE(
    schema_name         => 'DATA_OWNER',
    schema_object_name  => NULL, --or provide specific table names
    scope               => 'MY$COMPARTMENT'
  );
END;
/
```
This registration exposes the schema (or specific tables) for federated access to other databases within the same compartment.

In the consumer Autonomous AI Database, as ADMIN grant privileges that allow the consumer user (DATA_USER) to create and query federated tables.

GRANT EXECUTE ON DBMS_DATA_ACCESS TO DATA_USER;

GRANT CREATE SESSION TO DATA_USER;
GRANT CREATE TABLE TO DATA_USER;
ALTER USER DATA_USER QUOTA UNLIMITED ON DATA;

-- if the user will manage other objects
GRANT PDB_DBA TO DATA_USER;

As ADMIN grant read access to the remote schema and object. Allow the consumer user (DATA_USER) in the consumer database to read the shared object (CUSTOMERS) from the provider.
```
BEGIN
DBMS_DATA_ACCESS_ADMIN.GRANT_READ(
username => 'DATA_USER',
remote_schema_name => 'DATA_USER_SCHEMA',
remote_schema_object_name=> 'CUSTOMERS'
);
END;
/
```
This step authorizes the consumer user DATA_USER to access the specified table in the provider database.

Run this in the consumer database as user DATA_USER. The db_ocids argument specifies the provider database’s region and OCID. Use the region short code (for example, ORD for us-chicago-1).

BEGIN
  DBMS_DATA_ACCESS.CREATE_FEDERATED_TABLE(
    table_name                => 'FEDERATED_CUSTOMERS',
    remote_schema_name        => 'DATA_USER_SCHEMA',
    remote_schema_object_name => 'CUSTOMERS',
    db_ocids                 => '[{"region": "ORD","db_ocid": "OCID1.AUTONOMOUSDATABASE.OC1.US-CHICAGO-1.ANXX..."}]'
  );
END;
/

Note:

The Database OCID (db_ocid) must be in Uppercase.

Configure network ACL access for the AI provider endpoint as an ADMIN user.

BEGIN
  DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
    host => 'api.openai.com',
    ace  => xs$ace_type(
              privilege_list => xs$name_list('http'),
              principal_name => 'DATA_USER',
              principal_type => xs_acl.ptype_db
            )
  );
END;
/

Create a Select AI profile that includes the objects. List the local table created in the consumer Autonomous AI Database under object_list.

Note:

This step assumes that you have created your OpenAI credentials. See Example: Select AI with OpenAI for more details.

BEGIN
  DBMS_CLOUD_AI.CREATE_PROFILE(
    profile_name => 'AI_FEDERATED_TABLE_PROFILE',
      attributes   => '{
      "provider": "openai",
      "credential_name": "OPENAI_CRED",
      "object_list": [
        {"owner":"DATA_USER_SCHEMA","name":"FEDERATED_CUSTOMERS"}
      ],
      "conversation": "true"
    }'
  );
END;
/

Set the Select AI profile.
```
EXEC DBMS_CLOUD_AI.SET_PROFILE('AI_FEDERATED_TABLE_PROFILE');
```
The session sets the profile so Select AI uses the correct provider, credentials, and object metadata.

Test with Select AI. Select AI runs the prompt on the federated tables.

SELECT AI SHOWSQL how many customers do I have;

Review the generated SQL.

The output may be similar to:

SELECT COUNT("CUST_ID") AS "Total_Customers"
FROM "DATA_USER_SCHEMA"."FEDERATED_CUSTOMERS"

Select AI augments the prompt with table and view metadata, then sends it to the LLM to generate federated SQL. Select AI treats the federated tables as local objects while the data remains in the remote Autonomous AI Database.

In stateless environments (such as APEX or Database Actions SQL Worksheet), test Select AI using DBMS_CLOUD_AI.GENERATE and pass the profile name directly.

DECLARE
  result CLOB;
BEGIN
  result := DBMS_CLOUD_AI.GENERATE(
    prompt       => 'how many customers do I have',
    profile_name => 'AI_FEDERATED_TABLE_PROFILE',
    action       => 'showsql'
  );
  DBMS_OUTPUT.PUT_LINE(result);
END;
/

Parent topic: Query Remote and Federated Data