Lesson 5 of 5

Common Gotchas

The last mile of this transition isn't new syntax — it's the handful of behavioral differences that don't show up as an error message, just as wrong results. These are the ones that cost the most debugging time, because the query runs fine; it just doesn't mean what you think it means.

💡 The Bottom Line

SQL Server treats an empty string and NULL as genuinely different values; Oracle treats an empty string as NULL — a distinction with no equivalent setting, and no way to turn it off. SQL Server's default collation is usually case-insensitive; Oracle string comparisons are case-sensitive by default. SQL Server autocommits every statement unless you explicitly open a transaction; Oracle auto-starts a transaction and expects an explicit COMMIT. And a handful of everyday functions — starting with GETDATE() vs SYSDATE — just have different names.

Empty string vs NULL

SQL Server (what you know)

INSERT INTO customers (name, notes)
VALUES ('Ada', '');

SELECT * FROM customers
WHERE notes IS NULL;  -- does NOT match

Oracle (the new equivalent)

INSERT INTO customers (name, notes)
VALUES ('Ada', '');

SELECT * FROM customers
WHERE notes IS NULL;  -- matches the row above

⚠️ Gotcha

SQL Server treats an empty string as a real, distinct value with length zero — a WHERE notes IS NULL will not match it. Oracle silently converts an empty string literal ('') to NULL for VARCHAR2/CHAR columns; Oracle simply has no separate concept of "a zero-length string" at the storage level for these types. Code ported from SQL Server that relies on empty and NULL being distinct (e.g. counting empty-string rows separately from missing-value rows) will silently merge the two categories on Oracle — there is no session setting or column option that changes this, it's baked into how Oracle stores character data. Any query, report, or constraint that depends on telling '' apart from NULL needs to be rethought, not just ported.

Case sensitivity

SQL Server (default collation)

SELECT * FROM employees
WHERE last_name = 'smith';
-- matches 'Smith' too

Oracle (default)

SELECT * FROM employees
WHERE last_name = 'smith';
-- no match if stored as 'Smith'

📋 Key differences

SQL Server's behavior depends entirely on the column/database collation — most default installations use a case-insensitive collation (e.g. SQL_Latin1_General_CP1_CI_AS, where CI means case-insensitive), so 'smith' = 'Smith' is often true out of the box.
Oracle string comparisons are case-sensitive by default, full stop — unless you enable a case-insensitive session parameter (NLS_SORT/NLS_COMP) or wrap both sides in UPPER()/LOWER(), which is the far more common approach in practice.
This is configurable in both directions on both engines — the point isn't that one is "right," it's that you can't assume the SQL Server behavior carries over. Always check the target database/column's collation rather than assuming case sensitivity either way, and expect to add explicit UPPER()/LOWER() calls to Oracle `WHERE` clauses that worked fine unadorned on SQL Server.

Transactions and autocommit

SQL Server (what you know)

UPDATE employees SET salary = salary * 1.1
WHERE department_id = 10;
-- change IS already permanent

-- only needed if you opened one:
BEGIN TRAN; ... COMMIT;

Oracle (the new equivalent)

UPDATE employees SET salary = salary * 1.1
WHERE department_id = 10;
-- change is NOT permanent yet

COMMIT;
-- now it is

⚠️ Gotcha

SQL Server's default is autocommit: every individual statement is its own implicit transaction, committed the instant it succeeds. Oracle's client tools instead implicitly start a transaction with your first DML statement, and it stays open — uncommitted, and rollback-able — until you explicitly COMMIT or ROLLBACK (or disconnect, which triggers an automatic rollback in many clients). If you're used to SQL Server's autocommit and run a batch of Oracle UPDATE/ DELETE statements without a trailing COMMIT, none of it is durable yet — a crashed client, a killed session, or simply closing your SQL*Plus/SQL Developer window can quietly roll the whole batch back, and it will look like nothing happened at all rather than like a partial failure.

Date/time function names

SQL Server (what you know)

SELECT GETDATE();
SELECT DATEADD(MONTH, 3, GETDATE());
SELECT DATEDIFF(MONTH, d2, d1);
SELECT CAST(GETDATE() AS DATE);

Oracle (the new equivalent)

SELECT SYSDATE FROM DUAL;
SELECT ADD_MONTHS(SYSDATE, 3) FROM DUAL;
SELECT MONTHS_BETWEEN(d1, d2) FROM DUAL;
SELECT TRUNC(SYSDATE) FROM DUAL;

📋 Key differences

GETDATE() (function call, returns server date/time) → SYSDATE (no parentheses at all — one of the more common syntax slips going this direction). For UTC, SQL Server's SYSUTCDATETIME()/GETUTCDATE() maps to Oracle's SYSTIMESTAMP (converted to UTC as needed).
DATEADD(MONTH, n, date) → ADD_MONTHS(date, n) — note SQL Server's generic DATEADD(unit, number, date) handles days/months/years/hours all through one function by changing the first argument, where Oracle has more separate named functions (ADD_MONTHS, plus plain date + n for adding days).
DATEDIFF(MONTH, d2, d1) → MONTHS_BETWEEN(d1, d2) — mind the argument order swap (DATEDIFF: earlier date first as the "start"; MONTHS_BETWEEN: later date first).
Truncating a date to remove its time component: SQL Server's CAST(date AS DATE) → Oracle's TRUNC(date), since Oracle's DATE always carries a time component (see lesson 1) and has no separate date-only type to cast into — TRUNC is how you zero out the time portion in place.

🔧 Try It Yourself

Find a SQL Server query in your own codebase that filters on WHERE some_column IS NULL for a text column that might also contain empty strings. Run the Oracle equivalent against a test table with an explicit empty-string row and confirm the row does come back for IS NULL — a result that would have been wrong on SQL Server. Then find one query that uses GETDATE() or DATEADD and rewrite it with SYSDATE and ADD_MONTHS.

🙋 There Are No Dumb Questions

Q: Is there a setting to make Oracle behave like SQL Server for all of this — keep empty strings distinct from NULL, be case-insensitive, autocommit by default?
A: Partially, and it's not recommended. Case sensitivity can be changed via NLS_SORT/NLS_COMP session parameters. Autocommit-like behavior can be approximated in some client tools/drivers (many JDBC/ODBC connections default to autocommit mode even against Oracle, which surprises people the other way). But there's no supported setting that makes Oracle store '' as a distinct, non-null value — that behavior is fundamental to how Oracle represents character data, not a configurable session option. The better long-term approach is to write Oracle code that matches Oracle's actual semantics rather than trying to reproduce SQL Server's exactly.

✅ Quick Check

A developer ports a SQL Server batch script to Oracle. It runs ten UPDATE statements in a row with no explicit COMMIT anywhere, out of habit from SQL Server where each statement is durable the instant it succeeds. What actually happens on Oracle?

That's the guide — time for the recap.

← Back to