Oracle Function or Query to get data for all DB tables?-CodePudding

I have to get the min and max dates for the data stored in all the database tables and display same along with the table names. I have written below function to do same

CREATE OR REPLACE FUNCTION data_bound(tbl_name IN VARCHAR2) RETURN VARCHAR2
AS
  reqdates VARCHAR2;
BEGIN
  EXECUTE IMMEDIATE 'SELECT concat(min(rec_date),max(rec_date)) from ' || tbl_name INTO reqdates;
  RETURN reqdates;
END;

And I am trying to fetch data using below.

SELECT table_name, data_bound(table_name) FROM user_tables;

But my function does seems to be working, getting multiple errors. Please can you advise what's wrong here and if there's another better approach.

CodePudding user response：

You can use:

CREATE FUNCTION data_bound(
  tbl_name IN VARCHAR2
) RETURN VARCHAR2
AS
  reqdates VARCHAR2(39);
BEGIN
  EXECUTE IMMEDIATE 'SELECT TO_CHAR(min(rec_date), ''YYYY-MM-DD HH24:MI:SS'')
                         || ''-'' || TO_CHAR(max(rec_date), ''YYYY-MM-DD HH24:MI:SS'')
                     FROM   ' || DBMS_ASSERT.SIMPLE_SQL_NAME(tbl_name)
    INTO reqdates;
  RETURN reqdates;
END;
/

Which, if you have the table:

CREATE TABLE table_name (rec_date) AS
SELECT TRUNC(SYSDATE, 'YYYY') FROM DUAL UNION ALL
SELECT SYSDATE FROM DUAL;

Then:

SELECT data_bound('TABLE_NAME') FROM DUAL;

Outputs:

DATA_BOUND('TABLE_NAME')

2022-01-01 00:00:00-2022-04-19 18:57:25

DATA_BOUND('TABLE_NAME')
2022-01-01 00:00:00-2022-04-19 18:57:25

db<>fiddle here

CodePudding user response：

Error 1: data_bound() function is defined. data_retention() function is called

Error 2: tbl_name is taken as input, but not used (t_name is used once)

Error 3: 'from' keyword is missing in the execute immedeate statement

Error 4: 'into' keyword should be placed before 'from tableName'