oracle error messages

Oracle/PLSQL: ORA-01785

Learn the cause and how to resolve the ORA-01785 error message in Oracle.


When you encounter an ORA-01785 error, the following error message will appear:

  • ORA-01785: ORDER BY item must be the number of a SELECT-list expression


You tried to execute a SELECT statement that included a ORDER BY clause that referenced a column number that did not correspond to a valid column in your SELECT list.


The option(s) to resolve this Oracle error are:

Option #1

Rewrite the SELECT statement so that the ORDER BY clause references valid column numbers from your SELECT list. Remember that the first item in your SELECT list is column 1, the second is column 2, and so on.

For example, if you had tried to execute the following SELECT statement:

SELECT supplier_id, supplier_name
FROM suppliers

You would receive the following error message:

Oracle PLSQL

Since there are only 2 items in your SELECT list, you can reference column 1 or column 2, but not column 3. The following SQL statement would be correct:

SELECT supplier_id, supplier_name
FROM suppliers
ORDER BY 1, 2;