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>

Answer 2

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