Oracle Cloud Infrastructure Documentation

DBMS_CLOUD_MIGRATION Package

The DBMS_CLOUD_MIGRATION package facilitates the translation of the SQL code from PostgreSQL to Oracle SQL.

Parent topic: Autonomous Database Supplied Package Reference

Summary of DBMS_CLOUD_MIGRATION Subprograms

This table summarizes the subprograms included in the DBMS_CLOUD_MIGRATION package.

Subprogram Description

MIGRATE_SQL Procedure and Function

The MIGRATE_SQL facilitates the translation of SQL statements written in non-Oracle SQL to Oracle SQL.

MIGRATE_FILE Procedure

This procedure translates a file containing SQL statements from a specified source database as an input and translates the SQL statements to Oracle SQL.

ENABLE_TRANSLATION Procedure

This procedure enables real-time SQL translation and the running of non-Oracle SQL statements in Autonomous Database.

DISABLE_TRANSLATION Procedure

This procedure enables you to exit the SQL language translation.
  • MIGRATE_SQL Procedure and Function
    The MIGRATE_SQL facilitates the translation of SQL statements written in non-Oracle SQL to Oracle SQL. This procedure is overloaded and has function and procedure variants.
  • MIGRATE_FILE Procedure
    MIGRATE_FILE takes a file containing SQL statements from a specified source database as an input and translates the SQL statements to Oracle SQL.
  • ENABLE_TRANSLATION Procedure
    The ENABLE_TRANSLATION procedure enables real-time SQL translation and the running of non-Oracle SQL statements in your Autonomous Database.
  • DISABLE_TRANSLATION Procedure
    The DISABLE_TRANSLATION procedure enables you to exit the SQL language translation. An error is returned if the SQL language translation is not enabled for your session.

Parent topic: DBMS_CLOUD_MIGRATION Package

MIGRATE_SQL Procedure and Function

The MIGRATE_SQL facilitates the translation of SQL statements written in non-Oracle SQL to Oracle SQL. This procedure is overloaded and has function and procedure variants.

Syntax

DBMS_CLOUD_MIGRATION.MIGRATE_SQL (
   original_sql  IN  CLOB,
   output_sql    OUT CLOB,
   source_db     IN  VARCHAR2);

DBMS_CLOUD_MIGRATION.MIGRATE_SQL (
   original_sql  IN  CLOB,
   source_db     IN  VARCHAR2);
RETURN CLOB;

Parameters

Parameter Description

original_sql

Specifies the original SQL statement in the source database that needs to be translated into Oracle SQL.

This parameter is mandatory.

output_sql

Returns the translated SQL statement.

This parameter is mandatory.

source_db

Specifies the name of the source database. The valid value for this parameter is POSTGRES.

This parameter is mandatory.

Return Values

The function form of DBMS_CLOUD_MIGRATION.MIGRATE_SQL returns a CLOB that contains the translated Oracle SQL version of the ORIGINAL_SQL input.

Usage Notes

  • You may encounter an error during the translation if the input SQL statement is not supported in Oracle SQL. See Limitations for Migration and Translation of Non-Oracle SQL to Oracle SQL for more information.

  • The DBMS_CLOUD_MIGRATION.MIGRATE_SQL subprograms only accept one SQL statement as input. So, only a single SQL statement can be translated per call.

  • You must be logged in as the ADMIN user or have the EXECUTE privilege on the DBMS_CLOUD_MIGRATION package to run the DBMS_CLOUD_MIGRATION.MIGRATE_SQL procedure.

Examples

Procedure form of DBMS_CLOUD_MIGRATION.MIGRATE_SQL to translate the PostgreSQL statement to Oracle SQL: 

BEGIN
  DBMS_CLOUD_MIGRATION.MIGRATE_SQL(      
    original_sql => 'SELECT e.employee_id, e.last_name, e.salary FROM employees AS e;',
    output_sql   => output_variable,
    source_db    => 'POSTGRES');
END;        
/

Function form of DBMS_CLOUD_MIGRATION.MIGRATE_SQL to translate the PostgreSQL statement to Oracle SQL: 

SELECT DBMS_CLOUD_MIGRATION.MIGRATE_SQL
    ('CREATE TABLE IF NOT EXISTS cars (brand VARCHAR(255), model VARCHAR(255), year INT)', 'POSTGRES') AS output 
    FROM DUAL;

