query that runs on particular Oracle DB versions only

Question

i have the below query which is required to run on the Oracle versions-12c,19c

select owner, credential_name, username from dba_credentials where credential_name = 'GLOBAL_EXTPROC_CREDENTIAL' 
union 
select owner, credential_name, username from cdb_credentials where credential_name = 'GLOBAL_EXTPROC_CREDENTIAL'

But this query is deployes to run across all the versions(11g,12c,19c) as metric.So if the version is 11g the query should not run,but if its 12c or 19c it should run. Is there a way to get this done in a sql query? Please suggest.Thanks.

I have tried to use below query ,but didnt work as expected.

with ver as
  (select distinct substr(version, 1, 2) version
   from product_component_version
  )
select owner, credential_name, username  
From (select owner, credential_name, username from dba_credentials where credential_name = 'GLOBAL_EXTPROC_CREDENTIAL' 
union 
select owner, credential_name, username from cdb_credentials where credential_name = 'GLOBAL_EXTPROC_CREDENTIAL')
where exists (select null from ver where version != '11');

Answer 1

The below query uses DBMS_XMLGEN to run a different version of the query depending on the version of the database.

I've tested this query on 11.2 XE, 19 EE, and 23 free developer edition. But the query assumes the user has relatively high privileges and can view DBA* views.

--#4: Get the results from the XMLType.
select owner, credential_name, username
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
                (
                    select count(*)
                    from all_views
                    where owner = 'SYS' and view_name = 'CDB_CREDENTIALS'
                )
                when 1 then
                    q'[
                        select owner, credential_name, username from dba_credentials where credential_name = 'GLOBAL_EXTPROC_CREDENTIAL' 
                        union 
                        select owner, credential_name, username from cdb_credentials where credential_name = 'GLOBAL_EXTPROC_CREDENTIAL'
                    ]'
                else
                    '
                        select cast(null as varchar2(4000)) owner, cast(null as varchar2(4000)) credential_name, cast(null as varchar2(4000)) username
                        from dual
                        where 1 = 0
                    '
                end v_sql
            from dual
        )
    )
    where xml_clob_results is not null
)
cross join xmltable
(
    '/ROWSET/ROW'
    passing xmltype_results
    columns
        owner varchar2(4000) path 'OWNER',
        credential_name varchar2(4000) path 'CREDENTIAL_NAME',
        username varchar2(4000) path 'USERNAME'
);