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
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
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;Table created.
1 row created.
1 row created.
1 row created.
1 row created.
Commit complete.SELECT emp_id, name
FROM emp
LIMIT 2; 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/SELECT emp_id, name FROM emp WHERE ROWNUM <= 2; 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.
SELECT emp_id, name
FROM emp
ORDER BY emp_id
FETCH FIRST 2 ROWS ONLY; 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
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;Table created.
1 row created.
1 row created.
1 row created.
1 row created.
Commit complete.SELECT dept_id, salary, name
FROM emp
ORDER BY 1 2 DESC; 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/SELECT dept_id, salary, name
FROM emp
ORDER BY dept_id, salary DESC; 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.
SELECT dept_id, salary, name
FROM emp
ORDER BY 1, 2 DESC; 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
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;Table created.
1 row created.
1 row created.
1 row created.
1 row created.
Commit complete.SELECT emp_id, name
FROM emp
OFFSET 2;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/SELECT COUNT(*) AS total FROM emp; 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.
SELECT emp_id, name
FROM emp
ORDER BY emp_id
OFFSET 2 ROWS FETCH NEXT 2 ROWS ONLY; 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