OUTPUT
------------------------------------------------------------------------------
create table cars (brand VARCHAR2(255), model VARCHAR2(255), year NUMBER(10);

MIGRATE_FILE Procedure

MIGRATE_FILE takes a file containing SQL statements from a specified source database as an input and translates the SQL statements to Oracle SQL.

Syntax

DBMS_CLOUD_MIGRATION.MIGRATE_FILE (
    credential_name  IN VARCHAR2,
    location_uri     IN VARCHAR2,
    target_uri       IN VARCHAR2 DEFAULT NULL,
    source_db        IN VARCHAR2,
    params           IN CLOB

Parameters

Parameter Description

credential_name

The name of the credential to access the Cloud Object Storage.

This parameter is mandatory.

Optionally, the credential you use to access your Cloud Object Store can be defined by configuring policies and roles. See Configure Policies and Roles to Access Resources for more information.

location_uri

The source file URI. The format of the URI depends on the Cloud Object Storage service you are using, for details see DBMS_CLOUD URI Formats.

This parameter is mandatory.

target_uri

The target file URI, where the translated file should be stored. If you do not provide a value for this parameter, the translated file is stored at the same location as the source file.

The format of the URI depends on the Cloud Object Storage service you are using, for details see DBMS_CLOUD URI Formats.

source_db

Specifies the source database language. The valid value for this parameter is POSTGRES.

This parameter is mandatory.
params

Specifies the additional parameters that can be passed in JSON format.

Usage Notes

  • To run DBMS_CLOUD_MIGRATION.MIGRATE_FILE you must be logged in as the ADMIN user or have the EXECUTE privilege on DBMS_CLOUD_MIGRATION.

  • The credential_name parameter specifies the credential to access the Cloud Object Storage URI. The user that runs DBMS_CLOUD_MIGRATION.MIGRATE_FILE.must have EXECUTE privilege for the credential object used to access the Object Storage URI. That is, the credential you specify with the credential_name parameter.

Example

BEGIN
 DBMS_CLOUD_MIGRATION.MIGRATE_FILE (
     credential_name => 'OBJ_STORE_CRED',
     location_uri    => 'https://objectstorage.region.oraclecloud.com/n/namespace/b/bucket/o/files/postgrestest.sql',
     source_db       => 'POSTGRES'
    );
END;
/

The credential_name parameter specifies the credential to access the Cloud Object Storage URI.

The location_uri is the the source file URI. The format of the URI depends on the Cloud Object Storage service you are using. See DBMS_CLOUD URI Formats for more information.

In this example, namespace-string is the Oracle Cloud Infrastructure object storage namespace and bucketname is the bucket name. See Understanding Object Storage Namespaces for more information.

ENABLE_TRANSLATION Procedure

The ENABLE_TRANSLATION procedure enables real-time SQL translation and the running of non-Oracle SQL statements in your Autonomous Database.

Syntax

Parameters

DBMS_CLOUD_MIGRATION.ENABLE_TRANSLATION (
   source_db  IN  VARCHAR2);
Parameter Description

source_db

Specifies the source database language. The valid value for this parameter is POSTGRES.

This parameter is mandatory.

Usage Note

  • To run DBMS_CLOUD_MIGRATION.ENABLE_TRANSLATION you must be logged in as the ADMIN user or have the EXECUTE privilege on DBMS_CLOUD_MIGRATION.

Example

BEGIN
  DBMS_CLOUD_MIGRATION.ENABLE_TRANSLATION (source_db => 'POSTGRES');
END;        
/

DISABLE_TRANSLATION Procedure

The DISABLE_TRANSLATION procedure enables you to exit the SQL language translation. An error is returned if the SQL language translation is not enabled for your session.

Syntax

DBMS_CLOUD_MIGRATION.DISABLE_TRANSLATION();

Usage Note

  • To run DBMS_CLOUD_MIGRATION.DISABLE_TRANSLATION you must be logged in as the ADMIN user or have the EXECUTE privilege on DBMS_CLOUD_MIGRATION.

Example

BEGIN
  DBMS_CLOUD_MIGRATION.DISABLE_TRANSLATION();
END;        
/