I have tables t1
and t2
and both of them do have columns created_on
containing the timestamp when each record was created. Usual thing.
Now I'd like to create the view which would show my tables and the timestamp of last created record (MAX(created_on)
) in corresponding table.
The result should look like:
table | last_record
====== ============
t1 | 10.05.2019
t2 | 12.11.2020
For example I can retrieve the list of my tables with:
SELECT * FROM USER_TABLES WHERE table_name LIKE 'T%'
I'd like to get the timestamp of last record for each of these tables.
How to create this view?
CodePudding user response:
It might depend on tables' description; I presume they are somehow related to each other.
Anyway: here's how I understood the question. Read comments within code.
SQL> with
2 -- sample data
3 t1 (id, name, created_on) as
4 (select 1, 'Little', date '2021-12-14' from dual union all --> max for Little
5 select 2, 'Foot' , date '2021-12-13' from dual union all --> max for Foot
6 select 2, 'Foot' , date '2021-12-10' from dual
7 ),
8 t2 (id, name, created_on) as
9 (select 2, 'Foot' , date '2021-12-09' from dual union all
10 select 3, 'SBrbot', date '2021-12-14' from dual --> max for SBrbot
11 )
12 -- query you'd use for a view
13 select id, name, max(created_on) max_created_on
14 from
15 -- union them, so that it is easier to find max date
16 (select id, name, created_on from t1
17 union all
18 select id, name, created_on from t2
19 )
20 group by id, name;
ID NAME MAX_CREATE
---------- ------ ----------
1 Little 14.12.2021
2 Foot 13.12.2021
3 SBrbot 14.12.2021
SQL>
After you fixed the question, that's even easier; view query begins at line #12:
SQL> with
2 -- sample data
3 t1 (id, name, created_on) as
4 (select 1, 'Little', date '2021-12-14' from dual union all
5 select 2, 'Foot' , date '2021-12-13' from dual union all
6 select 2, 'Foot' , date '2021-12-10' from dual
7 ),
8 t2 (id, name, created_on) as
9 (select 2, 'Foot' , date '2021-12-09' from dual union all
10 select 3, 'SBrbot', date '2021-12-14' from dual
11 )
12 select 't1' source_table, max(created_on) max_created_on from t1
13 union
14 select 't2' source_table, max(created_on) max_created_on from t2;
SO MAX_CREATE
-- ----------
t1 14.12.2021
t2 14.12.2021
SQL>
If it has to be dynamic, one option is to create a function that returns ref cursor:
SQL> create or replace function f_max
2 return sys_refcursor
3 is
4 l_str varchar2(4000);
5 rc sys_refcursor;
6 begin
7 for cur_r in (select distinct c.table_name
8 from user_tab_columns c
9 where c.column_name = 'CREATED_ON'
10 order by c.table_name
11 )
12 loop
13 l_str := l_str ||' union all select ' || chr(39) || cur_r.table_name || chr(39) ||
14 ' table_name, max(created_on) last_updated from ' || cur_r.table_name;
15 end loop;
16
17 l_str := ltrim(l_str, ' union all ');
18
19 open rc for l_str;
20 return rc;
21 end;
22 /
Function created.
Testing:
SQL> select f_max from dual;
F_MAX
--------------------
CURSOR STATEMENT : 1
CURSOR STATEMENT : 1
TA LAST_UPDAT
-- ----------
T1 14.12.2021
T2 14.12.2021
SQL>
CodePudding user response:
I have 30 tables and wanted avoid hard coding SELECT statements for each of these tables and UNION them all. I expected some solution where I would insert tablenames in kinda array and create JOIN to show result with all last records. I know problem is here that tablename is variable!
You cannot do this in SQL as a VIEW
is set at compile time and the tables must be known; the best you can do is to dynamically create an SQL statement in PL/SQL and then use EXECUTE IMMEDIATE
and then re-run it if you want to recreate the view:
DECLARE
v_tables SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST(
'TABLE1', 'TABLE2', 'table3', 'TABLE5'
);
v_sql CLOB := 'CREATE OR REPLACE VIEW last_dates (table_name, last_date) AS ';
BEGIN
FOR i in 1 .. v_tables.COUNT LOOP
IF i > 1 THEN
v_sql := v_sql || ' UNION ALL ';
END IF;
v_sql := v_sql || 'SELECT '
|| DBMS_ASSERT.ENQUOTE_LITERAL(v_tables(i))
|| ', MAX(created_on) FROM '
|| DBMS_ASSERT.ENQUOTE_NAME(v_tables(i), FALSE);
END LOOP;
EXECUTE IMMEDIATE v_sql;
END;
/
Then for the sample tables:
CREATE TABLE table1 (created_on) AS
SELECT SYSDATE - LEVEL FROM DUAL CONNECT BY LEVEL <= 3;
CREATE TABLE table2 (created_on) AS
SELECT SYSDATE - LEVEL FROM DUAL CONNECT BY LEVEL <= 3;
CREATE TABLE "table3" (created_on) AS
SELECT SYSDATE - LEVEL FROM DUAL CONNECT BY LEVEL <= 3;
CREATE TABLE table5 (created_on) AS
SELECT SYSDATE FROM DUAL;
After running the PL/SQL block, then:
SELECT * FROM last_dates;
Outputs:
TABLE_NAME LAST_DATE TABLE1 2021-12-13 13:21:58 TABLE2 2021-12-13 13:21:59 table3 2021-12-13 13:21:59 TABLE5 2021-12-14 13:21:59
db<>fiddle here