Oracle AI Data Catalog

Learn about how to set up and use Oracle AI Data Catalog .

About Oracle AI Data Catalog

The Oracle AI Data Catalog is an Oracle-managed Iceberg REST catalog service. The catalog service is designed to manage Iceberg tables efficiently. It provides APIs for handling CRUD operations on tables, managing metadata, and handling transactions.

AI Data Catalog works within Oracle Autonomous AI Database and is available to all databases inside that tenancy. The catalog service uses the tenant database schema for storing the metadata of the tables.

Supported Storage Types

Oracle AI Data Catalog supports the following storage providers:

• Oracle Cloud Infrastructure with S3 compatibility
• Azure Storage (ADLS and BLOB)
• ZFS Storage

Quick Start

AI Data Catalog is intended to be enabled on one Autonomous AI Database in a tenancy, with other Autonomous AI Database instances in the tenancy connecting to and using it, so that all Autonomous AI Database instances can access the same Iceberg catalog, subject to user permissions.

Note the following:

• Only the tenancy administrator or a user with the ADMIN role can set up AI Data Catalog.
• Each tenant can have multiple databases. You must decide on the database that will serve as the Catalog database. Although it is possible to enable AI Data Catalog on any Autonomous AI Database in a tenancy, Oracle recommends that it is enabled on a single database. As the usage pattern of the catalog service is mostly transactional, we recommend that the Autonomous AI Database uses the Transaction Processing workload type.
• Ensure that the Object Storage location that you configure for the AI Data Catalog service is empty, and not used for any other purposes.

The workflow is as follows:

1. You enable AI Data Catalog on an Autonomous AI Database Serverless instance.
2. AI Data Catalog exposes REST catalog APIs.
3. You register Iceberg tables in AI Data Catalog.
4. You use Spark to connect to the catalog URL.
5. The query engine requests table metadata.
6. AI Data Catalog returns metadata locations.
7. The engine reads data directly from Object Storage.

Follow these steps to set up and use AI Data Catalog:

Task	More Information
Choose an Autonomous AI Database instance as the Catalog database.	Choose Catalog Repository Database
Enable AI Data Catalog Using Tags	Enable Oracle AI Data Catalog
Register Storage Metadata for AI Data Catalog	Register Storage Credentials
[Optional] Register Storage with Existing Iceberg Data	If you want to register existing Iceberg tables with AI Data Catalog, see Register Storage with Existing Iceberg Data.
[Optional] Authorize a database user with the AICAT_USER or PDB_DBA role. Authenticate the user using a token API call with user credentials.	Authenticate and Authorize Users

Enable Access to Private Data Sources from Autonomous AI Database

The Autonomous AI Database from where you are accessing AI Data Catalog should be configured to use a private endpoint to be able to communicate with private database sources. Otherwise when you try to create and test such a connection, you might get a “failed to connect” error.

When Autonomous AI Database is configured with a Private Endpoint, you need to explicitly route outbound connectivity through the private endpoint. Additionally, you need to set proper DNS resolution and connection configuration to ensure secure communication between AI Data Catalog and the database.

To configure AI Data Catalog with Autonomous AI Database Private Endpoint access, you need to route outbound connections from Autonomous AI Database through the Private Endpoint.

Do the following:

1. To set the routing property, log in to SQL Web as the ADMIN user and execute the following statement:

   ALTER DATABASE PROPERTY SET ROUTE_OUTBOUND_CONNECTIONS = 'PRIVATE_ENDPOINT';

   Note: If this command fails to execute, contact Oracle Autonomous AI Database Support.

2. To verify that the routing configuration is set correctly, run the following query:

   SELECT *
   FROM DATABASE_PROPERTIES
   WHERE PROPERTY_NAME = 'ROUTE_OUTBOUND_CONNECTIONS';

   The expected value is PRIVATE_ENDPOINT.

Choose Catalog Repository Database

You need to create or select an existing Autonomous AI Database instance the Oracle AI Data Catalog will run on. This database instance will be used as the catalog metadata repository.

Decide on the warehouse within the tenancy where the catalog will be created. Note the following:

