Migrate Applications from Non-Oracle Databases to Autonomous Database
You can migrate SQL statements from a non-Oracle database to Oracle SQL and run the statements on Autonomous Database.
- Application Migration Overview
Application migration involves moving an application from one environment to another. The migration process includes a variety of tasks, including migrating SQL statements from a non-Oracle database to Oracle SQL. - Translate PostgreSQL Statements to Oracle SQL
You can translate SQL statements written in PostgreSQL to Oracle SQL and run the translated statements on Autonomous Database. - Migrate PostgreSQL Files to Oracle SQL
You can migrate a file containing PostgreSQL statements to a file containing Oracle SQL statements. - Run PostgreSQL Statements in Autonomous Database
You can interactively translate and run PostgreSQL statements in your Autonomous Database. - Limitations for Migration and Translation of Non-Oracle SQL to Oracle SQL
This section summarizes the limitations for migrating SQL statements from a non-Oracle database to Oracle SQL.
Parent topic: Migrate to Autonomous Database
Application Migration Overview
Application migration involves moving an application from one environment to another. The migration process includes a variety of tasks, including migrating SQL statements from a non-Oracle database to Oracle SQL.
The DBMS_CLOUD_MIGRATION
package facilitates the
translation of SQL statements written for a non-Oracle database to Oracle SQL. This
allows you to easily migrate applications to Autonomous Database, when the applications were developed for non-Oracle
databases that use a different SQL flavor.
The DBMS_CLOUD_MIGRATION
package supports migration of SQL
code from the following non-Oracle database:
-
PostgreSQL
The DBMS_CLOUD_MIGRATION
package provides routines that
allow you to:
-
Translate individual SQL statements from a non-Oracle database to Oracle SQL. See Translate PostgreSQL Statements to Oracle SQL for details.
-
Translate files containing SQL code from a non-Oracle database to Oracle SQL, and generate a new file containg the translated Oracle SQL. See Migrate PostgreSQL Files to Oracle SQL for more information.
-
Work interactively in a session, entering SQL statements from a non-Oracle system. In this case, the non-Oracle statements are translated and run interactively in your session. See Run PostgreSQL Statements in Autonomous Database for more information.
To run the subprograms in
DBMS_CLOUD_MIGRATION
you must be logged in
as the ADMIN
user or have EXECUTE
privilege on
DBMS_CLOUD_MIGRATION
.
Translate PostgreSQL Statements to Oracle SQL
You can translate SQL statements written in PostgreSQL to Oracle SQL and run the translated statements on Autonomous Database.
Use DBMS_CLOUD_MIGRATION.MIGRATE_SQL
to translate a non-Oracle SQL
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 non-Oracle SQL
statement.
The output_sql
parameter stores the translated SQL.
The source_db
parameter specifies the non-Oracle SQL 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 PosgresSQL 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 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.
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:
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.
Run PostgreSQL Statements in Autonomous Database
You can interactively translate and run PostgreSQL statements in your Autonomous Database.
Use the ENABLE_TRANSLATION
procedure to enable real-time
translation of SQL statements written in a specified non-Oracle database language (for example
PostgreSQL). After you enable translation in a session, non-Oracle SQL statements are
automatically translated and run as Oracle SQL statements, and you can see the results.
ENABLE_TRANSLATION
you can interactively do the following in a session:
-
Create the tables. For example, create the tables
MOVIE
andINVENTORY
. -
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:
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.
Limitations for Migration and Translation of Non-Oracle SQL to Oracle SQL
This section summarizes the limitations for migrating SQL statements from a non-Oracle database to Oracle SQL.
PostgreSQL Statement Migration and Translation Restrictions
CREATE DOMAIN
CREATE EXTENSION
CREATE DATABASE
CREATE TYPE
SET
ALTER TABLE
: OnlyALTER TABLE ADD CONSTRAINT
is supported when migrating to Autonomous Database.DELETE
: TheRETURNING *
keyword in theDELETE
statement is not supported in Autonomous Database. You must replace theRETURNING *
clause with theRETURNING INTO
clause. For example,DELETE FROM tasks WHERE status = 'DONE' RETURNING *;
.See RETURNING INTO Clause for more information.
CREATE FUNCTION
: Following are not supported withCREATE FUNCTION
:- The
SETOF
return type, you must replaceSETOF
withCURSORS
orCOLLECTIONS
return types. - The
IMMUTABLE
clause. - The parameter declarations in the format
FUNCTION_NAME (DATATYPE, DATATYPE)
.
- The
ALTER FUNCTION
: TheALTER FUNCTION
function arguments, for example,RENAME TO
,OWNER TO
,SET SCHEMA
are not supported.