Migrate Applications from PostgreSQL Databases to Autonomous AI Database

You can migrate SQL statements from PostgreSQL to Oracle SQL and run the statements on Autonomous AI Database.

Translate PostgreSQL Statements to Oracle SQL

You can translate SQL statements written in PostgreSQL to Oracle SQL and run the translated statements on Autonomous AI Database.

Use DBMS_CLOUD_MIGRATION.MIGRATE_SQL to translate the PostgreSQL statement to Oracle SQL. There are procedure and function variants of DBMS_CLOUD_MIGRATION.MIGRATE_SQL.

Migrate PostgreSQL statement to Oracle SQL with MIGRATE_SQL Procedure

The following example accepts the SQL statement written in PostgreSQL as input, translates the statement to Oracle SQL, assigns the translated SQL statement to output_sql_result, and prints the result:

SET SERVEROUTPUT ON
   declare output_sql_result CLOB;
BEGIN
  DBMS_CLOUD_MIGRATION.MIGRATE_SQL(
    original_sql => 'SELECT e.employee_id, e.last_name, e.salary FROM employees AS e;',
    output_sql   => output_sql_result,
    source_db    => 'POSTGRES');
    DBMS_OUTPUT.PUT_LINE (output_sql_result);
END;
/

Output
--------------------------------------------------------------
SELECT e.employee_id, e.last_name, e.salary FROM employees e;

The original_sql parameter specifies the PostgreSQL statement.

The output_sql parameter stores the translated SQL.

The source_db parameter specifies the PostgreSQL database name.

See MIGRATE_SQL Procedure and Function for more information.

Migrate PostgreSQL statement to Oracle SQL with MIGRATE_SQL Function

The following example shows the DBMS_CLOUD_MIGRATION.MIGRATE_SQL function within a SELECT statement. The function input is a PostgreSQL statement and the function returns the translated statement in 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);

See MIGRATE_SQL Procedure and Function for more information.

Notes for running DBMS_CLOUD_MIGRATION.MIGRATE_SQL:

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 PostgreSQL Statements 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.

Run PostgreSQL Statements in Autonomous AI Database

You can interactively translate and run PostgreSQL statements in your Autonomous AI Database.

Use the ENABLE_TRANSLATION procedure to enable real-time translation of SQL statements written in PostgreSQL. After you enable translation in a session, PostgreSQL statements are automatically translated and run as Oracle SQL statements, and you can see the results.

For example, after you enable translation by running ENABLE_TRANSLATION you can interactively do the following in a session:

Create the tables. For example, create the tables MOVIE and INVENTORY.
Insert data into tables.
Query tables.
Perform JOIN operations on tables. For example, you can perform a left outer join on tables.

To enable translation with PostgreSQL and run commands:

Connect to your Autonomous AI Database using a SQL client.

See Connect to Autonomous AI Database for more information.

Note: DBMS_CLOUD_MIGRATION.ENABLE_TRANSLATION is not supported in Database Actions and is not supported with the Oracle APEX Service.
Run DBMS_CLOUD_MIGRATION.ENABLE_TRANSLATION to enable real-time SQL translation in your session.
```
BEGIN
 DBMS_CLOUD_MIGRATION.ENABLE_TRANSLATION('POSTGRES');
END;
/
```
Use the following query to verify the SQL translation language for your session:
```
SELECT SYS_CONTEXT('USERENV','SQL_TRANSLATION_PROFILE_NAME') FROM DUAL;
```
See ENABLE_TRANSLATION Procedure for more information.
Enter PostgreSQL statements. For example:
```
CREATE TABLE movie (film_id int, title varchar(255));

Table MOVIE created.
```
This automatically translates and runs the PostgreSQL CREATE TABLE statement.

You can verify using the DESC command. For example:
```
DESC movie;

Name        Null?     Type

-------     -----     -------------
FILM_ID               NUMBER(38)

TITLE                 VARCHAR2(255)
```
The MOVIE table is created and the datatypes of each of the columns are automatically converted to Oracle datatypes.

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

Insert data into the MOVIE table. For example:

INSERT INTO movie (film_id, title) VALUES (123, 'Tangled');

1 row inserted.

INSERT INTO movie (film_id, title) VALUES (234, 'Frozen');

1 row inserted.

