如何根据Oracle版本自动适配不同视图的查询语句

你的报错原因很明确：Oracle在解析SQL时会先校验所有引用的对象是否存在，哪怕对象只出现在某个条件分支里。11g环境里没有cdb_directories视图，所以不管你加了什么版本判断，语句都会直接触发ORA-00942错误。

要实现跨版本无报错运行，得让Oracle在对应版本里只处理存在的视图，这里提供两种可行方案：

方案1：纯SQL动态执行（适配OEM指标返回结果集） #

利用DBMS_XMLGEN动态生成并执行对应版本的SQL，再把XML结果解析成常规结果集：

WITH version_check AS (
  SELECT CASE WHEN SUBSTR(version, 1, 2) = '11' THEN '11' ELSE 'OTHER' END AS db_version
  FROM v$instance
),
sql_def AS (
  SELECT CASE db_version
         WHEN '11' THEN 
           '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 
           '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''))'
         END AS sql_stmt
  FROM version_check
)
SELECT x.*
FROM sql_def,
     XMLTABLE(
       '/ROWSET/ROW'
       PASSING DBMS_XMLGEN.GETXMLTYPE(sql_stmt)
       COLUMNS
         DB_OWNER_DIRECTORY VARCHAR2(200) PATH 'DB_OWNER_DIRECTORY',
         owner VARCHAR2(100) PATH 'OWNER',
         directory_name VARCHAR2(100) PATH 'DIRECTORY_NAME',
         DIRECTORY_PATH VARCHAR2(500) PATH 'DIRECTORY_PATH'
     ) x;

原理是先根据版本生成对应SQL，再通过XML工具执行并解析结果，11g环境下不会生成引用cdb_directories的语句，自然不会报错。

方案2：PL/SQL动态执行（适合自定义输出场景） #

如果OEM支持执行PL/SQL块，可以用分支判断直接执行对应版本的查询：

DECLARE
  v_db_version VARCHAR2(2);
  TYPE dir_rec_type IS RECORD(
    db_owner_directory VARCHAR2(200),
    owner VARCHAR2(100),
    directory_name VARCHAR2(100),
    directory_path VARCHAR2(500)
  );
  v_dir_rec dir_rec_type;
  CURSOR c_11g IS
    SELECT d.db_unique_name||':'||dd.owner||':'||dd.directory_name,
           dd.owner, dd.directory_name, dd.DIRECTORY_PATH
    FROM dba_directories dd, v$database d;
  CURSOR c_12c_plus IS
    SELECT d.db_unique_name||':'||cd.owner||':'||cd.directory_name,
           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'));
BEGIN
  SELECT SUBSTR(version, 1, 2) INTO v_db_version FROM v$instance;
  
  IF v_db_version = '11' THEN
    OPEN c_11g;
    LOOP
      FETCH c_11g INTO v_dir_rec;
      EXIT WHEN c_11g%NOTFOUND;
      -- 可根据OEM需求调整输出方式，比如插入临时表
      DBMS_OUTPUT.PUT_LINE(v_dir_rec.db_owner_directory || ',' ||
                           v_dir_rec.owner || ',' ||
                           v_dir_rec.directory_name || ',' ||
                           v_dir_rec.directory_path);
    END LOOP;
    CLOSE c_11g;
  ELSE
    OPEN c_12c_plus;
    LOOP
      FETCH c_12c_plus INTO v_dir_rec;
      EXIT WHEN c_12c_plus%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(v_dir_rec.db_owner_directory || ',' ||
                           v_dir_rec.owner || ',' ||
                           v_dir_rec.directory_name || ',' ||
                           v_dir_rec.directory_path);
    END LOOP;
    CLOSE c_12c_plus;
  END IF;
END;
/

注意事项 #

• 执行语句的用户需要拥有DBA_DIRECTORIES、CDB_DIRECTORIES、V$INSTANCE、V$DATABASE的查询权限。
• 方案1的XML解析字段长度可根据实际环境调整，避免数据截断。

内容的提问来源于stack exchange，提问作者Roshni Rabi