DBMS_CLOUD_IMPORT Package

DBMS_CLOUD_IMPORT facilitates the import of data from Oracle databases as well as from non-Oracle databases by using the Oracle Heterogeneous Gateway into Autonomous AI Database. During the import process, source data types are converted to Oracle-compatible data types. As a result, any limitations and restrictions of the Oracle Heterogeneous Gateway also apply to operations performed through this package.

Summary of DBMS_CLOUD_IMPORT Subprograms

This table summarizes the subprograms included in the DBMS_CLOUD_IMPORT package.

Subprogram	Description
CREATE_IMPORT_TASK Procedure	Creates an import task for importing data from a specified source into Oracle, with optional filtering by schema or table and configurable scheduling options.
SUSPEND_IMPORT_TASK Procedure	Temporarily pauses a running import task so it can be resumed later.
RESUME_IMPORT_TASK Procedure	Restarts a previously suspended import task and continues processing from the point it was paused.
DROP_IMPORT_TASK Procedure	Deletes an import task and removes its associated task definition and metadata.

CREATE_IMPORT_TASK Procedure

The DBMS_CLOUD_IMPORT.CREATE_IMPORT_TASK procedure creates and configures an import job to import data from a specified source system into an Autonomous AI Database.

You must be logged in as the ADMIN user to run this procedure.

Syntax

DBMS_CLOUD_IMPORT.CREATE_IMPORT_TASK(
    task_name          IN VARCHAR2,
    hostname           IN VARCHAR2,
    port               IN VARCHAR2,
    service_name       IN VARCHAR2,
    credential_name    IN VARCHAR2,
    directory_name     IN VARCHAR2,
    ssl_server_cert_dn IN VARCHAR2 DEFAULT NULL,
    gateway_params     IN VARCHAR2 DEFAULT NULL,
    schema_list        IN CLOB     DEFAULT NULL,
    table_list         IN CLOB     DEFAULT NULL
);

Parameters

Parameter	Description
`task_name`	Uniquely identifies the import task. This is the name assigned to the migration task. It is used to identify the task and its associated scheduler job.
`hostname`	The hostname for the target database. Specifying `localhost` for `hostname` is not allowed.
`port`	Specifies the port for the connections to the target database. When you specify a connection with Oracle-managed heterogeneous connectivity using the `gateway_params` parameter, set the port based on the `db_type` value. See Oracle-Managed Heterogeneous Connectivity Database Types and Ports for more information.
`service_name`	The `service_name` for the database to link to. For a target Autonomous AI Database, find the service name in the `tnsnames.ora` file in the wallet, in the connection strings on the Oracle Cloud Infrastructure Console, or by querying the `V$SERVICES` view. See View TNS Names and Connection Strings for an Autonomous AI Database Instance for more information. When you specify a connection with Oracle-managed heterogeneous connectivity using the `gateway_params` parameter, the `service_name` is the database name of the non-Oracle database.
`credential_name`	The name of a stored credential created with `DBMS_CLOUD.CREATE_CREDENTIAL`. This is the credential to access the source database.
`directory_name`	The directory for the `cwallet.sso` file. The default value for this parameter is `data_pump_dir`. Oracle-managed heterogeneous connectivity is preconfigured with a wallet that contains most of the common trusted root and intermediate SSL certificates. The `directory_name` parameter is not required when you supply the `gateway_params` parameter.
`ssl_server_cert_dn`	The DN value found in the server certificate. Oracle-managed heterogeneous connectivity is preconfigured with a wallet that contains most of the common trusted root and intermediate SSL certificates. The `ssl_server_cert_dn` must be `NULL` when you supply the `gateway_params` parameter or do not include the `ssl_server_cert_dn` parameter. The default value is `NULL`.
`gateway_params`	Specifies connection and source-type settings used for heterogeneous migrations, when the source is a non-Oracle database. It is passed as a JSON object and includes values such as the source database type. For example: `gateway_params => JSON_OBJECT('db_type' VALUE 'mysql')`. These parameters tell the migration job which gateway or driver behavior to use when connecting to and importing data from the source system. This parameter is optional and defaults to `NULL`.
`schema_list`	List of schemas to include in the import. This parameter is optional. If you do not specify a value, the default is `NULL`, all schemas in the source database are migrated.
`table_list`	Specifies the tables to migrate as a JSON array of `[{"schema_name":"public","table_name":"sales_data"}]` entries. Use `table_list` to perform a table-level partial migration, only the specified tables are migrated. Do not specify overlapping objects in `schema_list` and `table_list`. This parameter is only supported when the source database is Oracle. This parameter is optional and defaults to `NULL`.

Example

BEGIN
  DBMS_CLOUD_IMPORT.CREATE_IMPORT_TASK(
    task_name          => 'orcl_import_job',
    hostname           => '<remote_db_hostname>',
    port               => '<remote_db_port>',
    service_name       => '<remote_db_service_name>',
    ssl_server_cert_dn => '<cn=remote_db_cn_name>',
    directory_name     => '<walletdb>',
    credential_name    => 'db1_cred',
    schema_list        => '["admin"]'
  );
END;
/

SUSPEND_IMPORT_TASK Procedure

The DBMS_CLOUD_IMPORT.SUSPEND_IMPORT_TASK procedure temporarily pauses a running import task, allowing it to be resumed later.

Syntax

DBMS_CLOUD_IMPORT.SUSPEND_IMPORT_TASK(
   task_name IN VARCHAR2
);

Parameters

Parameter	Description
`task_name`	Uniquely identifies the import task.

Example

BEGIN
  DBMS_CLOUD_IMPORT.SUSPEND_IMPORT_TASK(
    task_name => 'SALES_IMPORT_JOB1'
  );
END;
/

RESUME_IMPORT_TASK Procedure

The DBMS_CLOUD_IMPORT.RESUME_IMPORT_TASK procedure resumes an import task that was previously suspended. It restarts task execution for the specified task_name and continues the import operation from the point it was paused.

You must be logged in as the ADMIN user to run this procedure.

Syntax

DBMS_CLOUD_IMPORT.RESUME_IMPORT_TASK(
   task_name IN VARCHAR2
);

Parameters

Parameter	Description
`task_name`	Uniquely identifies the import task.

Example

BEGIN
  DBMS_CLOUD_IMPORT.RESUME_IMPORT_TASK(
    task_name => 'SALES_IMPORT_JOB1'
  );
END;
/

DROP_IMPORT_TASK Procedure

The DBMS_CLOUD_IMPORT.DROP_IMPORT_TASK procedure deletes an import task and removes its associated task definition and metadata.

You must be logged in as the ADMIN user to run this procedure.

Syntax

DBMS_CLOUD_IMPORT.DROP_IMPORT_TASK(
   task_name IN VARCHAR2
);

Parameters

Parameter	Description
`task_name`	Uniquely identifies the import task.

Example

BEGIN
  DBMS_CLOUD_IMPORT.DROP_IMPORT_TASK(
    task_name => 'SALES_IMPORT_JOB1'
  );
END;
/