how to query a different view depending on oracle version

i have 2 queries.The 1st one runs on 11g: select owner,directory_name,directory_path from dba_directories;

The 2nd one runs on 12c an 19c oracle versions only: select owner,directory_name,directory_path from cdb_directories;

The objective is to combine these 2 sql's into a single query such that it runs seemlessly without any error on any version of the database. Please note the combined query will be run in OEM as a metric.

i have tried a few variants, but didnt work.

with version_check as ( select case when substr(version,1,2)= '11' then '11' else 'OTHER' end as db_version from v$instance ) select d.db_unique_name||':'||dd.owner||':'||dd.directory_name DB_OWNER_DIRECTORY,dd.owner, dd.directory_name, dd.DIRECTORY_PATH from dba_directories dd, v$database d where (select db_version from version_check)= '11' union all select d.db_unique_name||':'||cd.owner||':'||cd.directory_name DB_OWNER_DIRECTORY, cd.owner,cd.directory_name,cd.directory_path FROM cdb_directories cd, v$database d WHERE cd.origin_con_id NOT IN (0, 1) AND (REGEXP_LIKE (cd.directory_path,sys_context('USERENV', 'ORACLE_HOME')) OR REGEXP_LIKE (cd.directory_path,'/tmp') OR REGEXP_LIKE (cd.directory_path,'/usr/tmp')) and (select db_version from version_check)<> '11';

The above gives ORA-00942: table or view does not exist

Use the below DBMS_XMLGEN.GETXML trick to create a pure SQL statement that will run different SQL depending on the version of the database. This result is pretty similar to previous questions you've asked, so you might want to save this code as a template for any future question involving a single SQL statement that runs on multiple versions.

--Directory information from either DBA or CDB tables, depending on the database version.
--
--#4: Get the size from the XMLType.
select db_owner_directory, owner, directory_name, directory_path
from
(
    --#3: Convert the XML to an XMLType.
    select xmltype(xml_clob_results) xmltype_results
    from
    (
        --#2: Convert the SQL to XML.
        select dbms_xmlgen.getxml(v_sql) xml_clob_results
        from
        (
            --#1: Use data dictionary to create SQL statement based on which views exist.
            select
                case when substr(version,1,2)= '11' then
                    q'[
                        select d.db_unique_name||':'||dd.owner||':'||dd.directory_name DB_OWNER_DIRECTORY,dd.owner, dd.directory_name, dd.DIRECTORY_PATH
                        from dba_directories dd, v$database d
                    ]'
                else
                    q'[
                        select d.db_unique_name||':'||cd.owner||':'||cd.directory_name DB_OWNER_DIRECTORY, cd.owner,cd.directory_name,cd.directory_path
                        FROM cdb_directories cd, v$database d
                        WHERE cd.origin_con_id NOT IN (0, 1) 
                            -- Commented out these predicates because they threw an error on my system.
                            --AND (REGEXP_LIKE (cd.directory_path,sys_context('USERENV', 'ORACLE_HOME'))
                            --OR REGEXP_LIKE (cd.directory_path,'/tmp')
                            --OR REGEXP_LIKE (cd.directory_path,'/usr/tmp')
                            --)
                    ]'
                end v_sql
            from v$instance
        )
    )
    where xml_clob_results is not null
)
cross join xmltable
(
    '/ROWSET/ROW'
    passing xmltype_results
    columns
        DB_OWNER_DIRECTORY varchar2(4000) path 'DB_OWNER_DIRECTORY',
        OWNER varchar2(4000) path 'OWNER',
        DIRECTORY_NAME varchar2(4000) path 'DIRECTORY_NAME',
        DIRECTORY_PATH varchar2(4000) path 'DIRECTORY_PATH'
);