← Every conversion

Convert a SQL Server schema to Oracle

Paste the CREATE TABLE statements from SQL Server and ERD Designer draws them as a diagram. Change the diagram's target database to Oracle and every column's datatype is converted. You review each change before it is applied, then generate the Oracle script.

SQL Server DDL script

Source database

Diagram name (optional)

You sign up free and land on your diagram. Your script is held for 15 minutes and deleted from our server the moment you open it.

What changes in the sample

Converted to Oracle 23ai, these columns of the sample above change type. Everything else keeps its type; names, keys, nullability and lists of allowed values are left as they are.

Column	SQL Server	Oracle	Note
`Customers.CustomerId`	`INT`	`NUMBER`	numbered by the database
`Customers.Email`	`NVARCHAR(255)`	`VARCHAR2(255)`
`Customers.FullName`	`NVARCHAR(120)`	`VARCHAR2(120)`
`Customers.Notes`	`NVARCHAR(MAX)`	`CLOB`
`Customers.IsActive`	`BIT`	`BOOLEAN`	default: 1 → TRUE
`Customers.CreatedAt`	`DATETIME2(7)`	`TIMESTAMP(7)`	default: SYSUTCDATETIME() → CURRENT_TIMESTAMP
`Orders.OrderId`	`BIGINT`	`NUMBER`	numbered by the database
`Orders.CustomerId`	`INT`	`NUMBER`
`Orders.PublicId`	`UNIQUEIDENTIFIER`	`RAW(16)`	default: NEWID() → SYS_GUID()
`Orders.PlacedAt`	`DATETIMEOFFSET(3)`	`TIMESTAMP(3) WITH TIME ZONE`
`Orders.ReceiptPdf`	`VARBINARY(MAX)`	`BLOB`

What you are asked

Where Oracle offers more than one fair answer, ERD Designer asks before it converts — once per kind of column, not once per column. For this page each question was answered with its first option:

Auto-numbered keys — answered NUMBER identity

NUMBER identity — generated as identity — Oracle numbers new rows itself (12c and later)
NUMBER — a plain number — new rows must be numbered by a sequence or trigger you manage
INTEGER — the ANSI spelling (an alias of NUMBER(38)); same numbering caveat

Long text — answered CLOB

CLOB — unbounded like the source, but cannot be indexed or compared directly
VARCHAR2(4000) — indexable and comparable; text longer than 4000 bytes will not fit

True / false columns — answered BOOLEAN

BOOLEAN — native on Oracle 23ai and later
NUMBER(1) — 0/1 — the pre-23ai convention, held to those two values by a CHECK
CHAR(1) Y/N — 'Y'/'N', held to those two values by a CHECK
CHAR(1) T/F — 'T'/'F', held to those two values by a CHECK
VARCHAR2(3) — 'Yes'/'No', held to those two values by a CHECK
VARCHAR2(5) — 'True'/'False', held to those two values by a CHECK

UUID columns — answered RAW(16)

RAW(16) — Oracle's own form of a UUID — 16 bytes; a UUID-generating default becomes SYS_GUID(). Existing values carry over as bytes
NUMBER identity — a numeric key generated as identity (12c and later) — existing UUID values do not carry over
NUMBER — a plain numeric key — numbering and existing values are both up to you
INTEGER — the Long Integer form (ANSI, an alias of NUMBER(38)) — same caveats

What it tells you

Where a conversion loses something, ERD Designer says so rather than picking for you. These are the messages for the sample:

Customers.CreatedAt: default SYSUTCDATETIME() is the time in UTC; it became CURRENT_TIMESTAMP, which is the session's local time — the same only where the database runs in UTC.
Customers.Email: Oracle stores an empty string as NULL, so this required column refuses an empty value. Store a placeholder, or allow NULL and treat it as empty.
Customers.FullName: Oracle stores an empty string as NULL, so this required column refuses an empty value. Store a placeholder, or allow NULL and treat it as empty.
Oracle stores an empty string as NULL — an empty value in Customers.Notes reads back as NULL.

The Oracle script

This is what ERD Designer generates for the sample, for Oracle 23ai. Our test suite applies this conversion to a real Oracle server and then reads the database back to confirm it matches the diagram exactly.

-- Create table Customers
CREATE TABLE "Customers" (
  "CustomerId" NUMBER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  "Email" VARCHAR2(255 CHAR) NOT NULL,
  "FullName" VARCHAR2(120 CHAR) NOT NULL,
  "Notes" CLOB,
  "IsActive" BOOLEAN DEFAULT 'true' NOT NULL,
  "CreatedAt" TIMESTAMP(7) DEFAULT CURRENT_TIMESTAMP NOT NULL,
  CONSTRAINT "Customers_PK" PRIMARY KEY ("CustomerId")
);

-- Create table Orders
CREATE TABLE "Orders" (
  "OrderId" NUMBER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  "CustomerId" NUMBER NOT NULL,
  "PublicId" RAW(16) DEFAULT sys_guid() NOT NULL,
  "Total" NUMBER(12,2) NOT NULL,
  "PlacedAt" TIMESTAMP(3) WITH TIME ZONE NOT NULL,
  "ReceiptPdf" BLOB,
  CONSTRAINT "Orders_PK" PRIMARY KEY ("OrderId")
);

-- Add unique constraint on Customers (Email)
ALTER TABLE "Customers" ADD CONSTRAINT "Customers_UQ1" UNIQUE ("Email");

-- Create index on Orders (PlacedAt)
CREATE INDEX "IX_Orders_PlacedAt" ON "Orders" ("PlacedAt");

-- Add foreign key Orders (CustomerId) → Customers (CustomerId)
ALTER TABLE "Orders" ADD CONSTRAINT "Orders_FK1" FOREIGN KEY ("CustomerId") REFERENCES "Customers" ("CustomerId");

How to convert your own schema

Paste your SQL Server script above and press Open in ERD Designer. The diagram opens laid out and ready to edit.
Click the diagram's name in the toolbar to open its properties, choose Oracle under Target Database / Version, and press Apply Changes.
If Oracle offers more than one way to store a kind of column, choose one and press Continue.
Read the list of retyped columns and any warnings, then press Apply Conversion. Save the diagram to keep it.
Press Generate DDL (the database icon) to copy or download the Oracle script — or apply it to a database on your own network with the ERD Designer Agent.

What it reads, and what it does not

It reads CREATE TABLE, ALTER TABLE … ADD CONSTRAINT and CREATE INDEX: tables, columns, keys, foreign keys, unique constraints, check lists and indexes. Anything else in the script is skipped and listed.
It converts the schema, never the data: no rows are read, moved or changed.
It does not convert views, stored procedures, functions, triggers or packages.

Which plan you need

Pasting a script and working with it as a diagram is free on the Basic plan, with no card. Converting a diagram to another database is part of the Pro and Advanced plans, which also generate its script and can apply it through the Agent.

Other conversions

Questions? Write to support@erddesigner.com. · ERD Designer · Terms · Privacy