Verify the data insertion by querying the MOVIE table. For example:

SELECT * FROM movie;

FILM_ID  TITLE
-------- ---------
123  Tangled
234  Frozen

Create an INVENTORY table:

CREATE TABLE inventory (film_id int, inventory_id int);

Table INVENTORY created.

You can verify this step with the DESC command. For example:

DESC inventory;

Name        Null?     Type

-------     -----     -------------
FILM_ID               NUMBER(38)

INVENTORY_ID          NUMBER(38)

The INVENTORY table is created and the datatypes of each of the columns are automatically converted to Oracle datatypes.

Insert data into the INVENTORY table. For example:

INSERT INTO inventory(film_id, inventory_id) VALUES (123, 223);

1 row inserted.

INSERT INTO inventory(film_id, inventory_id) VALUES (234, 334);

1 row inserted.

Verify the data insertion by querying INVENTORY. For example:

SELECT * FROM inventory;

FILM_ID  INVENTORY_ID
-------- -------------
123  223
234  334

Perform a left outer join on tables MOVIE and INVENTORY:
```
SELECT m.film_id, m.title, inventory.inventory_id
    FROM movie AS m LEFT JOIN inventory
    ON inventory.film_id = m.film_id;

  FILM_ID  TITLE       INVENTORY_ID

---------- ---------- ------------
       234 Frozen      334
       123 Tangled     223
```
This example performs a LEFT OUTER JOIN on the movie and inventory tables. The AS keyword for table aliases in the FROM clause is not supported in Oracle SQL. The query is first translated to Oracle SQL and then run in your session.
Use DBMS_CLOUD_MIGRATION.DISABLE_TRANSLATION procedure to disable real-time SQL language translation for your session.
```
BEGIN
 DBMS_CLOUD_MIGRATION.DISABLE_TRANSLATION;
END;
/
```
This returns an error if SQL language translation is not enabled for your session.

See DISABLE_TRANSLATION Procedure

You can query the V$MAPPED_SQL view to list the PostgreSQL statements that are translated and mapped in memory to Oracle SQL statements.

For example:

SELECT v.*
    FROM v$mapped_sql v, dba_objects o
    WHERE v.sql_translation_profile_id = o.object_id
        AND o.object_name = 'POSTGRES'
        AND o.object_type = 'TRANSLATION PROFILE';

See V$MAPPED_SQL for more information.

Migrate PostgreSQL Files to Oracle SQL

You can migrate a file containing PostgreSQL statements to a file containing Oracle SQL statements.

The DBMS_CLOUD_MIGRATION.MIGRATE_FILE procedure translates SQL statements in a PostgreSQL file in Object Storage and generates a new file containing Oracle SQL.

As a prerequisite, upload one or more PostgreSQL files with a .sql extension to a location on Object Storage. The following examples use the file postgrestest.sql that is uploaded to Object Storage. See Put data into object storage for more information.

To migrate PostgreSQL files to Oracle SQL:

Connect to your Autonomous AI Database instance.

See Connect to Autonomous AI Database for more information.
Configure access to Cloud Object Storage using a resource principal or by creating a credential object.

This step provides access to the Cloud Object Storage where you put the files you are migrating:
- Use a resource principal to access Cloud Object Storage:
  
  Define policies and roles to access Oracle Cloud Infrastructure Object Storage with a resource principal. See Use Resource Principal to Access Oracle Cloud Infrastructure Resources andUse Resource Principal with DBMS_CLOUD for more information.
  
  If your files reside in another vendor's Cloud Object Storage, define the appropriate policies and roles to access the resource. See Configure Policies and Roles to Access Resources for more information.
- Create a credential to access Cloud Object Storage:
  
  You can create a credential to access Cloud Object Storage.
  
  For example:
```
BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL (
credential_name => 'OBJ_STORE_CRED',
username        => 'user1@example.com',
password        => 'password'
);
END;
/
```
  See CREATE_CREDENTIAL Procedure for more information.

Optionally, you can list the files in Object Storage. For example:

VAR function_list CLOB;
SELECT object_name FROM DBMS_CLOUD.LIST_OBJECTS
 (credential_name => 'OCI$RESOURCE_PRINCIPAL', location_uri => 'https://objectstorage.*region*.oraclecloud.com/n/*namespace*/b/*bucket*/o/files');