• If a tenancy already has the catalog created with a centralized database, then all other tenant databases can point to the existing catalog in the tenancy.
• If you want to create multiple catalogs for a tenant, then we recommend that you get details about the existing catalog and use the centralized catalog instead of allowing tenants to create multiple catalogs.

Enable Oracle AI Data Catalog

To enable Oracle AI Data Catalog on an Autonomous AI Database Serverless instance you need to set the ADB$TOOLS Oracle Cloud Infrastructure tag to AI_CAT. You can then connect to the catalog service using the generated service URL.

Note the following:

• Setting the Oracle Cloud Infrastructure tag AI_CAT on an Autonomous AI Database Serverless instance enables the AI Data Catalog service for that database.
• The service supports up to 32 ECPUs in the initial release. ECPU consumption is billed at the standard Autonomous AI Database rate when the catalog service compute resources are active and in use. See ECPU Compute Model Billing Information for information about ECPU billing for additional VMs and compute resources.
• The AI Data Catalog service is configured with a default idle timeout of 2 hours (120 minutes). This configuration keeps the service readily available and responsive for typical usage patterns while avoiding unnecessary billing when the service is idle. This also ensures that when the service remains inactive beyond the configured idle timeout period, the associated compute resources are automatically stopped and are no longer billed.
• If the AI Data Catalog service has timed out, any subsequent request to the service automatically triggers a restart of the catalog service. In most cases, the service becomes available within approximately 30 seconds.

You can enable AI Data Catalog on Lakehouse and Autonomous AI Transaction Processing database types. Oracle recommends that you use ATP databases because catalog operations such as metadata management, API calls, authorization checks, and catalog updates would be better optimized using ATP.

To enable AI Data Catalog and obtain the instance URL:

1. Log in to Oracle Cloud Infrastructure and go to the Autonomous AI Database Serverless instance that you have selected as the catalog metadata repository.
2. Navigate to Tags.

3. Set the following tag:

   • Tag Key name: ADB$TOOLS
   • Tag Value: AI_CAT

   This enables AI Data Catalog for the Autonomous AI Database Serverless instance.

4. To obtain the endpoint, go to Autonomous AI Database Serverless in Oracle Cloud Infrastructure and select the Tools configuration tab. Edit the displayed URL to the <database name>/catalog format. For example, change https://test1234.adb.us-phoenix-1.oraclecloudapps.com/ords/apex to https://test1234.adb.us-phoenix-1.oraclecloudapps.com/catalog.

A splash screen appears that lists the steps you need to take to get started with using AI Data Catalog along with links to the help topics.

Register Storage Credentials

To register storage use the PL/SQL ORACLE_AI_DATA_CATALOG.REGISTER_STORAGE_<VENDOR>() procedure.

Note that the invoker must be an ADMIN user or a user with PDB_DBA role assigned. See Manage User Roles and Privileges on Autonomous AI Database for more information on granting roles and adding or updating privileges for a user.

Oracle AI Data Catalog supports following storage providers:

• Oracle Cloud Infrastructure with S3 compatibility
• Azure Storage (ADLS and BLOB)
• ZFS Storage

To update the storage credentials, see Update Storage Credentials. If you want to unregister the storage credentials see Unregister Storage Credentials.

Oracle Cloud Infrastructure with S3 compatibility

Here is an example procedure to register your warehouse for Oracle Cloud Infrastructure S3 Compatible Storage:

begin
   oracle_ai_data_catalog.register_storage_oci(
   p_warehouse => 's3://<bucket>',
   p_endpoint => 'https://<namespace>.compat.objectstorage.<region>.oci.customer-oci.com',
   p_region     => '<region>',
   p_access_key => '<access-key-id>',
   p_secret_key => '<secret-key-id>'
   );
end;
/

For information about the storage endpoint format, see Amazon S3 Compatibility API Hosted Style Support in Object Storage.

See instructions in https://docs.oracle.com/en-us/iaas/Content/Identity/Tasks/managingcredentials.htm#create-secret-key on how to obtain the access key and secret key.

Azure Storage (ADLS and BLOB)

Here is an example procedure to register your warehouse for Azure ADLS Storage:

