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

SET SERVEROUTPUT ON
   declare output_sql_result CLOB;
BEGIN
  DBMS_CLOUD_MIGRATION.MIGRATE_SQL(      
    original_sql => 'CREATE TABLE [dbo].[movie] ([film_id] [int], [title] [varchar](20));',
    output_sql   => output_sql_result,
    source_db    => 'SQLSERVER');
    DBMS_OUTPUT.PUT_LINE (output_sql_result);
END;        
/

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

SELECT DBMS_CLOUD_MIGRATION.MIGRATE_SQL(
     'CREATE TABLE [dbo].[movie] ([film_id] [int], [title] [varchar](20));','SQLSERVER') AS OUTPUT 
     FROM DUAL;

OUTPUT
------------------------------------------------------------------------------
CREATE TABLE movie (film_id NUMBER(10), title VARCHAR2(20));

You can interactively translate and run SQL Server statements in your Autonomous Database.

1. Connect to your Autonomous Database using a SQL client.

   See Connect to Autonomous 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.
2. Run DBMS_CLOUD_MIGRATION.ENABLE_TRANSLATION to enable real-time SQL translation in your session. Use the SQLSERVER parameter to translate from Microsoft SQL Server.

   BEGIN
    DBMS_CLOUD_MIGRATION.ENABLE_TRANSLATION('SQLSERVER');
   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.

3. Enter SQL Server SQL statements. For example:

   CREATE TABLE [dbo].[movie] ([film_id] [int], [title] [varchar](20));
   
   Table [dbo].[movie] created.

   This automatically translates and runs the SQL Server 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.
4. 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
   

5. Create an INVENTORY table:

   CREATE TABLE [dbo].[inventory] ([film_id] [int], [title] [inventory_id](20));
   
   
   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.
6. 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

7. 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 query is first translated to Oracle SQL and then run in your session.

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

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

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

1. Connect to your Autonomous Database instance.

   See Connect to Autonomous Database for more information.

2. 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 and Use 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.

3. 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
   ---------------------
   mssqltest.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.

4. Run DBMS_CLOUD_MIGRATION.MIGRATE_FILE to migrate the SQL Server 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/mssqltest.sql',
        source_db       => 'SQLSERVER'
       );
   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 SQL Server as the database language. Use the value SQLSERVER to translate SQL Server files to Oracle SQL.

   Running this command translates the SQL Server file mssqltest.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 mssqltest.sql generates mssqltest_oracle.sql. After the translation step the procedure uploads mssqltest_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.

5. 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
   ---------------------
   mssqltest.sql
   mssqltest_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.

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
--------------------------------------------------------------------------------
DROP TABLE movie;
CREATE TABLE movie (film_id NUMBER(10), title VARCHAR2(20));
 
INSERT INTO movie (film_id, title) VALUES (123, 'Tangled');
INSERT INTO movie (film_id, title) VALUES (234, 'Frozen');
 
CREATE TABLE inventory(film_id NUMBER(10), inventory_id NUMBER(10));
INSERT INTO inventory(film_id, inventory_id) VALUES (123, 223);
INSERT INTO inventory(film_id, inventory_id) VALUES (234, 334);
 
SELECT * FROM movie;
SELECT * FROM inventory;
SELECT m.film_id, m.title, inventory_id FROM movie m LEFT JOIN inventory ON inventory.film_id = m.film_id;

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