Oracle 添加Dinamic空值以执行并集

你好，先生，
这是我第一次给你写信!

我有必要列出每个表的数据年龄 (我们在表中拥有的最旧数据)。


我试图为模式的每个表生成一个dinamic sqls，如下所示:

SELECT'ALL_OBJECTS', MIN(CREATED), MIN(LAST_DDL_TIME) from ALL_OBJECTS;
SELECT 'USER_ADDM_TASKS',MIN(CREATED), MIN(LAST_MODIFIED), MIN(EXECUTION_START), MIN(EXECUTION_END)from USER_ADDM_TASKS;
复制

...

我的尝试:

SELECT *
  FROM (
SELECT l_tname,
       'select ' || '''' || l_tname || ''',' || SUBSTR (cl, 1, LENGTH (cl) - 1) || 'from ' || l_tname,
       ROW_NUMBER () OVER (PARTITION BY l_tname ORDER BY ROWNUM) num
  FROM (SELECT table_name l_tname,
               (SELECT REPLACE (XMLAGG (XMLELEMENT (e, 
                                                       CASE WHEN data_type = 'DATE' 
                                                            THEN Q'#MIN(#' || COLUMN_NAME || ')' --else ''
                                                       END, ',') 
                                        ORDER BY column_id ASC).EXTRACT ('//text()').getClobVal (), '&' || 'apos;', '''')
                          cl2
                  FROM all_tab_cols
                 WHERE table_name = tab1.table_name AND data_type = 'DATE')
                  cl
          FROM all_tab_cols tab1
         WHERE table_name IN ('ALL_OBJECTS', 'USER_ADDM_TASKS') AND data_type = 'DATE')
)
 WHERE num = 1  
复制

现在我需要在连接 | | 之前添加空列

它必须看起来像:
选择 “所有对象” 、 “最小 (创建)” 、 “最小 (最后一次ddl_time)”, NULL, NULL从所有对象联合所有
从USER_ADDM_TASKS中选择 “USER_ADDM_TASKS”，MIN (创建)，MIN (最后修改)，MIN (执行 _ 开始)，MIN (执行 _ 结束);


我知道我的模式中每个表最多有10个日期列:

SELECT TABLE_NAME, COUNT(*)FROM ALL_TAB_COLS
WHERE DATA_TYPE='DATE'
GROUP BY TABLE_NAME
ORDER BY 2 DESC
复制

因此，我需要填充空列，以获得每个select中的总共10列，以便具有union all。

你能帮忙吗？

非常感谢。

为此，我创建了all_tab_cols的 “假” 版本，其中多达10个人工列全部添加为null，即

SQL> with modified_all_tables as
  2  ( select distinct owner, table_name
  3    from all_tab_cols
  4    where data_type = 'DATE' ),
  5  modified_all_tab_cols as
  6  ( select owner,
  7           table_name,
  8           'min('||column_name||')' column_name,
  9           column_id
 10    from   all_tab_cols
 11    where  data_type = 'DATE'
 12    union all
 13    select owner,
 14           table_name,
 15           'null' column_name,
 16           2000+d
 17    from   modified_all_tables,
 18           ( select rownum d from dual connect by level <= 10 )
 19  )
 20  SELECT table_name,
 21         (SELECT XMLAGG(XMLELEMENT(e,column_name,',') ORDER BY column_id ASC).EXTRACT ('//text()').getClobVal () cl2
 22          FROM modified_all_tab_cols
 23          WHERE table_name = t1.table_name) cl
 24  FROM   modified_all_tables t1
 25  WHERE table_name IN ('ALL_OBJECTS', 'USER_ADDM_TASKS');

TABLE_NAME
------------------------------
CL
------------------------------------------------------------------------------------------------------------------------------
ALL_OBJECTS
min(CREATED),min(LAST_DDL_TIME),null,null,null,null,null,null,null,null,null,null,

USER_ADDM_TASKS
min(CREATED),min(LAST_MODIFIED),min(EXECUTION_START),min(EXECUTION_END),null,null,null,null,null,null,null,null,null,null,
复制

所以现在我有了，我需要做的就是挑出第10个逗号的文本，即，

SQL> with modified_all_tables as
  2  ( select distinct owner, table_name
  3    from all_tab_cols
  4    where data_type = 'DATE' ),
  5  modified_all_tab_cols as
  6  ( select owner,
  7           table_name,
  8           'min('||column_name||')' column_name,
  9           column_id
 10    from   all_tab_cols
 11    where  data_type = 'DATE'
 12    union all
 13    select owner,
 14           table_name,
 15           'null' column_name,
 16           2000+d
 17    from   modified_all_tables,
 18           ( select rownum d from dual connect by level <= 10 )
 19  )
 20  select table_name,
 21         substr(cl,1,instr(cl,',',1,10)-1) str
 22  from
 23  (
 24  SELECT table_name,
 25         (SELECT XMLAGG(XMLELEMENT(e,column_name,',') ORDER BY column_id ASC).EXTRACT ('//text()').getClobVal () cl2
 26          FROM modified_all_tab_cols
 27          WHERE table_name = t1.table_name) cl
 28          FROM   modified_all_tables t1
 29  WHERE table_name IN ('ALL_OBJECTS', 'USER_ADDM_TASKS')
 30  );

TABLE_NAME
------------------------------
STR
-------------------------------------------------------------------------------------------------------------------------
ALL_OBJECTS
min(CREATED),min(LAST_DDL_TIME),null,null,null,null,null,null,null,null

USER_ADDM_TASKS
min(CREATED),min(LAST_MODIFIED),min(EXECUTION_START),min(EXECUTION_END),null,null,null,null,null,null
复制