begin oracle_ai_data_catalog.register_storage_azure(
    p_warehouse            => 'abfss://<container>',
    p_endpoint             => 'https://<account>.dfs.core.windows.net',
    p_storage_account_name => '<storage_account_name>',
    p_storage_account_key  => '<storage_account_key>'
);
end;
/

Here is an example procedure to register your warehouse for Azure BLOB Storage:

begin oracle_ai_data_catalog.register_storage_azure(
    p_warehouse            => 'abfss://<container>',
    p_endpoint             => 'https://<account>.blob.core.windows.net',
    p_storage_account_name => '<storage_account_name>',
    p_storage_account_key  => '<storage_account_key>'
);
end;
/

ZFS Storage

Here is an example procedure to register your warehouse for ZFS Storage:

begin
   oracle_ai_data_catalog.register_storage_zfs(
   p_warehouse  => 's3a://icebergs3/iceberg_warehouse_s3/',
   p_endpoint   => 'https://<account>.us.oracle.com',
   p_accesskey => '<access-key-id>',
   p_secret_key => '<secret-key-id>'
   );
end;
/

Update Storage Credentials

To update storage credentials, use the PL/SQL ORACLE_AI_DATA_CATALOG.UPDATE_STORAGE_CREDENTIAL_<VENDOR>() procedure.

Note that the invoker must be an ADMIN user or a user with PDB_DBA role assigned. See Manage User Roles and Privileges on Autonomous AI Database for more information on granting roles and adding or updating privileges for a user.

Oracle AI Data Catalog supports following storage providers:

• Oracle Cloud Infrastructure with S3 compatibility
• Azure Storage (ADLS and BLOB)
• ZFS Storage

If you want to unregister the storage credentials use the UNREGISTER_STORAGE_CREDENTIAL procedure. See Unregister Storage Credentials.

Oracle Cloud Infrastructure with S3 compatibility

Here is an example procedure to update credentials for your Oracle Cloud Infrastructure S3 Compatible Storage:

BEGIN
    oracle_ai_data_catalog.update_storage_credential_oci(
        p_access_key      => '<value>',
        p_secret_key      => '<value>'
    );
END;
/

Azure Storage (ADLS and BLOB)

Here is an example procedure to update credentials for Azure Storage:

BEGIN
    oracle_ai_data_catalog.update_storage_credential_azure(
        p_storage_account_name      => '<value>',
        p_storage_account_key      => '<value>'
    );
END;
/

ZFS Storage

Here is an example procedure to update credentials for ZFS Storage:

BEGIN
    oracle_ai_data_catalog.update_storage_credential_zfs(
        p_access_key      => '<value>',
        p_secret_key      => '<value>'
    );
END;
/

Unregister Storage Credentials

To unregister storage credentials use the PL/SQL ORACLE_AI_DATA_CATALOG.UNREGISTER_STORAGE_CREDENTIAL(...) procedure.

Note that the invoker must be an ADMIN user or a user with PDB_DBA role assigned.

Execute the following:

BEGIN
    oracle_ai_data_catalog.unregister_storage_credential;
END;
/

If you want to reuse the AI Data Catalog, use the UPDATE_STORAGE_CREDENTIAL_<VENDOR>() procedure. See Update Storage Credentials.

Register Storage with Existing Iceberg Data

AI Data Catalog supports registering existing Apache Iceberg tables that are stored in Object Storage. Once registered, the Iceberg table metadata and table definitions become discoverable and accessible through AI Data Catalog.

Before registering Iceberg tables in AI Data Catalog, make sure that:

• AI Data Catalog is already enabled on an Autonomous AI Database Serverless instance. See Enable Oracle AI Data Catalog.
• You are connected to the Autonomous AI Database Serverless instance as the ADMIN user or a user with the PDB_DBA or AICAT_USER role.
• You have registered your storage with AI Data Catalog. See Register Storage Credentials.

• The Object Storage bucket contains valid Iceberg table data and metadata in the required format.

  For example:

  Bucket/Warehouse: N-Warehouse
  -NAMESPACE
    -TABLE_NAME_NAMESPACE
      - data
        - parquet files.
      - metadata
        - json files

  Here:

  • NAMESPACE represents the Iceberg namespace/schema.
  • TABLE_NAME_NAMESPACE represents the Iceberg table
  • data contains the table data files (typically Parquet)
  • metadata contains Iceberg metadata and snapshot JSON files required for table management and querying
