ORA-03047: number is not syntactically valid following ...

ORA-03047: number '2' is not syntactically valid following '..., name FROM emp LIMIT '

A number appears in a place where Oracle's grammar does not allow one.

Lab-verifiedReproduced and fixed on Oracle AI Database 26ai Free 23.26.3.0.0 · 2026-11-14

ORA-03047 is one of the more specific syntax errors in Oracle 23ai. The message names the number that broke the statement and quotes the text just before it, for example number '2' is not syntactically valid following '..., name FROM emp LIMIT '.

So you do not need to search the whole query: look at the quoted text and the word right before the number. In practice it is usually a row-limit syntax copied from MySQL or PostgreSQL, or a missing comma between two numbers.

Causes and fixes

  1. MySQL-style LIMIT n without ORDER BY
  2. Missing comma between column positions in ORDER BY
  3. PostgreSQL-style OFFSET n without ROWS

1MySQL-style LIMIT n without ORDER BY

SELECT ... FROM emp LIMIT 2 is MySQL syntax. Oracle accepted LIMIT itself (as a plain name after the table) and failed on the number: the message is number '2' is not syntactically valid following '... FROM emp LIMIT '.

To get the first 2 rows, WHERE ROWNUM <= 2 worked and returned 2 rows. If you want them in a defined order, use ORDER BY emp_id FETCH FIRST 2 ROWS ONLY, which returned Kim and Lee.

Setup
Setuprun as lab_a
CREATE TABLE emp (emp_id NUMBER PRIMARY KEY, name VARCHAR2(20), dept_id NUMBER, salary NUMBER);
INSERT INTO emp VALUES (1, 'Kim', 10, 5000);
INSERT INTO emp VALUES (2, 'Lee', 10, 4200);
INSERT INTO emp VALUES (3, 'Park', 20, 6100);
INSERT INTO emp VALUES (4, 'Choi', 20, 3900);
COMMIT;
OUTPUT
Table created.


1 row created.


1 row created.


1 row created.


1 row created.


Commit complete.
Reproducerun as lab_a
SELECT emp_id, name
  FROM emp
 LIMIT 2;
OUTPUT
 LIMIT 2
       *
ERROR at line 3:
ORA-03047: number '2' is not syntactically valid following '..., name
FROM emp
LIMIT '
Help: https://docs.oracle.com/error-help/db/ora-03047/
Checkrun as lab_a
SELECT emp_id, name FROM emp WHERE ROWNUM <= 2;
OUTPUT
    EMP_ID NAME
---------- ------------------------------
         1 Kim
         2 Lee

2 rows selected.

If the query has ORDER BY before LIMIT, Oracle 23ai reports ORA-03049 for the word LIMIT instead. The fix is the same: FETCH FIRST n ROWS ONLY.

After the fixrun as lab_a
SELECT emp_id, name
  FROM emp
 ORDER BY emp_id
 FETCH FIRST 2 ROWS ONLY;
OUTPUT
    EMP_ID NAME
---------- ------------------------------
         1 Kim
         2 Lee

2 rows selected.

2Missing comma between column positions in ORDER BY

ORDER BY 1 2 DESC was meant to sort by the 1st column, then the 2nd. Without the comma, Oracle stops at the second number: number '2' is not syntactically valid following '... ORDER BY 1 '.

Add the comma: ORDER BY 1, 2 DESC. The query returned all 4 rows sorted by department, then by salary from high to low.

Setup
Setuprun as lab_a
CREATE TABLE emp (emp_id NUMBER PRIMARY KEY, name VARCHAR2(20), dept_id NUMBER, salary NUMBER);
INSERT INTO emp VALUES (1, 'Kim', 10, 5000);
INSERT INTO emp VALUES (2, 'Lee', 10, 4200);
INSERT INTO emp VALUES (3, 'Park', 20, 6100);
INSERT INTO emp VALUES (4, 'Choi', 20, 3900);
COMMIT;
OUTPUT
Table created.


1 row created.


1 row created.


1 row created.


1 row created.


Commit complete.
Reproducerun as lab_a
SELECT dept_id, salary, name
  FROM emp
 ORDER BY 1 2 DESC;
OUTPUT
 ORDER BY 1 2 DESC
            *
