Summary of DBMS

Summary of DBMS_CATALOG

This section covers the DBMS_CATALOG subprograms provided with Autonomous AI Database.

Catalog Mounting and Unmounting

Subprogram	Description
mount_db_link	This procedure mounts a catalog based on a database link.
mount_shares	This procedure mounts multiple catalogs from a share provider, with overloads to support different input types.
mount_share	This procedure mounts a single catalog based on a share.
mount_virtual_catalog	This procedure creates and mounts a new virtual catalog.
mount_data_catalog	This procedure mounts a data catalog such as AWS GLUE or OCI Data Catalog.
mount_iceberg	This procedure mounts an Iceberg catalog with specific configurations.
unmount	This procedure unmounts an existing external catalog.

Catalog Property Management

Subprogram	Description
get_catalog_property and get_catalog_property_clob	These procedures retrieves property values for a catalog.
update_catalog_property and update_catalog_property_clob	These procedures updates property values for a catalog.
get_schema_property and get_schema_property_clob	These procedures retrieves schema property values.
update_schema_property and update_schema_property_clob	These procedures updates schema property values.
get_table_property and get_table_property_clob	These procedures retrieves table property values.
update_table_property and update_table_property_clob	These procedures updates table property values.

Credential Management

Subprogram	Description
add_required_credential	This procedure defines a required credential for a catalog.
remove_required_credential	This procedure removes a required credential from a catalog.
rename_required_credential	This procedure renames a required credential.
update_required_credential_property	This procedure updates a property of a required credential.
get_required_credential_property	This procedure retrieves a property of a required credential.
set_local_credential	This procedure specifies a local credential for a required credential.
get_local_credential_map	This procedure retrieves mappings between required credentials and local credentials, with overloads for various output formats.

Catalog Entity Management

Subprogram	Description
create_cloud_storage_link and drop_cloud_storage_link	These procedure manages cloud storage links within a catalog.
create_external_table	This procedure creates an external table in a catalog.
drop_schema and drop_table	This procedure drops schemas and tables from a catalog.
get_tables, get_schemas and get_objects	This procedure retrieves metadata for tables, schemas, and objects within a catalog using pipelined functions.

Data Access and Caching

Subprogram	Description
generate_table_select	This procedure creates a `SELECT` statement for a logical table, with overloads for both procedure and function.
open_table_cursor	This procedure opens a cursor to return data from a logical table.
flush_catalog_cache and prefill_catalog_cache	This procedure manages catalog metadata caching.

Synchronization and Sharing

Subprogram	Description
create_synchronized_schemas and drop_synchronized_schemas	These procedure manages synchronized schemas with remote catalog definitions.
update_synchronized_schema_property	This procedure updates properties of synchronized schemas.
grant_to_recipient and revoke_from_recipient	These procedure manages access permissions for catalog recipients.

MOUNT_DB_LINK Procedure

This procedure mounts an external catalog based on a database link.

Syntax

PROCEDURE mount_db_link
(
  catalog_name 	IN VARCHAR2,
  db_link 		IN VARCHAR2,
  enabled 		IN BOOLEAN := TRUE
);

Parameters

Parameter	Description
`catalog_name`	The name of the new catalog. This will be converted to uppercase.
`db_link`	The database link to use for mounting the catalog.
`enabled`	Indicates if the catalog is enabled for search. Defaults to `TRUE`.

MOUNT_SHARES Procedure

This procedure mounts multiple catalogs based on shares from a share provider.

Syntax

PROCEDURE mount_shares
(
  share_provider 		IN VARCHAR2,
  shares 				IN SYS.JSON_ARRAY_T,
  created_catalogs 		IN OUT NOCOPY SYS.JSON_ARRAY_T,
  share_provider_owner 	IN VARCHAR2 := NULL
);

Parameters

Parameter	Description
`share_provider`:	The name of the share provider.
`shares`	A JSON array defining the list of shares to mount. Each element specifies properties like `shareName`, `required`, `shareNameRule`, `catalogName`, `skipExisting`, `placeholder`, and `enabled`.
`created_catalogs`	A JSON array of newly created catalogs, returned as output with properties like `shareName` and `catalogName`.
`share_provider_owner`	The owner of the share provider. Defaults to the current catalog if NULL.

Syntax of Second Procedure

PROCEDURE mount_shares
(
  share_provider 		IN VARCHAR2,
  shares 				IN SYS.JSON_ARRAY_T,
  share_provider_owner 	IN VARCHAR2 := NULL
);

Syntax of Third Procedure

PROCEDURE mount_shares
(
  share_provider 		IN VARCHAR2,
  shares 				IN CLOB := '[{shareName:"%"}]',
  share_provider_owner 	IN VARCHAR2 := NULL
);

Parameters of the Second Procedure

Parameters are Identical to Procedure 1, except without created_catalogs.

Parameters of the Third Procedure

Parameters are Identical to Signature 1, except shares is a CLOB with a default value that includes all shares.

Example