• You have access permissions to the bucket and objects.

To register your existing Iceberg tables with AI Data Catalog:

1. Connect to your AI Data Catalog instance using your database authentication credentials.

   Use the following command to generate an access token:

   TOKEN=$(curl -k -s --location '<URI>/v1/auth/token' \
   --header 'Content-Type: application/x-www-form-urlencoded' \
   --data-urlencode 'grant_type=client_credentials' \
   --data-urlencode 'client_id=<dbUser>' \
   --data-urlencode 'client_secret=<dbPwd>' \
   --data-urlencode 'scope=PRINCIPAL_ROLE:ALL' | jq -r '.access_token')

   Here:

   • <URI> is the Catalog server URL.
   • <dbUser> is your database username.
   • <dbPwd> is your database password.

   If the token value returns as null, rerun the request using the -v option for verbose output to help troubleshoot authentication or connectivity issues.

   Example:

   curl -v -k --location '<URI>/v1/auth/token'

2. Determine the Iceberg metadata file location for the table you want to register in the catalog. You can obtain the metadata location in one of the following ways:

   • Load the table metadata from a previous catalog server.
   • Manually browse the warehouse or Object Storage bucket and identify the current Iceberg metadata JSON file.

   For example:

   Namespace           : SILVER
   Table               : COMPETITOR_PRICING_SILVER
   Metadata Location   : s3://0A-Warehouse/SILVER/COMPETITOR_PRICING_SILVER/metadata/00002-f2662b11-3268-4792-90f9-b8f4b851d629.metadata.json

   The metadata JSON file contains the Iceberg table definition, schema, snapshots, manifests, and table state information required for catalog registration.

   To create namespace SILVER if it does not already exist, use the following command:

   curl -k --location '<URI>/v1/namespaces' \
   --header "Authorization: Bearer $TOKEN" \
   --header 'Content-Type: application/json' \
   --data '{"namespace": ["SILVER"], "properties": {}}'

3. To register the table, run the following command:

   curl -k --location '<URI>/v1/namespaces/SILVER/register' \
   --header 'Content-Type: application/json' \
   --header "Authorization: Bearer $TOKEN" \
   --data '{
       "name":"COMPETITOR_PRICING_SILVER",
       "metadata-location": "s3://0A-Warehouse/SILVER/COMPETITOR_PRICING_SILVER/metadata/00002-f2662b11-3268-4792-90f9-b8f4b851d629.metadata.json",
       "overwrite": false
   }'

4. To load the newly registered table, run the following command:

   curl -k --location '<URI>/v1/namespaces/SILVER/tables/COMPETITOR_PRICING_SILVER' \
   --header "Authorization: Bearer $TOKEN" \
   --header 'Content-Type: application/json'

5. Similarly, register other tables.

Authenticate and Authorize Users

To allow a database user to access the AI Data Catalog service (see Choose Catalog Repository Database), the user should be an ADMIN user or you need to authorize the user with the AICAT_USER or PDB_DBA role.

You can authenticate users using a token API call with user credentials. To obtain the access token, send the following request:

POST https://public_lb_host/catalog/v1/auth/token

Example request parameter using cURL:

curl -k -s --location '<URI>/catalog/v1/auth/token' \
--header 'Content-Type: application/x-www-form-urlencoded' \
--data-urlencode 'grant_type=client_credentials' \
--data-urlencode 'client_id=<dbUser>' \
--data-urlencode 'client_secret=<dbPwd>' \
--data-urlencode 'scope=PRINCIPAL_ROLE:ALL' | jq -r '.access_token'

When the token expires, you will receive a 401 response. Use the same endpoint to obtain a new token.

Here’s a sample response token:

{
    "access_token": "<token>",
    "issued_token_type": "urn:ietf:params:oauth:token-type:access_token",
    "token_type": "bearer",
    "expires_in": 3600
}

Use the obtained token as your Bearer token in the Authorization header:

Authorization: Bearer <your_token>

