ORA-01791: not a SELECTed expression - Oracle Error

This Error message might show up when you are trying to use DISTINCT with ORDER BY clause. Let us try and understand when this error message comes and what is causing it. Take the following simple SQL for example:

  SELECT    DISTINCT a, b
    FROM      mytable
ORDER BY c

When we try to run the above SQL we will get the error message: "ORA-01791: not a SELECTed expression".
The reason for this is simple, once you have applied DISTINCT to the result set the only columns that are available are the ones written (calculated) in the SELECT query.
So if you try to run the below query then it will run without any issues:

   SELECT   DISTINCT a, b, c
    FROM      mytable
ORDER BY c

If this guide was helpful to you, do not forget to leave a Thanks message.

Difference between dba_source and all_source

In short the difference between dba_source and all_source is:

ALL_SOURCE describes the text source (all the code) of the stored objectsaccessible to the current user. Whereas DBA_SOURCE is not limited for a particular user, it describes the text source of all stored objects in the database.

Wait that's not all there is!

Both dba_source and all_source have 5 columns: OWNER, NAME,TYPE, LINE and TEXT. Other then these two there is one more view called USER_SOURCE which describes the text source of the stored objects owned by the current user.

Just tell me how to use it!

Most common way to use these:

1	SELECT * FROM dba_source where TEXT like '%package.procedure/function_name%'

view rawfirst_post hosted with ❤ by GitHub

Source: Oracle Docs for DBA_SOURCE, ALL_SOURCE