Learn / SQL Server → Oracle / Queries & Pagination
Lesson 2 of 5

Queries & Pagination

Core SELECT syntax is nearly identical between the two engines — joins, group by, subqueries all translate almost word for word. Where SQL Server developers get tripped up is the handful of things Oracle's grammar needed a special mechanism for that T-SQL just doesn't need — plus a few renamed string functions.

💡 The Bottom Line

SQL Server can run a SELECT with no FROM clause at all, and has a purpose-built OFFSET...FETCH clause for pagination. Oracle's grammar requires a FROM clause on every query, so it ships a permanent one-row DUAL table just so you can select a constant — and classic Oracle pagination leans on the ROWNUM pseudo-column, which behaves in a way that surprises almost everyone the first time. Where SQL Server mostly kept SQL-standard function names (SUBSTRING, CHARINDEX), Oracle mostly uses C-style names (SUBSTR, INSTR) — the logic is the same, only the spelling differs.

Pagination: OFFSET/FETCH vs ROWNUM

SQL Server (what you know)
SELECT *
FROM employees
ORDER BY hire_date DESC
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;
Oracle (the new equivalent)
SELECT *
FROM (
  SELECT e.*, ROWNUM rn
  FROM (
    SELECT * FROM employees
    ORDER BY hire_date DESC
  ) e
  WHERE ROWNUM <= 20
)
WHERE rn > 10;

📋 Key differences

  • SQL Server's OFFSET...FETCH (added in SQL Server 2012) does exactly what it reads as: skip N rows, then take the next M — no subquery gymnastics required, and it requires an ORDER BY to be meaningful.
  • Classic Oracle's ROWNUM is assigned before ORDER BY is applied to the outer query — which is why the pattern above needs a nested subquery just to paginate correctly. This is one of the most common Oracle interview gotchas, precisely because it looks like it should work as a simple WHERE ROWNUM > 10 AND ROWNUM <= 20 and silently returns zero rows if you try that.
  • Oracle 12c+ also added a direct equivalent, OFFSET n ROWS FETCH NEXT m ROWS ONLY — if you're targeting a modern Oracle version, prefer that syntax over the classic ROWNUM pattern above; it reads almost exactly like the T-SQL you already know.
  • For "just give me the top N," SQL Server's SELECT TOP (n) maps to Oracle's modern FETCH FIRST n ROWS ONLY (or classic WHERE ROWNUM <= n, which works fine for this simpler case since there's no offset to fight with).

No table at all vs the DUAL table

SQL Server (what you know)
SELECT GETDATE();

SELECT 1 + 1;
Oracle (the new equivalent)
SELECT SYSDATE FROM DUAL;

SELECT 1 + 1 FROM DUAL;

SQL Server's grammar has no restriction on this — FROM is optional, so you just omit it. Oracle's grammar requires every SELECT to have a FROM clause, so Oracle ships a permanent one-row, one-column table called DUAL purely so you can select a constant or call a function without a real table.

⚠️ Gotcha

If you write SELECT GETDATE(); or SELECT 1 + 1; straight into Oracle, it fails with ORA-00923: FROM keyword not found where expected — Oracle simply won't let a query skip the FROM clause. You need FROM DUAL tacked on the end, every time, even for the simplest constant expression. It feels like unnecessary ceremony coming from SQL Server, but it's baked into Oracle's parser and isn't optional.

String functions: renamed, not reinvented

SQL Server (what you know)
SELECT SUBSTRING(last_name, 1, 3),
       CHARINDEX('a', last_name),
       LEN(last_name)
FROM employees;
Oracle (the new equivalent)
SELECT SUBSTR(last_name, 1, 3),
       INSTR(last_name, 'a'),
       LENGTH(last_name)
FROM employees;

📋 Key differences

  • SUBSTRING(string, start, length) → SUBSTR(string, start, length) — same three arguments, same order, just the shorter C-style spelling. (Oracle's SUBSTR also accepts a negative start position to count from the end of the string — SQL Server's SUBSTRING does not.)
  • CHARINDEX(search, string) → INSTR(string, search) — note the argument order flips: SQL Server takes (needle, haystack), Oracle takes (haystack, needle).
  • LEN(string) → LENGTH(string) — same idea, but one subtlety: SQL Server's LEN() trims trailing spaces from the count; Oracle's LENGTH() does not.
  • Concatenation inside these calls still follows lesson 1's rule: + or CONCAT() in SQL Server, || in Oracle.

🔧 Try It Yourself

Take a SQL Server report query that uses SELECT TOP (20) ... ORDER BY ... and rewrite it for Oracle 12c+ using FETCH FIRST 20 ROWS ONLY. Then take a second query that pages results (rows 11–20) and rewrite it two ways: once using Oracle 12c+'s OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY, and once using the classic nested ROWNUM pattern, so you recognize both when you see them in existing code.

🙋 There Are No Dumb Questions

Q: Does Oracle have anything like SQL Server's ROW_NUMBER() window function?
A: Yes — Oracle has ROW_NUMBER() OVER (ORDER BY ...) too, working identically to SQL Server's version: a window function that assigns a sequential number per row based on an explicit ordering, applied to already-ordered results. Unlike classic ROWNUM, it doesn't have the "filter-before-sort" trap that makes the nested-subquery pagination pattern necessary — if you'd rather use the same construct you already know from SQL Server instead of learning ROWNUM's quirks, ROW_NUMBER() is available on both sides.

✅ Quick Check

You want to select the literal value 42 with no table involved. In classic Oracle syntax, what's the correct query?

Next up: how each engine auto-generates numbers — SQL Server's IDENTITY columns versus Oracle's standalone SEQUENCE objects.

← Back to
Next →