See REST API for Oracle AI Data Catalog for detailed information about each catalog API REST endpoint.

AI Data Catalog Iceberg REST Catalog

The AI Data Catalog Iceberg REST Catalog is the default REST-based Iceberg catalog embedded within AI Data Catalog. It is designed to efficiently manage Iceberg tables by providing REST APIs for table lifecycle operations, including create, read, update, and delete (CRUD), along with metadata management and transaction handling. The AI Data Catalog Iceberg REST Catalog follows the standard Apache Iceberg REST Catalog API specification. You must be an ADMIN user or as a user that is granted the PDB_DBA or the AICAT_USER role.

See REST API for Oracle AI Data Catalog for detailed information about each catalog API REST endpoint.

Using AI Data Catalog with Query Engines

AI Data Catalog integrates with multiple query engines to request and retrieve information. You can use AI Data Catalog with query engines such as Apache Spark, add external tables over AI Data Catalog tables, mount AI Data Catalog on DBMS catalog, as well as create Apache Iceberg tables using Oracle Data Transforms.

For specific information, see the following sections:

• Query AI Data Catalog using Apache Spark
• Add External Tables over AI Data Catalog Iceberg Tables
• Mount and Query AI Data Catalog in DBMS Catalog
• Use AI Data Catalog as a Catalog Provider in Oracle Data Transforms

If you encounter any issues, file a service request at Oracle Cloud Support or contact your support representative.

Query AI Data Catalog using Apache Spark

To use Apache Spark queries on AI Data Catalog you need to first download the Spark runtime binary file.

1. Download and extract spark-3.5.6-bin-hadoop runtime binary tgz file.
2. Add iceberg-aws-bundle-1.9.2.jar under the spark-3.5.6-bin-hadoop3/jars directory.

3. From root directory (spark-3.5.6-bin-hadoop3) run the following.

   To configure Spark with Oracle Cloud Infrastructure S3:

   bin/spark-shell \
   --packages org.apache.iceberg:iceberg-spark-runtime-3.5_2.12:1.10.0 \
   --conf spark.sql.catalog.aicat=org.apache.iceberg.spark.SparkCatalog \
   --conf spark.sql.catalog.aicat.type=rest \
   --conf spark.sql.catalog.aicat.uri=http://<host>:<port>/catalog \
   --conf spark.sql.catalog.aicat.warehouse=aicat_iceberg_rest_catalog \
   --conf spark.sql.catalog.aicat.s3.access-key-id=<s3AccessKeyId> \
   --conf spark.sql.catalog.aicat.s3.secret-access-key=<s3SecretAccessKey> \
   --conf spark.sql.catalog.aicat.rest.auth.type=oauth2 \
   --conf spark.sql.catalog.aicat.oauth2-server-uri=http://<host>:<port>/catalog/v1/auth/token \
   --conf spark.sql.catalog.aicat.credential=<dbusername>:<dbpassword> \
   --conf spark.sql.catalog.aicat.scope=PRINCIPAL_ROLE:ALL \
   --conf spark.driver.extraJavaOptions=" -Dhttp.proxyHost=www-proxy-hqdc.us.oracle.com -Dhttp.proxyPort=80 -Dhttps.proxyHost=www-proxy-hqdc.us.oracle.com -Dhttps.proxyPort=80"

   For creating secret key and secret access key, see Creating a Customer Secret Key.

   To configure Spark with Azure:

   bin/spark-shell \
   --packages org.apache.iceberg:iceberg-spark-runtime-3.5_2.12:1.10.0 \
   --conf spark.sql.catalog.aicat=org.apache.iceberg.spark.SparkCatalog \
   --conf spark.sql.catalog.aicat.type=rest \
   --conf spark.sql.catalog.aicat.uri=http://<host>:<port>/catalog \
   --conf spark.sql.catalog.aicat.warehouse=oadc_iceberg_rest_catalog \
   --conf spark.sql.catalog.aicat.rest.auth.type=oauth2 \
   --conf spark.sql.catalog.aicat.oauth2-server-uri=http://<host>:<port>/catalog/v1/auth/token \
   --conf spark.sql.catalog.aicat.credential=<username>:<password> \
   --conf spark.sql.catalog.aicat.scope=PRINCIPAL_ROLE:ALL \
   --conf spark.sql.catalog.aicat.io-impl=org.apache.iceberg.azure.adlsv2.ADLSFileIO \
   --conf spark.sql.catalog.aicat.adls.account=<accountName> \
   --conf spark.sql.catalog.aicat.adls.endpoint=https://<accountName>.blob.core.windows.net/<container> \
   --conf spark.sql.catalog.aicat.adls.auth.shared-key.account.name=<accountName> \
   --conf spark.sql.catalog.aicat.adls.auth.shared-key.account.key=<accountKey> \
   --conf spark.driver.extraJavaOptions=" -Dhttp.proxyHost=www-proxy-hqdc.us.oracle.com -Dhttp.proxyPort=80 -Dhttps.proxyHost=www-proxy-hqdc.us.oracle.com -Dhttps.proxyPort=80"