/****************** Share Catalog ******************/
BEGIN
  -- Create a share provider
  dbms_share.create_or_replace_share_provider(
    provider_name        => 'MY_SHARE_PROVIDER',
    endpoint             => 'https://...');
  dbms_cloud.create_credential(
    credential_name      => 'SHARE_PROVIDER_CREDENTIAL', ...);
  dbms_share.set_share_provider_credential(
    provider_name        => 'MY_SHARE_PROVIDER',
    share_credential     => 'SHARE_PROVIDER_CREDENTIAL');

  -- Mount shares as catalogs
  dbms_catalog.mount_shares(
    share_provider       => 'MY_SHARE_PROVIDER');
END;
/

MOUNT_SHARE Procedure

This procedure mounts a catalog based on a single share.

Syntax

PROCEDURE mount_share
(
  catalog_name 				IN VARCHAR2,
  share_provider 			IN VARCHAR2,
  share_name 				IN VARCHAR2,
  share_provider_owner 		IN VARCHAR2 := NULL,
  enabled 					IN BOOLEAN := TRUE
);

Parameters

Parameter	Description
`catalog_name`	The name of the new catalog. This will be converted to uppercase.
`share_provider`	The name of the share provider.
`share_name`	The name of the share to mount.
`share_provider_owner`	The owner of the share provider. Defaults to the current catalog if NULL.
`enabled`	Indicates if the catalog is enabled for search. Defaults to TRUE.

Example

/****************** Share Catalog ******************/
BEGIN
  -- Create a share provider
  dbms_share.create_or_replace_share_provider(
    provider_name        => 'MY_SHARE_PROVIDER',
    endpoint             => 'https://...');
  dbms_cloud.create_credential(
    credential_name      => 'SHARE_PROVIDER_CREDENTIAL', ...);
  dbms_share.set_share_provider_credential(
    provider_name        => 'MY_SHARE_PROVIDER',
    share_credential     => 'SHARE_PROVIDER_CREDENTIAL');

  -- Mount a share catalog
  dbms_catalog.mount_share(
    catalog_name         => 'SHARE_CAT',
    share_provider       => 'MY_SHARE_PROVIDER',
    share_name           => 'MY_SHARE_1');
END;
/

MOUNT_VIRTUAL_CATALOG Procedure

This procedure creates and mounts a new virtual catalog.

Syntax

PROCEDURE mount_virtual_catalog
(
  catalog_name 	IN VARCHAR2,
  enabled 		IN BOOLEAN := TRUE
);

Parameters

Parameter	Description
`catalog_name`	The name of the new catalog. This will be converted to uppercase.
`enabled`	Indicates if the catalog is enabled for search. Defaults to TRUE.

MOUNT_DATA_CATALOG Procedure

This procedure mounts a data catalog (e.g., AWS GLUE or OCI Data Catalog).

Syntax

PROCEDURE mount_data_catalog
(
  catalog_name 				IN VARCHAR2,
  data_catalog_type 		IN VARCHAR2,
  data_catalog_region 		IN VARCHAR2,
  data_catalog_credential 	IN VARCHAR2,
  data_catalog_id 			IN VARCHAR2 := NULL,
  data_storage_credential 	IN VARCHAR2 := NULL,
  enabled 					IN BOOLEAN := TRUE
);

Parameters

Parameter	Description
`catalog_name`	The name of the new catalog. This will be converted to uppercase.
`data_catalog_type`	Type of data catalog (e.g., OCI_DCAT or AWS_GLUE).
`data_catalog_region`	The cloud resource region of the data catalog.
`data_catalog_credential`	The local credential used to access the data catalog.
`data_catalog_id`	The data catalog name or identifier. Defaults to NULL.
`data_storage_credential`	It specifies the local credential for data access. Defaults to NULL. This field is optional.
`enabled`	Indicates if the catalog is enabled for search. Defaults to `TRUE`.

Example

/****************** Glue Catalog ******************/
BEGIN
  -- Create a credential capable of accessing a glue catalog
  dbms_cloud.create_credential('GLUE_CATALOG_CREDENTIAL', ...); 

  -- Mount a glue catalog
  dbms_catalog.mount_data_catalog(
    catalog_name             => 'GLUE_CAT',
    data_catalog_type        => 'AWS_GLUE',
    data_catalog_region      => 'us-east-1', 
    data_catalog_credential  => 'GLUE_CATALOG_CREDENTIAL');
END;
/

MOUNT_ICEBERG Procedure

This procedure mounts an Iceberg catalog.

Syntax

See Iceberg REST Catalog Spec for more information on Iceberg Catalog.

PROCEDURE mount_iceberg
(
  catalog_name 				IN VARCHAR2,
  endpoint 					IN VARCHAR2,
  catalog_credential 		IN VARCHAR2,
  data_storage_credential 	IN VARCHAR2,
  configuration 			IN SYS.JSON_OBJECT_T := NULL,
  enabled 					IN BOOLEAN := TRUE,
  catalog_type 				IN VARCHAR2 := 'ICEBERG_GENERIC'
);

Parameters

