Migration guides
SQL Server to Oracle migration guide
Convert a SQL Server script from SSMS into a Oracle script with the same tables, keys and data: how to script, convert, load, and every rule applied.
IntoSQL reads the script SSMS writes with Generate Scripts and writes one Oracle script with the same tables, columns, keys, indexes, foreign keys, defaults, counters, comments and rows. It reads the script itself, without a database server, and lists in the report every value Oracle can't hold as it is and every object it doesn't carry. The script loads on Oracle 19c and later.
Convert in the browser: SQL Server to Oracle opens the converter set up for this pair.
Script for the best result
In SQL Server Management Studio, right-click the database, choose Tasks, Generate Scripts, pick the tables, and under Advanced set Types of data to script to Schema and data. Save it as Unicode (SSMS's default) or UTF-8, as .sql or .sql.gz. dbatools (Export-DbaScript), mssql-scripter and other tools built on SQL Server Management Objects write the same script, from SQL Server 2017 on.
- A script of the data alone still converts: the types are read from the values, and the report says the schema was missing (
no_schema). - Tables on a partition scheme arrive as one table with every partition's rows (
partitions_dropped), and a temporal table and its history table arrive as two plain tables. - SSMS doesn't script the values of computed and
rowversioncolumns; computed columns are written again, androwversioncolumns are reported (rowversion_empty).
Convert
In the browser, open SQL Server to Oracle or the converter on the home page, drop the script and choose Oracle. It reads the file from your disk in pieces, twice, and writes the new script as it goes, so the file is never uploaded and its size isn't limited by memory. The desktop app does the same with the file on disk.
With the command line:
intosql inspect shop.sql
intosql convert shop.sql --to oracle --output shop.oracle.sql --report shop.report.jsoninspect lists the tables, their rows and what won't be converted, without writing anything. convert writes the script and, with --report, the report as JSON.
Through the API, a script converts like any other file: open a conversion with its name ending in .sql or .sql.gz and "output": {"dialect": "oracle"}.
Load into Oracle
NLS_LANG=.AL32UTF8 sqlplus -S -L shop@//localhost/FREEPDB1 @shop.oracle.sqlNLS_LANG makes SQL*Plus read the script as UTF-8. The user needs CREATE TABLE, CREATE SEQUENCE, CREATE TRIGGER and CREATE PROCEDURE, and quota on its tablespace. The script stops at the first error, creates the tables, copies the rows in blocks of 100 with a commit after each, then adds keys, indexes, foreign keys, identities, sequences and comments. Schemas other than dbo become a prefix on their tables' names (app.orders is APP_ORDERS). datetime2 keeps every digit as TIMESTAMP, and datetimeoffset values keep their offsets as TIMESTAMP WITH TIME ZONE. Computed columns become virtual columns (stored_as_virtual).
Oracle stores an empty string as NULL, so values that were '' read back as NULL; the report counts them per column (empty_string_null). Plain names become uppercase (customers is CUSTOMERS), so queries can leave the quotes out.
What isn't converted
Views, functions, procedures, triggers, synonyms, rules and security policies are written in T-SQL. They are listed in the report by kind and name, never dropped silently, so you can recreate them in Oracle.
Data types
SQL Server to Oracle data types lists each type that changes in the script: what it becomes, why, and the report line.
Every other rule
Generated from the engine, where each rule is decided.
| In SQL Server | In Oracle | Why | Report line |
|---|---|---|---|
| collations that ignore case (CI) | exact comparison | Oracle compares text exactly unless told otherwise, so columns that ignored case in SQL Server now tell Ana and ana apart. | case_insensitive_collation |
| CREATE SEQUENCE | CREATE SEQUENCE with the same options | Oracle sequences work the same way. SSMS doesn't script where a sequence has reached, so it starts where it was created. | None |
| computed columns | virtual columns | Oracle computes generated columns on read only; PERSISTED columns become virtual with the same values. | stored_as_virtual |
| computed columns with functions Oracle lacks | a plain column | A formula that can't be written leaves a plain column and a report line; SSMS scripts no values for computed columns. | generated_not_translated |
| defaults with functions Oracle lacks, and NEXT VALUE FOR | no default | A default that can't be written is left out and reported. | default_not_translated |
| CHECK constraints | translated | The same checks, tested as each row loads. | None |
| CHECK constraints with functions Oracle lacks | not created | A check that can't be written is left out and reported. | check_skipped |
| CHECK constraints added WITH NOCHECK or disabled | not created | SQL Server doesn't apply these checks, so the rows may break them. | check_not_enforced |
| filtered and columnstore indexes, and indexes on CLOB columns | not created | IntoSQL writes b-tree indexes on whole columns; create the others again after loading. | index_skipped |
| foreign keys | after the data ENABLE NOVALIDATE, then validated with a warning on orphans | The key checks new rows at once and is validated last; rows without a parent leave it unvalidated with a warning instead of stopping the load. | None |
| ON UPDATE CASCADE, SET NULL or SET DEFAULT; ON DELETE SET DEFAULT | dropped | Oracle's foreign keys act on delete only, with CASCADE or SET NULL. | foreign_key_action_unsupported |
| names over 128 bytes | shortened | Oracle 12.2 and later names hold 128 bytes; SQL Server's hold 128 characters. | name_shortened |
| foreign keys to tables the dump doesn't create | not created | A key can't name a table that isn't there. | foreign_key_skipped |
| tables on a partition scheme | one table with every partition's rows | The rows are the same; partition the table again in Oracle if it needs it. | partitions_dropped |
| temporal tables (SYSTEM_VERSIONING) | two tables: the current rows and the history | The table and its history table are copied as plain tables; Oracle doesn't keep the history by itself. | system_versioning_dropped |
| schemas | one schema, tables named after their schema first | The script loads into the schema of the user who runs it, so tables of different schemas get their schema's name in front; tables in dbo keep their names. | schemas_merged |
| NOT NULL columns with values that become NULL | NOT NULL dropped | A column whose values had to become NULL can't keep NOT NULL, or the load would stop. | not_null_relaxed |
| views, functions, procedures, triggers, synonyms, types and partition functions | listed in the report | Their code is written in T-SQL, so they are listed for you to recreate. | not_converted_objects |
| scripts without CREATE TABLE (data only) | types read from the values | A data-only script has only rows, so the types come from the values. | no_schema |
| expressions in INSERT values | NULL | A converter that doesn't run a database can't evaluate them. | expression_value |
| text decoded twice (ñ) | copied as it is | The script holds exactly these characters, so they are copied; the report points them out. | mojibake_suspected |