How to Migrate from SQLServer to Oracle in PostgreSQL

Why How to Migrate from SQLServer to Oracle in PostgreSQL is important

-- Migrate Orders and related OrderItems
CREATE SEQUENCE seq_orders START WITH 2000;
CREATE TABLE Orders (
  id NUMBER(10) PRIMARY KEY,
  customer_id NUMBER(10) REFERENCES Customers(id),
  order_date  DATE,
  total_amount NUMBER(12,2)
);

INSERT /*+APPEND*/ INTO Orders(id, customer_id, order_date, total_amount)
SELECT id, customer_id, order_date, total_amount FROM dbo.Orders@sqlsrv_link;

CREATE SEQUENCE seq_order_items START WITH 5000;
CREATE TABLE OrderItems (
  id NUMBER(10) PRIMARY KEY,
  order_id  NUMBER(10) REFERENCES Orders(id),
  product_id NUMBER(10),
  quantity  NUMBER(5)
);

INSERT /*+APPEND*/ INTO OrderItems(id, order_id, product_id, quantity)
SELECT id, order_id, product_id, quantity FROM dbo.OrderItems@sqlsrv_link;

-- 1. Create Oracle sequences to replace IDENTITY
CREATE SEQUENCE seq_customers START WITH 1000;

-- 2. Re-create table with Oracle types
CREATE TABLE Customers (
  id         NUMBER(10) PRIMARY KEY,
  name       VARCHAR2(255),
  email      VARCHAR2(320) UNIQUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 3. Auto-populate PK
CREATE OR REPLACE TRIGGER trg_customers_pk
BEFORE INSERT ON Customers
FOR EACH ROW
WHEN (NEW.id IS NULL)
BEGIN
  SELECT seq_customers.NEXTVAL INTO :NEW.id FROM dual;
END;
/

-- 4. Database link via heterogeneous gateway
CREATE DATABASE LINK sqlsrv_link CONNECT TO "sa" IDENTIFIED BY "pwd"
USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sqlsrv)(PORT=1433))(CONNECT_DATA=(SID=SQLSVR)))';

-- 5. One-time bulk load
INSERT /*+APPEND*/ INTO Customers(id, name, email, created_at)
SELECT id, name, email, created_at FROM dbo.Customers@sqlsrv_link;

-- 6. Validate counts
SELECT COUNT(*) FROM Customers;