Parameter	Description
`catalog_name`	The name of the new catalog. This will be converted to uppercase.
`endpoint`	The Iceberg base server URL.
`catalog_credential`	The credential used to access the Iceberg catalog.
`data_storage_credential`	The credential used to access the data.
`configuration`	This describes the catalog configuration as a JSON object. The following are supported configurations: `namespacePath`: Defaults to `NULL`. `namespaceSeparator`: Defaults to dot (.) for Unity, `%1F` for other Iceberg catalogs. `isCaseSensitive`: Defaults to `FALSE` for Unity, `TRUE` for other Iceberg catalogs) `isPublicCatalog`: Defaults to `FALSE`. `bucketRegion`: Defaults to `NULL`. It is relevant only if the catalog uses S3 storage.
`enabled`	This indicates if the catalog is enabled for search. Defaults to `TRUE`.
`catalog_type`	Type of Iceberg catalog (e.g., `ICEBERG_GENERIC`, `ICEBERG_POLARIS`, `ICEBERG_UNITY`). Defaults to `ICEBERG_GENERIC`.

Example

/****************** Iceberg Catalog - Unity ******************/
DECLARE
  config      JSON_OBJECT_T := JSON_OBJECT_T();
BEGIN
  -- Create a credential capable of accessing an external iceberg REST catalog
  dbms_share.create_bearer_token_credential(
    credential_name => 'ICEBERG_CATALOG_CREDENTIAL',
    token_endpoint => 'https://.../v1/tokens',
    client_id => ...,
    client_secret => ...,
    token_scope => 'all-apis');

  -- Create a credential capable of accessing the bucket where the 
  -- iceberg data files are stored.
  dbms_cloud.create_credential('ICEBERG_STORAGE_CRED', ...); 

  -- Mount a iceberg catalog
  config.put('namespacePath', 'sales_catalog');
  dbms_catalog.mount_iceberg(
    catalog_name             => 'ICEBERG_CAT',
    endpoint                 => 'https://...//api/2.1/unity-catalog/iceberg/v1',
    catalog_credential       => 'ICEBERG_CATALOG_CREDENTIAL',
    data_storage_credential  => 'ICEBERG_STORAGE_CRED',
    configuration            => config,
    catalog_type             => 'ICEBERG_UNITY');
END;
/

/****************** Iceberg Catalog - Polaris ******************/
DECLARE
  config      JSON_OBJECT_T := JSON_OBJECT_T();
BEGIN
  -- Create a credential capable of accessing an external iceberg REST catalog
  dbms_share.create_bearer_token_credential(
    credential_name => 'ICEBERG_CATALOG_CREDENTIAL',
    token_endpoint => 'https://.../v1/oauth/tokens',
    client_id => ...,
    client_secret => ...,
    token_scope => 'PRINCIPAL_ROLE:ALL');

  -- Create a credential capable of accessing the bucket where the 
  -- iceberg data files are stored.
  dbms_cloud.create_credential('ICEBERG_STORAGE_CRED', ...); 

  -- Mount a iceberg catalog
  config.put('namespacePath', 'sales_catalog');
  dbms_catalog.mount_iceberg(
    catalog_name             => 'ICEBERG_CAT',
    endpoint                 => 'https://.../polaris/api/catalog/v1',
    catalog_credential       => 'ICEBERG_CATALOG_CREDENTIAL',
    data_storage_credential  => 'ICEBERG_STORAGE_CRED',
    configuration            => config,
    catalog_type             => 'ICEBERG_POLARIS');
END;
/

UNMOUNT Procedure

This procedure unmounts an external catalog.

Syntax

PROCEDURE unmount
(
  catalog_name IN VARCHAR2
);

Parameters

Parameter	Description
`catalog_name`	The name of an existing catalog to unmount. This will be converted to uppercase.

GET_CATALOG_PROPERTY Procedure

This procedure returns the value of a property for a catalog.

Syntax

FUNCTION get_catalog_property
(
  catalog_name 		IN VARCHAR2,
  catalog_property 	IN VARCHAR2
)
RETURN VARCHAR2;

Parameters

Parameter Description

catalog_name

The name of the existing catalog. This will be converted to uppercase.

catalog_property

This describes the the property to be retrieved.(for example, PROP_CACHE_DURATION, PROP_IS_ENABLED).

The value can be any of the following constants:

PROP_CACHE_ASYNC
PROP_CACHE_DURATION
PROP_CACHE_ENABLED
PROP_CONFIGURATION
PROP_CUSTOM
PROP_DCAT_TYPE
PROP_DEFAULT_SCHEMA
PROP_IS_ENABLED
PROP_METADATA

See Catalog Types and Constants for more information.

GET_CATALOG_PROPERTY_CLOB Procedure

This procedure returns the value of a property for a catalog in CLOB format.

Syntax

FUNCTION get_catalog_property_clob
(
  catalog_name 		IN VARCHAR2,
  catalog_property 	IN VARCHAR2
)
RETURN CLOB;

Parameters

Parameter Description

catalog_name

The name of the existing catalog. This will be converted to uppercase.

catalog_property

This describes the the property to be retrieved (for example, PROP_CACHE_DURATION, PROP_IS_ENABLED).

