Select a column that potentially doesn't exist in Oracle dictionary views

Question

I want to create a backwards compatible query on SYS.ALL_ARGUMENTS. In Oracle 11g, the useful ALL_ARGUMENTS.DEFAULTED column was added. Now if I run this query against Oracle 10g:

SELECT defaulted FROM all_arguments

I get an error, of course.

ORA-00904: "SYS"."ALL_ARGUMENTS"."DEFAULTED": invalid identifier

What I'd like to do is this:

SELECT CASE WHEN column_exists("defaulted") 
            THEN defaulted 
            ELSE 'N'
       END
FROM all_arguments

Or even better

SELECT evaluate_column_on_current_row(column_name           => "defaulted", 
                                      default_if_not_exists => 'N')
FROM all_arguments

Is there some way to do that in a single SQL query, without resorting to PL/SQL? Or should I check the Oracle version first like this:

SELECT count(*) 
FROM all_tab_cols
WHERE owner = 'SYS'
AND table_name = 'ALL_ARGUMENTS'
AND column_name = 'DEFAULTED'

Answer 1

A query that references a column that doesn't exist can't generate a valid plan.

You need to choose an approach where the queries submitted are always valid. Be that dynamically generating/executing them, or some other approach.

But if you submit a query to be parsed, and it contains a non existant field on an existant table, the parser will throw it back at you.