ERROR at line 3:
ORA-03047: number '2' is not syntactically valid following '..., name
FROM emp
ORDER BY 1 '
Help: https://docs.oracle.com/error-help/db/ora-03047/
Checkrun as lab_a
SELECT dept_id, salary, name
  FROM emp
 ORDER BY dept_id, salary DESC;
OUTPUT
   DEPT_ID     SALARY NAME
---------- ---------- ------------------------------
        10       5000 Kim
        10       4200 Lee
        20       6100 Park
        20       3900 Choi

4 rows selected.

Writing the column names instead, ORDER BY dept_id, salary DESC, gave the same order. Names are easier to check than positions.

After the fixrun as lab_a
SELECT dept_id, salary, name
  FROM emp
 ORDER BY 1, 2 DESC;
OUTPUT
   DEPT_ID     SALARY NAME
---------- ---------- ------------------------------
        10       5000 Kim
        10       4200 Lee
        20       6100 Park
        20       3900 Choi

4 rows selected.

3PostgreSQL-style OFFSET n without ROWS

FROM emp OFFSET 2 works in PostgreSQL, but Oracle needs the full form. As with LIMIT, the error is on the number: number '2' is not syntactically valid following '... FROM emp OFFSET '.

Write ORDER BY emp_id OFFSET 2 ROWS FETCH NEXT 2 ROWS ONLY. Of the 4 employees, it skipped the first 2 and returned Park and Choi.

Setup
Setuprun as lab_a
CREATE TABLE emp (emp_id NUMBER PRIMARY KEY, name VARCHAR2(20), dept_id NUMBER, salary NUMBER);
INSERT INTO emp VALUES (1, 'Kim', 10, 5000);
INSERT INTO emp VALUES (2, 'Lee', 10, 4200);
INSERT INTO emp VALUES (3, 'Park', 20, 6100);
INSERT INTO emp VALUES (4, 'Choi', 20, 3900);
COMMIT;
OUTPUT
Table created.


1 row created.


1 row created.


1 row created.


1 row created.


Commit complete.
Reproducerun as lab_a
SELECT emp_id, name
  FROM emp
OFFSET 2;
OUTPUT
OFFSET 2
       *
ERROR at line 3:
ORA-03047: number '2' is not syntactically valid following '..., name
FROM emp
OFFSET '
Help: https://docs.oracle.com/error-help/db/ora-03047/
Checkrun as lab_a
SELECT COUNT(*) AS total FROM emp;
OUTPUT
     TOTAL
----------
         4

1 row selected.

Always put ORDER BY in a paging query. Without it, which rows count as "the first 2" is not defined.

After the fixrun as lab_a
SELECT emp_id, name
  FROM emp
 ORDER BY emp_id
OFFSET 2 ROWS FETCH NEXT 2 ROWS ONLY;
OUTPUT
    EMP_ID NAME
---------- ------------------------------
         3 Park
         4 Choi

2 rows selected.

FAQ

Does Oracle support LIMIT?

No. Use FETCH FIRST n ROWS ONLY (with ORDER BY for a fixed order) or WHERE ROWNUM <= n. Both were run in this lab and returned 2 rows.

How do I read the ORA-03047 message?

It shows the number that failed and the text right before it. In following '... FROM emp LIMIT ', the word before the number is LIMIT, so that is where the syntax is wrong.

Why do I get ORA-03047 instead of ORA-00933?

Oracle 23ai reports many syntax mistakes with newer, more specific errors such as ORA-03047, ORA-03048 and ORA-03049. In this lab, LIMIT 2 produced ORA-03047.

How do I write OFFSET pagination in Oracle?

ORDER BY col OFFSET n ROWS FETCH NEXT m ROWS ONLY. In this lab OFFSET 2 ROWS FETCH NEXT 2 ROWS ONLY returned rows 3 and 4.

How we verify

Each scenario starts from fresh database users. A script runs the setup, confirms the exact error code, applies the fix and runs the statement again. The output shown is what the database printed, saved by the script.

lab_a and lab_b are ordinary users created for the test; system is the DBA account. Oracle AI Database 26ai Free Release 23.26.3.0.0 - Develop, Learn, and Run for Free

Related errors