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

Question

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.

Is the function you're calling, data_retention, supposed to match the function you're showing, data_bound? Or is there a layer of code missing from your question? — Justin Cave
– Justin Cave, Commented Apr 19, 2022 at 17:30
OK. There are syntax errors compiling your function. Are those also mistakes in putting the question together? Or are those in your actual code? — Justin Cave
– Justin Cave, Commented Apr 19, 2022 at 17:33
OK. That helps. Now you say that you're still "getting multiple errors". Show us the errors you're getting. Have you tried executing the function with a single hard-coded table name? Did it produce the results you wanted? You're building the query assuming there is a column date in each table. date is a reserved word so I doubt your tables actually have a column by that name. Not sure if that is another transcription error though. — Justin Cave
– Justin Cave, Commented Apr 19, 2022 at 17:44
the length part is missing in VARCHAR2 data type for reqdates local variable. — Barbaros Özhan
– Barbaros Özhan, Commented Apr 19, 2022 at 17:48

MT0 · Accepted Answer · 2022-04-19 17:58:58Z

2

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

db<>fiddle here

edited Apr 19, 2022 at 17:58

answered Apr 19, 2022 at 17:53

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jaikrit · Accepted Answer · 2022-04-19 17:33:32Z

0

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'

answered Apr 19, 2022 at 17:33

Jaikrit

11 bronze badge

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related