Oracle SQL: Implicit Conversion and ROWNUM Explained

Oracle SQL is very flexible, but this flexibility can sometimes lead to unexpected results. Two areas where developers often face surprises are Implicit Conversion and ROWNUM. This guide explains both concepts clearly with practical examples.

1. Implicit Conversion in Oracle SQL

Oracle automatically converts data types when it can. This is called implicit conversion. While convenient, it can cause performance problems and incorrect results if not understood properly.

Common Implicit Conversion Scenarios

Example 1: VARCHAR2 to NUMBER

CREATE TABLE employees (
  emp_id     VARCHAR2(10),
  salary     NUMBER
);

-- This works because Oracle converts '101' to number
SELECT * FROM employees WHERE emp_id = 101;

Oracle converts the string '101' to number 101 to compare with the column. However, this can prevent index usage in some cases.

Example 2: DATE and String Comparison

SELECT * FROM employees 
WHERE hire_date = '01-JAN-2023';

Oracle converts the string to a DATE using the default date format (usually DD-MON-YYYY). This works but is risky if the session date format changes.

Best Practice: Always use explicit conversion functions like TO_NUMBER(), TO_DATE(), or TO_CHAR() for clarity and performance.

Why Implicit Conversion Can Be Dangerous

• Can cause full table scans even when indexes exist
• May lead to incorrect results with different session settings
• Makes code harder to debug

2. ROWNUM in Oracle SQL

ROWNUM is a pseudocolumn that returns a number indicating the order in which Oracle selects rows from a table. It is very useful but has some important behaviors that often confuse developers.

How ROWNUM Works

SELECT ROWNUM, first_name, salary
FROM employees;

ROWNUM is assigned before the ORDER BY clause is processed. This is the most common source of confusion.

Common Mistake: Using ROWNUM with ORDER BY

-- Wrong way to get top 5 highest paid employees
SELECT ROWNUM, first_name, salary
FROM employees
ORDER BY salary DESC;

This does not give the top 5 highest salaries because ROWNUM is assigned before sorting.

Correct Way: Using ROWNUM with Subquery

SELECT ROWNUM, first_name, salary
FROM (
  SELECT first_name, salary
  FROM employees
  ORDER BY salary DESC
)
WHERE ROWNUM <= 5;

This works correctly because the inner query sorts first, then ROWNUM is applied on the sorted result.

Other Useful ROWNUM Patterns

-- Get rows 6 to 10 (pagination)
SELECT * FROM (
  SELECT ROWNUM rn, first_name, salary
  FROM employees
  ORDER BY salary DESC
)
WHERE rn BETWEEN 6 AND 10;

Key Takeaways

• Always use explicit conversion when data types might differ
• ROWNUM is assigned before ORDER BY
• Use a subquery when you need to sort first and then limit rows with ROWNUM
• Be careful with implicit conversion in WHERE clauses — it can hurt performance

Interactive Quiz

Q1. What happens when you compare a VARCHAR2 column with a number using = ?

A. Oracle raises an error B. Oracle performs implicit conversion C. The query returns no rows D. The column is automatically converted to VARCHAR2

Q2. When is ROWNUM assigned to rows?

A. After ORDER BY is processed B. Before ORDER BY is processed C. Only when using a subquery D. After the entire query finishes

Q3. What is the correct way to get the top 5 highest paid employees using ROWNUM?

A. SELECT * FROM employees WHERE ROWNUM <= 5 ORDER BY salary DESC B. Use a subquery to sort first, then apply ROWNUM C. Both methods give the same result D. ROWNUM cannot be used with ORDER BY