The value can be any of the following constants:

PROP_CACHE_ASYNC
PROP_CACHE_DURATION
PROP_CACHE_ENABLED
PROP_CONFIGURATION
PROP_CUSTOM
PROP_DCAT_TYPE
PROP_DEFAULT_SCHEMA
PROP_IS_ENABLED
PROP_METADATA

See Catalog Types and Constants for more information.

UPDATE_CATALOG_PROPERTY Procedure

This procedure updates the value of a property for a catalog.

Syntax

PROCEDURE update_catalog_property
(
  catalog_name 		IN VARCHAR2,
  catalog_property 	IN VARCHAR2,
  new_value 		IN VARCHAR2
);

Parameters

Parameter Description

catalog_name

The name of the existing catalog. This will be converted to uppercase.

catalog_property

This describes the property to be updated (for example, PROP_CACHE_DURATION, PROP_IS_ENABLED).

The value can be any of the following constants:

PROP_CACHE_ASYNC
PROP_CACHE_DURATION
PROP_CACHE_ENABLED
PROP_CONFIGURATION
PROP_CUSTOM
PROP_DEFAULT_SCHEMA
PROP_IS_ENABLED
PROP_METADATA

See Catalog Types and Constants for more information.

new_value

The new property value.

UPDATE_CATALOG_PROPERTY_CLOB Procedure

This procedure updates the value of a property for a catalog in CLOB.

Syntax

PROCEDURE update_catalog_property_clob
(
  catalog_name 		IN VARCHAR2,
  catalog_property 	IN VARCHAR2,
  new_value 		IN CLOB
);

Parameters

Parameter	Description
`catalog_name`	The name of the existing catalog. This will be converted to uppercase.
`catalog_property`	This describes the the property to be retrieved (for example, `PROP_CACHE_DURATION`, `PROP_IS_ENABLED`).
`new_value`	The new property value in CLOB.

UPDATE_TABLE_PROPERTY_CLOB Procedure

This procedure updates the value of a property for a table in CLOB format.

Syntax

PROCEDURE update_table_property_clob
(
  catalog_name 		IN VARCHAR2,
  schema_name 		IN VARCHAR2,
  table_name		IN VARCHAR2,
  table_property 	IN VARCHAR2,
  new_value 		IN CLOB
);

Parameters

Parameter	Description
`catalog_name`	The name of the existing catalog. This will be converted to uppercase.
`schema_name`	The name of the schema.
`table_name`	The name of the table.
`table_property`	The property to update.
`new_value`	The new property value in CLOB.

GET_SCHEMA_PROPERTY Procedure

This procedure returns the value of a property for a schema.

Syntax

FUNCTION get_schema_property
(
  catalog_name 		IN VARCHAR2,
  schema_name 		IN VARCHAR2,
  schema_property 	IN VARCHAR2
)

Parameters

Parameter	Description
`catalog_name`	The name of the existing catalog. This will be converted to uppercase.
`schema_name`	The name of the schema.
`schema_property`	This describes the property to be retrieved (for example, `PROP_CUSTOM`, `PROP_METADATA`).

GET_SCHEMA_PROPERTY_CLOB Procedure

This procedure returns the value of a property for a schema in CLOB format.

Syntax

FUNCTION get_schema_property_clob
(
  catalog_name 		IN VARCHAR2,
  schema_name 		IN VARCHAR2,
  schema_property 	IN VARCHAR2
)
RETURN CLOB;

Parameters

Parameter	Description
`catalog_name`	The name of the existing catalog. This will be converted to uppercase.
`schema_name`	The name of the schema.
`schema_property`	This describes the property to be retrieved (for example, `PROP_CUSTOM`, `PROP_METADATA`).

UPDATE_SCHEMA_PROPERTY Procedure

This procedure returns the value of a property for a schema.

Syntax

PROCEDURE update_schema_property
(
  catalog_name 		IN VARCHAR2,
  schema_name 		IN VARCHAR2,
  schema_property 	IN VARCHAR2,
  new_value 		IN VARCHAR2
);

Parameters

Parameter	Description
`catalog_name`	The name of the existing catalog. This will be converted to uppercase.
`schema_name`	The name of the schema.
`schema_property`	This describes the property to be retrieved (for example, `PROP_CUSTOM`, `PROP_METADATA`).
`new_value`	The new property value.

GET_TABLE_PROPERTY Procedure

This procedure returns the value of a property for a table.

Syntax

FUNCTION get_table_property
(
  catalog_name 		IN VARCHAR2,
  schema_name 		IN VARCHAR2,
  table_name 		IN VARCHAR2,
  table_property 	IN VARCHAR2
)
RETURN VARCHAR2;

Parameters

Parameter	Description
`catalog_name`	The name of the existing catalog. This will be converted to uppercase.
`schema_name`	The name of the schema.
`table_property`	The property to retrieve.
`table_name`	The name of the table.

GET_TABLE_PROPERTY_CLOB Procedure

This procedure returns the value of a property for a table in CLOB format.

Syntax