4. Run any of the following Spark SQL commands:

   • spark.sql("SHOW NAMESPACES IN aicat").show()
   • spark.sql("CREATE NAMESPACE IF NOT EXISTS aicat.\KBNSXI`”).show()`
   • spark.sql("CREATE TABLE IF NOT EXISTS aicat.\KBNSXI`.TABLE2 (name STRING, add STRING)”).show()`
   • spark.sql("INSERT INTO aicat.\KBNSXI`.TABLE2 VALUES (‘John Doe’, ‘USA, 12345’)”).show()`
   • spark.sql("SELECT * FROM aicat.\KBNSXI`.TABLE2”).show()`

Add External Tables over AI Data Catalog Iceberg Tables

To use External Tables each catalog object should be identified using a fully qualified name in the <catalog_name>.<namespace>.<table> format.

Open SQL worksheet for your Autonomous AI Database instance and execute the following:

-- setup ACL Rules --
-- for XT to communicate to ai catalog instance
-- for XT to communicate to object storage bucket, OCI in this example.

BEGIN
dbms_network_acl_admin.append_host_ace(
host => '*.oraclecloudapps.com',
lower_port => 443,
upper_port => 443,
ace => xs$ace_type(
privilege_list => xs$name_list('http', 'http_proxy'),
principal_name =>  'ADMIN',
principal_type => xs_acl.ptype_db));

dbms_network_acl_admin.append_host_ace(
host => '*.oci.customer-oci.com',
lower_port => 443,
upper_port => 443,
ace => xs$ace_type(
privilege_list => xs$name_list('http', 'http_proxy'),
principal_name =>  'ADMIN',
principal_type => xs_acl.ptype_db));
END;
/

-- create storage and catalog access credentials --
begin
dbms_cloud.create_credential(
  credential_name => '<catalogAuthCreds>',
  username        => '<dbUserName>',
  password        => '<dbUserPwd>'
);
end;
/

begin
  dbms_cloud.create_credential(
    credential_name => '<storageCredentialName>',
    username        => '<s3accessKey>',
    password        => '<s3secretKey>'
  );
end;
/

-- XT Creation with AI Cat Syntax --
-- <catalogURI> eg: https://<dbIdentifierString>.adb.<region>.oraclecloudapps.com/catalog
-- <authUri> eg: https://<dbIdentifierString>.adb.<region>.oraclecloudapps.com/catalog/v1/auth/token

begin
dbms_cloud.create_external_table(
  table_name      => '<extTableName>',
  credential_name => '<storageCredentialName>',
  format          => '{
    "access_protocol": {
      "protocol_type": "iceberg",
      "protocol_config": {
        "iceberg_catalog_type": "oracle_ai_data_catalog",
        "rest_catalog_endpoint":"<catalogURI>",
        "rest_authentication": {
          "rest_auth_cred": "<catalogAuthCreds>",
          "rest_auth_endpoint":"<authUri>"
        },
        "table_path": ["<namespaceName>", "<tableName>"]
      }
    }
  }'
);
end;
/

select * from <extTableName>;

For more information see Query Apache Iceberg Tables.

Mount and Query AI Data Catalog in DBMS Catalog

Open SQL worksheet for your Autonomous AI Database instance and execute the following:

-- setup ACL Rules --
-- for XT to communicate to ai catalog instance
-- for XT to communicate to object storage bucket, OCI in this example.

BEGIN
dbms_network_acl_admin.append_host_ace(
host => '*.oraclecloudapps.com',
lower_port => 443,
upper_port => 443,
ace => xs$ace_type(
privilege_list => xs$name_list('http', 'http_proxy'),
principal_name =>  'ADMIN',
principal_type => xs_acl.ptype_db));

dbms_network_acl_admin.append_host_ace(
host => '*.oci.customer-oci.com',
lower_port => 443,
upper_port => 443,
ace => xs$ace_type(
privilege_list => xs$name_list('http', 'http_proxy'),
principal_name =>  'ADMIN',
principal_type => xs_acl.ptype_db));
END;
/

-- create bearer token and catalog access credentials --
-- <authUri> eg: https://<dbIdentifierString>.adb.<region>.oraclecloudapps.com/catalog/v1/auth/token
BEGIN
    DBMS_SHARE.CREATE_BEARER_TOKEN_CREDENTIAL(
        CREDENTIAL_NAME => '<bearerTokenCredName>',
        BEARER_TOKEN    => 'BEARER_TOKEN',
        TOKEN_ENDPOINT  => '<authUri>',
        CLIENT_ID       => '<dbUserName>',
        CLIENT_SECRET   => '<dbUserPwd>',
        TOKEN_SCOPE     => 'PRINCIPAL_ROLE:ALL'
    );
END;
/

begin
  dbms_cloud.create_credential(
    credential_name => '<storageCredentialName>',
    username        => '<s3accessKey>',
    password        => '<s3secretKey>'
  );
end;
/

-- XT Creation with AI Cat Syntax --
-- <catalogURIForIceberg> eg: https://<dbIdentifierString>.adb.<region>.oraclecloudapps.com/catalog/v1
-- <authUri> eg: https://<dbIdentifierString>.adb.<region>.oraclecloudapps.com/catalog/v1/auth/token

-- DBMS_CATALOG with AI CAT --
BEGIN
dbms_catalog.mount_iceberg(
catalog_name             => '<catalogName>',
endpoint                 => '<catalogURIforIceberg>',
catalog_credential       => '<bearerTokenCredentialName>',
data_storage_credential  => '<storageCredentialName>',
catalog_type             => 'ICEBERG_ORACLE');
END;
/

-- sample queries once catalog is mounted
-- show mounted catalogs
SELECT c.catalog_name AS mounted_catalog_name, c.catalog_type AS mounted_catalog_type
FROM user_mounted_catalogs c
ORDER BY c.catalog_name;

-- show schemas
select schema_name from dbms_catalog.get_schemas('<catalogName>');

-- show tables
select table_name from dbms_catalog.get_tables('<catalogName>', '<schemaName>');

-- query existing tables
select FIRST_NAME, LAST_NAME from "<schemaName>"."<tableName>"@<catalogName> where rownum < 10;

-- create new table
CREATE ICEBERG TABLE  "<schemaName>"."<tableName>"
(   type  STRING,
    address   STRING
)
WITHIN CATALOG "<catalogName>"
STORAGE LOCATION "s3://<ociBucketNameUsedInStep#2>/<schemaName>/<tableName>/";

-- insert into table;
create table TESTLOCAL (type varchar2(10), address varchar2(10));
insert into TESTLOCAL values ('Alan', 'Matthews');
commit;

-- insert into iceberg table via ai catalog from local table
INSERT INTO "<schemaName>"."<tableName>"@<catalogName> select * from TESTLOCAL;

-- query the table in ai cat
select * from "<schemaName>"."<tableName>"@<catalogName>;

select DBMS_CATALOG.GENERATE_TABLE_SELECT('<catalogName>', '"<schemaName>"', '"<tableName>"') from dual;

Use AI Data Catalog as a Catalog Provider in Oracle Data Transforms

You can use Data Transforms to connect to the AI Data Catalog REST catalog and load data into Apache Iceberg tables. See Creating an Apache Iceberg Connection using Oracle AI Data Catalog for information.