Lesson 1 of 5

Syntax & Data Types

The good news first: both SQL Server and Oracle speak SQL, and 80% of a query looks identical in either. The friction shows up at the edges — how strict each engine is about semicolons, the exact names of data types, and how each engine treats NULL and empty strings. Get those out of the way first and the rest of the transition is mostly vocabulary.

💡 The Bottom Line

SQL Server's T-SQL is fairly relaxed about semicolons and often doesn't need them at all; Oracle's PL/SQL is strict — every statement inside a block ends with one, and a trailing / on its own line tells SQL*Plus/SQL Developer to execute the block. SQL Server's VARCHAR/DECIMAL/DATETIME2 map to Oracle's VARCHAR2/NUMBER/DATE — similar jobs, different names, and some sharper edge cases than you'd expect. And the two engines disagree, sharply, on what an empty string is.

Statement terminators

SQL Server T-SQL (what you know)

SELECT last_name
FROM employees
WHERE department_id = 10;

PRINT 'Done';
GO

Oracle PL/SQL (the new equivalent)

SELECT last_name
FROM employees
WHERE department_id = 10;

BEGIN
  DBMS_OUTPUT.PUT_LINE('Done');
END;
/

📋 Key differences

SQL Server uses GO as a batch separator for tools like SSMS — it's not part of T-SQL itself, and semicolons are statement terminators that are often optional (though required before some statements like WITH common table expressions).
Oracle requires a semicolon after every PL/SQL statement inside a block; a trailing / on its own line tells SQL*Plus/SQL Developer to execute the preceding block — forget it and your session just sits there waiting for more input.
PRINT becomes DBMS_OUTPUT.PUT_LINE for simple debug output — and note that in most Oracle clients you also need SET SERVEROUTPUT ON once per session before you'll actually see the output.

Data types: same job, different names

SQL Server (what you know)

CREATE TABLE employees (
  employee_id   INT,
  first_name    VARCHAR(50),
  hire_date     DATETIME2,
  salary        DECIMAL(10,2)
);

Oracle (the new equivalent)

CREATE TABLE employees (
  employee_id   NUMBER(10),
  first_name    VARCHAR2(50),
  hire_date     DATE,
  salary        NUMBER(10,2)
);

📋 Key differences

VARCHAR(n) → VARCHAR2(n). Oracle has a plain VARCHAR too, but it's a reserved (and effectively deprecated) type left over for potential future ANSI-compliance changes — Oracle's own documentation tells you to always use VARCHAR2, never VARCHAR, in new code.
INT/DECIMAL(p,s) → NUMBER(p,s). Oracle has a single numeric type that covers everything from a bit flag to a currency amount — there's no separate integer type. For a whole-number column you'll typically see NUMBER(10) or just NUMBER with no precision/scale at all (unbounded), rather than a dedicated INT.
SQL Server's true date-only DATE has no Oracle equivalent by that name — Oracle's DATE always stores a time component down to the second, even if you never set one (it defaults to midnight). If you need SQL Server's DATETIME2-style precision beyond seconds, Oracle's TIMESTAMP is the closer match.
For Unicode text, SQL Server's NVARCHAR maps to Oracle's NVARCHAR2.

String concatenation

SQL Server (what you know)

SELECT first_name + ' ' + last_name AS full_name
FROM employees;
-- or: CONCAT(first_name, ' ', last_name)

Oracle (the new equivalent)

SELECT first_name || ' ' || last_name AS full_name
FROM employees;

SQL Server overloads the plain + operator for strings. Oracle instead has a dedicated concatenation operator, ||, that never gets confused with numeric addition — and, as the gotcha below shows, it behaves quite differently around NULL than you're used to.

⚠️ Gotcha

In SQL Server, 'Hello' + NULL evaluates to NULL — the entire expression collapses to NULL the instant one operand is NULL. In Oracle, 'Hello' || NULL evaluates to 'Hello' — Oracle's || treats NULL as an empty string for concatenation purposes. If you're porting a SQL Server report that concatenates optional (nullable) columns and relies on the whole thing going blank when one piece is missing, that safety net is gone in Oracle: a missing middle name will simply vanish from the string with no trace, rather than nulling out the entire display name. Wrap nullable columns in NVL(col, '') if you need to reason about it explicitly.

NULL handling functions

SQL Server (what you know)

SELECT ISNULL(commission_pct, 0) AS commission
FROM employees;

SELECT IIF(commission_pct IS NOT NULL, 'Yes', 'No')
FROM employees;

Oracle (the new equivalent)

SELECT NVL(commission_pct, 0) AS commission
FROM employees;

SELECT NVL2(commission_pct, 'Yes', 'No')
FROM employees;

📋 Key differences

ISNULL(expr, replacement) maps most directly to NVL(expr, replacement) — both return the replacement only when the first argument is NULL.
The ANSI-standard COALESCE(a, b, c, ...) works identically in both SQL Server and Oracle (returns the first non-null argument) — prefer it when you want code that's portable across engines, since ISNULL is T-SQL-only and NVL is Oracle-only.
T-SQL's IIF(condition, true_val, false_val) has no direct one-function equivalent in Oracle before very recent versions — use Oracle's three-way NVL2(expr, if-not-null, if-null) when the condition is simply "is this NULL," or a CASE expression for anything more general.

🔧 Try It Yourself

Take a CREATE TABLE statement from a SQL Server schema you know and rewrite it for Oracle: swap VARCHAR for VARCHAR2, decide whether DATE's built-in time component is fine or whether you actually need TIMESTAMP, and replace any INT primary key with NUMBER. Then rewrite one query that uses + and ISNULL, converting both to their PL/SQL equivalents.

🙋 There Are No Dumb Questions

Q: Can I just use COALESCE everywhere instead of learning NVL?
A: Yes, and it's arguably the better habit — COALESCE is ANSI SQL and works in both Oracle and SQL Server (and Postgres, MySQL, etc.), while NVL only exists in Oracle and ISNULL only exists in T-SQL. The one wrinkle: COALESCE's result type is determined by data type precedence across all its arguments, which can occasionally surprise you, whereas NVL's result type always matches its first argument. For simple two-argument cases the difference rarely matters, and NVL is still worth knowing since it's everywhere in existing Oracle code you'll be reading.

✅ Quick Check

You're porting a SQL Server report that builds a display name with first_name + ' ' + middle_name + ' ' + last_name, where middle_name is often NULL. You rewrite it in PL/SQL using ||. What happens for rows where middle_name is NULL?

Next up: once your tables and types are in place, the next friction point is how you query them — the mysterious DUAL table, pagination without OFFSET...FETCH, and a handful of renamed string functions.

← Back to