Oracle VIEW showing when last records were created in my tables

Question

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?

Answer 1

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>