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.