FUNCTION get_table_property_clob
(
  catalog_name 		IN VARCHAR2,
  schema_name 		IN VARCHAR2,
  table_name 		IN VARCHAR2,
  table_property 	IN VARCHAR2
)
RETURN CLOB;

Parameters

Parameter	Description
`catalog_name`	The name of the existing catalog. This will be converted to uppercase.
`schema_name`	The name of the schema.
`table_name`	The name of the table.
`table_property`	The property to update (e.g., `PROP_CUSTOM`).

UPDATE_TABLE_PROPERTY Procedure

This procedure updates the value of a property for a table.

Syntax

PROCEDURE update_table_property
(
  catalog_name 		IN VARCHAR2,
  schema_name 		IN VARCHAR2,
  table_name 		IN VARCHAR2,
  table_property 	IN VARCHAR2,
  new_value 		IN VARCHAR2
);

Parameters

Parameter	Description
`catalog_name`	The name of the existing catalog. This will be converted to uppercase.
`schema_name`	The name of the schema.
`table_name`	The name of the table.
`table_property`	The property to update.
`new_value`	The new property value.

UPDATE_TABLE_PROPERTY_CLOB Procedure

This procedure updates the value of a property for a table in CLOB format.

Syntax

PROCEDURE update_table_property_clob
(
  catalog_name 		IN VARCHAR2,
  schema_name 		IN VARCHAR2,
  table_name		IN VARCHAR2,
  table_property 	IN VARCHAR2,
  new_value 		IN CLOB
);

Parameters

Parameter	Description
`catalog_name`	The name of the existing catalog. This will be converted to uppercase.
`schema_name`	The name of the schema.
`table_name`	The name of the table.
`table_property`	The property to update.
`new_value`	The new property value in CLOB.

ADD_REQUIRED_CREDENTIAL Procedure

This procedure defines a credential required for using the catalog.

Syntax

PROCEDURE add_required_credential
(
  catalog_name 			IN VARCHAR2,
  required_credential 	IN VARCHAR2,
  local_credential	 	IN VARCHAR2 := NULL
  credential_type       IN VARCHAR2 := NULL
);

Parameters

Parameter	Description
`catalog_name`	The name of the existing catalog. This will be converted to uppercase.
`required_credential`	The name of the required credential.
`local_credential`	It is an optional local credential to use. Defaults to `NULL`.
`credential_type`	This field is optional. This parameter specifies the type of authentication mechanism being used to access external services, such as object storage or other databases. For example, oci, aws, azure or a slack_credential.

See Catalog Types and Constants for more information.

REMOVE_REQUIRED_CREDENTIAL Procedure

This procedure removes a required credential from a catalog.

Syntax

PROCEDURE remove_required_credential
(
  catalog_name 			IN VARCHAR2,
  required_credential 	IN VARCHAR2
);

Parameters

Parameter	Description
`catalog_name`	The name of the existing catalog. This will be converted to uppercase.
`required_credential`	The name of the required credential to remove.

RENAME_REQUIRED_CREDENTIAL Procedure

This procedure renames a required credential from a catalog.

Syntax

PROCEDURE rename_required_credential
(
  catalog_name 			IN VARCHAR2,
  old_credential_name 	IN VARCHAR2,
  new_credential_name 	IN VARCHAR2
);

Parameters

Parameter	Description
`catalog_name`	The name of the existing catalog. This will be converted to uppercase.
`old_credential_name`	The old name of the required credential.
`new_credential_name`	The new name of the required credential.

UPDATE_REQUIRED_CREDENTIAL_PROPERTY Procedure

This procedure updates a property of a named required credential.

Syntax

PROCEDURE update_required_credential_property
(
  catalog_name 			IN VARCHAR2,
  required_credential 	IN VARCHAR2,
  credential_property 	IN VARCHAR2,
  new_value 			IN VARCHAR2
);

Parameters

Parameter	Description
`catalog_name`	The name of the existing catalog. This will be converted to uppercase.
`required_credential`	The name of the required credential.
`credential_property`	The property name (e.g., `PROP_REQUIRED_CREDENTIAL_DESCRIPTION`, `PROP_REQUIRED_CREDENTIAL_VAULT_SECRET`).
`new_value`	The new value for the property.

GET_REQUIRED_CREDENTIAL_PROPERTY Procedure

This procedure retrieves a property of a named required credential.

Syntax

FUNCTION get_required_credential_property
(
  catalog_name 			IN VARCHAR2,
  required_credential 	IN VARCHAR2,
  credential_property 	IN VARCHAR2
)
RETURN VARCHAR2;

Parameters

Parameter	Description
`catalog_name`	The name of the existing catalog. This will be converted to uppercase.
`required_credential`	The name of the required credential.
`credential_property`	The property name (e.g., `PROP_REQUIRED_CREDENTIAL_DESCRIPTION`, `PROP_REQUIRED_CREDENTIAL_VAULT_SECRET`).

SET_LOCAL_CREDENTIAL Procedure

This procedure specifies a local credential to use when consuming a catalog.

Syntax

