how can I dynamically select a column from a list of tables and output timestamp results?-CodePudding

I have a large number of identical tables containing columns "eventtime" and "eventvalue" eventtime is TIMESTAMP(6) and eventvalue is NUMBER (but I don't care about it.)

table names are like data_001, data_002, data_003, etc. all identically defined but it doesn't matter because I have another table called table_list which contains a list of these tables by name. (They are dynamically generated.)

So "select distinct data_table from table_list" gets you output like data_001 data_002 data_003

and so on.

I am trying to extract the minimum timestamp from the eventtime column of each table and output it to DBMS_OUTPUT and I can't get it to work.

DECLARE
  mystr VARCHAR(1000);
  v_mystamp TIMESTAMP(6);
  cursor c1 is 
    select distinct data_table from table_list order by data_table asc;
 
  
BEGIN
FOR rec IN c1 LOOP
     mystr := 'select min(eventtime) into :v_mystamp from ' || rec.data_table;
     
     execute immediate mystr;
     DBMS_OUTPUT.PUT_LINE(rec.data_table);
     DBMS_OUTPUT.PUT_LINE(v_mystamp);
   END LOOP;
END;

The expected output is a list of tables, and the min(eventtime) from each table.

What I am getting is a list of tables, and then a blank line after each, and I am wondering what I am doing wrong... somehow it is not capturing the min(eventtime) properly (or not outputting it properly?) but I am not sure why.

CodePudding user response：

Put INTO clause into execute immediate:

mystr := 'select min(eventtime) from ' || rec.data_table;
 
execute immediate mystr into v_mystamp ;