OBJECT_NAME

---------------------
postgrestest.sql

Alternatively, if you create a credential instead of the resource principal, OCI$RESOURCE_PRINCIPAL, specify the credential name in the credential_name parameter.

See LIST_OBJECTS Function for more information.

Run DBMS_CLOUD_MIGRATION.MIGRATE_FILE to migrate the PostgreSQL file to Oracle SQL:
```
BEGIN
DBMS_CLOUD_MIGRATION.MIGRATE_FILE (
   credential_name => 'OCI$RESOURCE_PRINCIPAL',
   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 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. If you use a credential instead of a resource principal, specify the credential name in the credential_name parameter.

The location_uri parameter specifies 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.

The source_db parameter specifies the PostgreSQL as database language. Use the value POSTGRES to translate PostgreSQL files to Oracle SQL.

Running this command translates the PostgreSQL file postgrestest.sql to Oracle SQL and generates a new file with the name original_filename_oracle.sql.

For this example, running DBMS_CLOUD_MIGRATION.MIGRATE_FILE with the input file postgrestest.sql generates postgrestest_oracle.sql. After the translation step the procedure uploads postgrestest_oracle.sql to Object Storage.

Optionally, use the target_uri parameter to specify the location where the translated file is uploaded. The default value for this parameter is NULL, which means the translated file is uploaded to the same location as specified in the location_uri parameter.

See MIGRATE_FILE Procedure for more information.

Verify that the output file was generated.

SELECT object_name FROM DBMS_CLOUD.LIST_OBJECTS (
  credential_name => 'OCI$RESOURCE_PRINCIPAL', location_uri => 'https://objectstorage.*region*.oraclecloud.com/n/*namespace*/b/*bucket*/o/files');

OBJECT_NAME

---------------------
postgrestest.sql
postgrestest_oracle.sql

Alternatively, if you create a credential instead of the resource principal, OCI$RESOURCE_PRINCIPAL, specify the credential name in the credential_name parameter.

See LIST_OBJECTS Function for more information.

Run the following query to view the content of the postgrestest_oracle.sql file:

SELECT UTL_RAW.CAST_TO_VARCHAR2 (DBMS_CLOUD.GET_OBJECT(
   credential_name => 'OCI$RESOURCE_PRINCIPAL',
   object_uri => 'https://objectstorage.region.oraclecloud.com/n/namespace/b/bucket/o/files'))
FROM dual;

UTL_RAW.CAST_TO_VARCHAR2(DBMS_CLOUD.GET_OBJECT(CREDENTIAL_NAME=>'CRED1',OBJECT_U
--------------------------------------------------------------------------------
SELECT f.film_id, f.title, inventory_id
FROM film f LEFT JOIN inventory
ON inventory.film_id = f.film_id;

Alternatively, if you create a credential instead of the resource principal, OCI$RESOURCE_PRINCIPAL, specify the credential name in the credential_name parameter.

See GET_OBJECT Procedure and Function for more information.

Limitations for Migration and Translation of PostgreSQL Statements to Oracle SQL

This section summarizes the limitations for migrating SQL statements from PostgreSQL to Oracle SQL.

The following list of PostgreSQL statements are not supported when migrating to Autonomous AI Database:

CREATE DOMAIN
CREATE EXTENSION
CREATE DATABASE
CREATE TYPE
SET

The following list of PostgreSQL statements are supported with restrictions:

ALTER TABLE: Only ALTER TABLE ADD CONSTRAINT is supported when migrating to Autonomous AI Database.
DELETE: The RETURNING * keyword in the DELETE statement is not supported in Autonomous AI Database. You must replace the RETURNING * clause with the RETURNING INTO clause. For example, DELETE FROM tasks WHERE status = 'DONE' RETURNING *;.

See RETURNING INTO Clause for more information.
CREATE FUNCTION: Following are not supported with CREATE FUNCTION:
- The SETOF return type, you must replace SETOF with CURSORS or COLLECTIONS return types.
- The IMMUTABLE clause.
- The parameter declarations in the format FUNCTION_NAME (DATATYPE, DATATYPE).
ALTER FUNCTION: The ALTER FUNCTION function arguments, for example, RENAME TO, OWNER TO, SET SCHEMA are not supported.