Oracle Cloud Infrastructure Documentation

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.

Parent topic: Autonomous AI Database Supplied Package Reference

Summary of DBMS_CLOUD_IMPORT Subprograms

This table summarizes the subprograms included DBMS_CLOUD_IMPORT in the package.

Subprogram Description

ADD_BY_LIKE 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.

ADD_FILE Procedure

Temporarily pauses a running import task so it can be resumed later.

ADD_LATEST_FILES Procedure

Restarts a previously suspended import task and continues processing from the point it was paused.

ADD_TABLE 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.
  • SUSPEND_IMPORT_TASK Procedure
    The DBMS_CLOUD_IMPORT.SUSPEND_IMPORT_TASK procedure temporarily pauses a running import task, allowing it to be resumed later.
  • 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.
  • DROP_IMPORT_TASK Procedure
    The DBMS_CLOUD_IMPORT.DROP_IMPORT_TASK procedure deletes an import task and removes its associated task definition and metadata.

Parent topic: DBMS_CLOUD_IMPORT Package

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 by one of the following methods:

  • Look in the tnsnames.ora file in the wallet.zip that you download from an Autonomous AI Database for your connection.

  • Click Database connection on the Oracle Cloud Infrastructure Console. In the Connection Strings area, each connection string includes a service_name entry with the connection string for the corresponding service. When both Mutual TLS (mTLS) and TLS connections are allowed, under TLS authentication select TLS to view the TNS names and connection strings for connections with TLS authentication. See View TNS Names and Connection Strings for an Autonomous AI Database Instance for more information.

  • Query V$SERVICES view. For example: 

    SELECT name FROM V$SERVICES;

    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 credentials 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 (that is, 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;
/