PROCEDURE set_local_credential
(
  catalog_name 			IN VARCHAR2,
  required_credential 	IN VARCHAR2,
  local_credential 		IN VARCHAR2
);

Parameters

Parameter	Description
`catalog_name`	The name of the existing catalog. This will be converted to uppercase.
`required_credential`	The name of the required credential.
`local_credential`	The credential to use.

GET_LOCAL_CREDENTIAL_MAP (Overloaded) Procedure

This procedure retrieves a map of required credentials to local credentials.

Syntax 1

PROCEDURE get_local_credential_map
(
  catalog_name 		IN VARCHAR2,
  credential_map 	OUT NOCOPY credential_info_map
);

Parameters

Parameter	Description
`catalog_name`	The name of the existing catalog. This will be converted to uppercase.
`credential_map`	The populated map of credentials.

Syntax 2

FUNCTION get_local_credential_map
(
  catalog_name 	IN VARCHAR2,
  show_errors 	IN NUMBER := 0,
  pretty_json 	IN NUMBER := 0
)
RETURN CLOB;

Parameters

Parameter	Description
`catalog_name`	The name of the existing catalog.
`show_errors`	This displays error messages for invalid local credentials. Defaults to 0.
`pretty_json`	Format the JSON with the PRETTY option. Defaults to `0`.

Syntax 3

PROCEDURE get_local_credential_map
(
  catalog_id 		IN NUMBER,
  credential_map 	OUT NOCOPY SYS.JSON_OBJECT_T
);

Parameters

Parameter	Description
`catalog_id`	The ID of an existing catalog from the `CATALOG_ID` column in `ALL_MOUNTED_CATALOGS`.
`credential_map`	The populated map as a JSON object.

CREATE_CLOUD_STORAGE_LINK Procedure

This procedure creates a cloud storage link in a catalog.

Syntax

PROCEDURE create_cloud_storage_link
(
  catalog_name 			IN VARCHAR2,
  schema_name 			IN VARCHAR2,
  storage_link_name 	IN VARCHAR2,
  uri 					IN VARCHAR2,
  required_credential 	IN VARCHAR2 := NULL,
  enabled 				IN BOOLEAN := TRUE,
  replace_if_exists 	IN BOOLEAN := FALSE
);

Parameters

Parameter	Description
`catalog_name`	The name of the existing catalog. This will be converted to uppercase.
`schema_name:`	The schema name.
`storage_link_name`	The storage link name.
`uri`	The bucket URI.
`required_credential`	The required credential (or NULL for public buckets). Defaults to NULL.
`enabled`	Indicates if the storage link is enabled for search. Defaults to TRUE.
`replace_if_exists`	Replace the link if it already exists. Defaults to FALSE.

DROP_CLOUD_STORAGE_LINK Procedure

This procedure drops a cloud storage link from a catalog.

Syntax

PROCEDURE drop_cloud_storage_link
(
  catalog_name 		IN VARCHAR2,
  schema_name 		IN VARCHAR2,
  storage_link_name IN VARCHAR2
);

Parameters

Parameter	Description
`catalog_name`	The name of the existing catalog. This will be converted to uppercase.
`schema_name:`	The schema name.
`storage_link_name`	The storage link name.

CREATE_EXTERNAL_TABLE Procedure

This procedure creates an external table in a catalog.

Syntax

PROCEDURE create_external_table
(
  catalog_name 			IN VARCHAR2,
  schema_name 			IN VARCHAR2,
  table_name 			IN VARCHAR2,
  storage_link_name 	IN VARCHAR2,
  file_uri_list 		IN CLOB,
  column_list 			IN CLOB := NULL,
  field_list 			IN CLOB := NULL,
  table_format 			IN CLOB := NULL,
  credential_name 		IN VARCHAR2 := NULL,
  replace_if_exists 	IN BOOLEAN := FALSE
);

Parameters

Parameter	Description
`catalog_name`	The name of the existing catalog. This will be converted to uppercase.
`schema_name`	The schema name.
`table_name`	The table name.
`storage_link_name`	The storage link name.
`file_uri_list`	The relative file list as a CLOB.
`column_list`	Optional column list. Defaults to NULL.
`field_list`	Optional field list. Defaults to NULL.
`table_format`	Optional format information. Defaults to NULL.
`credential_name`	The required credential. Defaults to NULL.
`replace_if_exists`	Replace the table if it already exists. Defaults to FALSE.

DROP_SCHEMA Procedure

This procedure drops a schema from a catalog along with all its contents.

Syntax

PROCEDURE drop_schema
(
  catalog_name 	IN VARCHAR2,
  schema_name 	IN VARCHAR2
);

Parameters

Parameter	Description
`catalog_name`	The name of the existing catalog. This will be converted to uppercase.
`schema_name:`	The schema name to drop.

DROP_TABLE Procedure

This procedure drops a table from a catalog.

Syntax

PROCEDURE drop_table
(
  catalog_name 	IN VARCHAR2,
  schema_name 	IN VARCHAR2,
  table_name 	IN VARCHAR2
);

Parameters

