PL/SQL (MOSC)

MOSC Banner

Oracle 11.2.0.4.0. error ORA-01791 running SELECT DISTINCT TO_CHAR(...) query

in PL/SQL (MOSC) 2 commentsAnswered ✓

hi,

Im managing a WPF (.NET technology) application, connected to a Oracle 11.2.0.4.0 DB.

Last week, DB managers have upgraded old 11.2.0.1.0 DB to 11.2.0.4.0.

Now, users reported the above error on running a simple query like this


SELECT DISTINCT TO_CHAR(T.DATETIME,'DD/MM/RRRR HH24:MI') AS DATETIME, T.ID,....

FROM MY_TAB T

WHERE .....

ORDER BY T.DATETIME DESC, T.ID;

ORA-01791 - NOT A SELECTED EXPRESSION!

Note that if I replace ORDER BY T.DATETIME with ORDER BY DATETIME, query works, but data sorting is wrong (string sorting may not be matching datetime sorting).

Excepting DB version upgrade, there are no reasons for this issue to be raised (query was working on 11.2.0.1.0); and I wish to fix it avoiding having to re-compile and deploy the whole application.

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center