Hello Sir,
it's the first time I write to you!
I've the necessity of listing per table the age of data (oldest data thet we have in the table).
i'm trying to generate a dinamic sqls for each table of the schema like:
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;
...
my attemp:
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
now I need to add dinamicaly the null columns befor concatenating the ||'union all'
it would have to look like:
select 'ALL_OBJECTS',MIN(CREATED),MIN(LAST_DDL_TIME)
, NULL, NULL from ALL_OBJECTS UNION ALL
select 'USER_ADDM_TASKS',MIN(CREATED),MIN(LAST_MODIFIED),MIN(EXECUTION_START),MIN(EXECUTION_END)from USER_ADDM_TASKS;
I know i'll have at most 10 Date columns per table in my schema:
SELECT TABLE_NAME, COUNT(*)FROM ALL_TAB_COLS
WHERE DATA_TYPE='DATE'
GROUP BY TABLE_NAME
ORDER BY 2 DESC
so I'd need to fill dinamicly the null columns to get the total of 10 columns in each select in order to have a union all.
Could you help?
Thank you very much.
To do this, I create "fake" versions of all_tab_cols which has up to 10 artificial columns added all as null, ie
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,
so now that I have that, all I need do is pick out the text up to the 10th comma, ie,
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