Parameter	Description
`catalog_name`	The name of the existing catalog. This will be converted to uppercase.
`schema_name`	The schema name.
`table_name`	The table name.

CREATE_SCHEMA Procedure

This procedure creates a new schema in a catalog.

Syntax

PROCEDURE create_schema
(
  catalog_name 	IN VARCHAR2,
  schema_name 	IN VARCHAR2
);

Parameters

Parameter	Description
`catalog_name`	The name of the existing catalog. This will be converted to uppercase.
`schema_name`	The schema name.

GET_TABLES Procedure

This procedure fetches all tables for a catalog.

Syntax

FUNCTION get_tables
(
  catalog_name 		IN VARCHAR2,
  schema_name 		IN VARCHAR2 := NULL,
  table_name 		IN VARCHAR2 := NULL,
  conditions 		IN VARCHAR2 := NULL,
  flags 			IN NUMBER := 0,
  result_limit 		IN NUMBER := NULL,
  column_flags 		IN NUMBER := 0
)
RETURN catalog_tables PIPELINED;

Parameters

Parameter	Description
`catalog_name`	The name of the existing catalog. This will be converted to uppercase.
`schema_name`	Optional schema name. Defaults to `NULL`.
`table_name`	Optional table name. Defaults to `NULL`.
`conditions`	Optional additional conditions using lineage syntax. Defaults to `NULL`.
`result_limit`	Optional result limit. Defaults to NULL.
`column_flags`	Optional column flags (e.g., `FETCH_PROPERTIES`, `FETCH_METADATA`). Defaults to 0.

Note:

RECORD types describe structured data with named fields in databases.
You have to query the database's information schema, system catalogs, or use database introspection tools to see the structure of RECORD types.

GET_SCHEMAS Procedure

This procedure fetches all schemas for a catalog.

Syntax

FUNCTION get_schemas
(
  catalog_name 	IN VARCHAR2,
  schema_name 	IN VARCHAR2 := NULL,
  conditions 	IN VARCHAR2 := NULL,
  flags 		IN NUMBER := 0,
  result_limit 	IN NUMBER := NULL,
  column_flags 	IN NUMBER := 0
)
RETURN catalog_schemas PIPELINED;

Parameters

Parameter	Description
`catalog_name`	The name of the existing catalog. This will be converted to uppercase.
`schema_name`	Optional schema name. Defaults to `NULL`.
`conditions`	Optional additional conditions using lineage syntax. Defaults to `NULL`.
`result_limit`	Optional result limit. Defaults to NULL.
`column_flags`	Optional column flags (e.g., `FETCH_PROPERTIES`, `FETCH_METADATA`). Defaults to 0.

GET_OBJECTS Procedure

This procedure fetches all objects for a catalog.

Syntax

FUNCTION get_objects
(
  catalog_name 	IN VARCHAR2,
  schema_name 	IN VARCHAR2 := NULL,
  object_name 	IN VARCHAR2 := NULL,
  conditions 	IN VARCHAR2 := NULL,
  flags 		IN NUMBER := 0,
  result_limit 	IN NUMBER := NULL
)
RETURN catalog_objects PIPELINED;

Parameters

Parameter	Description
`catalog_name`	The name of the existing catalog. This will be converted to uppercase.
`schema_name`	Optional schema name. Defaults to `NULL`.
`object_name`	Optional object name. Defaults to `NULL`.
`conditions`	Optional additional conditions using lineage syntax. Defaults to `NULL`.
`result_limit`	Optional result limit. Defaults to NULL.

GENERATE_TABLE_SELECT (Overloaded) Procedure

This procedure generates a SELECT statement for a logical table.

Syntax

PROCEDURE generate_table_select
(
  catalog_name 		IN VARCHAR2,
  schema_name 		IN VARCHAR2,
  table_name 		IN VARCHAR2,
  stmt 				IN OUT NOCOPY CLOB,
  options 			IN CLOB := NULL,
  prev_gen_sql 		IN CLOB := NULL,
  prev_ts 			IN TIMESTAMP WITH TIME ZONE := NULL
);

Parameters

Parameter	Description
`catalog_name`	The name of the existing catalog. This will be converted to uppercase.
`schema_name`	The schema name.
`table_name`	The table name.
`stmt`	The generated SELECT statement as output.
`options`	Generation options as a JSON object (e.g., distinct, rowLimit, select, orderBy). Defaults to NULL.
`prev_gen_sql`	The previously generated SELECT statement, if known. Defaults to NULL.
`prev_ts`	The previous generation timestamp, if known. Defaults to NULL.

Syntax

FUNCTION generate_table_select
(
  catalog_name 	IN VARCHAR2,
  schema_name 	IN VARCHAR2,
  table_name 	IN VARCHAR2,
  options 		IN CLOB := NULL,
  prev_gen_sql 	IN CLOB := NULL,
  prev_ts 		IN TIMESTAMP WITH TIME ZONE := NULL
)
RETURN CLOB;

Parameters

Parameter	Description
`catalog_name`	The name of the existing catalog. This will be converted to uppercase.
`schema_name`	The schema name.
`table_name`	The table name.
`stmt`	The generated SELECT statement as output.
`options`	Generation options as a JSON object (e.g., distinct, rowLimit, select, orderBy). Defaults to NULL.
`prev_gen_sql`	The previously generated SELECT statement, if known. Defaults to NULL.
`prev_ts`	The previous generation timestamp, if known. Defaults to NULL.

OPEN_TABLE_CURSOR Procedure

This procedure opens a cursor that returns data from a logical table.

Syntax

PROCEDURE open_table_cursor
(
  catalog_name 	IN VARCHAR2,
  schema_name 	IN VARCHAR2,
  table_name 	IN VARCHAR2,
  table_cursor 	IN OUT NOCOPY SYS_REFCURSOR,
  options 		IN CLOB := NULL
);

Parameters

Parameter	Description
`catalog_name`	The name of the existing catalog. This will be converted to uppercase.
`schema_name`	Optional schema name. Defaults to `NULL`.
`table_name`	Optional table name. Defaults to `NULL`.
`table_cursor`	The cursor to return data.
`options`	Generates options as a JSON object (e.g., rowLimit, select, orderBy). Defaults to NULL.

FLUSH_CATALOG_CACHE Procedure

This procedure flushes the catalog metadata cache,

Syntax

PROCEDURE flush_catalog_cache
(
  catalog_name 	IN VARCHAR2,
  auto_commit	IN BOOLEAN := TRUE
);

Parameters

Parameter	Description
`catalog_name`	The name of the existing catalog. This will be converted to uppercase.
`auto_commit`	Indicates if changes should be committed automatically. Defaults to TRUE.

PREFILL_CATALOG_CACHE Procedure

This procedure fills the cache for the specified catalog with current data.

Syntax

PROCEDURE prefill_catalog_cache
(
  catalog_name 	IN VARCHAR2,
  schema_name 	IN VARCHAR2 := NULL,
  auto_commit 	IN BOOLEAN := TRUE
);

Parameters

Parameter	Description
`catalog_name`	The name of the existing catalog. This will be converted to uppercase.
`schema_name`	The name of the schema.
`auto_commit`	Indicates if changes should be committed automatically. Defaults to TRUE.

CREATE_SYNCHRONIZED_SCHEMAS Procedure

This procedure creates local database schemas synchronized with remote catalog definitions.

Syntax

PROCEDURE create_synchronized_schemas
(
  catalog_name IN VARCHAR2,
  schema_names IN VARCHAR2 := NULL,
  restrictions IN CLOB := NULL
);

Parameters

Parameter	Description
`catalog_name`	The name of the existing catalog. This will be converted to uppercase.
`schema_name`	A comma-delimited list of schema names (quoted or unquoted). Defaults to NULL to synchronize all schemas.
`restrictions`	JSON document specifying the scope of catalog entities. Defaults to NULL.

DROP_SYNCHRONIZED_SCHEMAS Procedure

This procedure drops one or more synchronized schemas.

Syntax

PROCEDURE drop_synchronized_schemas
(
  catalog_name IN VARCHAR2,
  schema_names IN VARCHAR2 := NULL,
  restrictions IN CLOB := NULL
);

Parameters

Parameter	Description
`catalog_name`	The name of the existing catalog. This will be converted to uppercase.
`schema_name`	A comma-delimited list of schema names (quoted or unquoted). Defaults to NULL to synchronize all schemas.
`restrictions`	JSON document specifying the scope of catalog entities. Defaults to NULL.

UPDATE_SYNCHRONIZED_SCHEMA_PROPERTY Procedure

This procedure updates a property of synchronized schemas.

Syntax

PROCEDURE update_synchronized_schema_property
(
  catalog_name 	IN VARCHAR2,
  property_name IN VARCHAR2,
  new_value 	IN CLOB
);

Parameters

Parameter	Description
`catalog_name`	The name of the existing catalog. This will be converted to uppercase.
`property_name`	The property name to update.
`new_value`	The new value as a CLOB.

GRANT_TO_RECIPIENT Procedure

This procedure grants access on a catalog to a specific share recipient.

Syntax

PROCEDURE grant_to_recipient
(
  catalog_name 		IN VARCHAR2,
  recipient_name 	IN VARCHAR2
);

Parameters

Parameter	Description
`catalog_name`	The name of the existing catalog. This will be converted to uppercase.
`recipient_name`	The name of the share recipient (created by `DBMS_SHARE.CREATE_SHARE_RECIPIENT`).

REVOKE_FROM_RECIPIENT Procedure

This procedure revokes access on a catalog from a specific recipient.

Syntax

PROCEDURE revoke_from_recipient
(
  catalog_name 		IN VARCHAR2,
  recipient_name 	IN VARCHAR2,
  owner 			IN VARCHAR2 := NULL
);

Parameters

Parameter	Description
`catalog_name`	The name of the existing catalog. This will be converted to uppercase.
`recipient_name`	The name of the share recipient (created by `DBMS_SHARE.CREATE_SHARE_RECIPIENT`).
`owner`	The owner of both catalog and recipient. Defaults to `NULL`.