PL/SQL Dynamic Table From Cursor Select

I am trying create a PL/SQL to get sums of values from dynamic table names. I am using Oracle 11c. The table names are based off row values in another table. Rows will be added or may be deleted. Here is what I have tried that gives me the error that the table doesn't exist.

set serveroutput on;



declare 

v_table         varchar2(10);
v_appname       varchar2(50);
v_appcnt        number;
v_cnts_appname  varchar2(50);

-- get app names
cursor appid_cur is
select 'ae_dt'||APPID, APPNAME from AE_APPS;

begin
open appid_cur;
loop
    fetch appid_cur into v_table, v_appname;
      FOR Cur_Rec IN (SELECT sum(NUMOBJECTS) cnt FROM v_table) LOOP
          dbms_output.put_line(v_appname||', '||Cur_Rec.cnt);
      END LOOP;
    close appid_cur;
 END LOOP;
 
 END;

You'll need dynamic SQL for that (because you have to include table name into a statement which will be executed), along with inner BEGIN-EXCEPTION-END block which will handle errors and let loop continue to another round, without raising an error.

Sample table that contains table names:

SQL> select * from ae_apps;

TABLE_NAME
------------------------------
EMP
DEPT
DOES_NOT_EXIST

Just note that - if you wanted to fetch number of rows in those tables - you could query user_tables. If you collect statistics regularly, num_rows will retrieve value which was valid at the time of gathering statistics:

SQL> select table_name, num_rows from user_tables
  2  where table_name in ('EMP', 'DEPT', 'DOES_NOT_EXIST');

TABLE_NAME                         NUM_ROWS
------------------------------ ------------
DEPT                                      4
EMP                                      14

Though, if you want to do it right now, you'll have to calculate that number in your PL/SQL procedure:

SQL> set serveroutput on
SQL> declare
  2    l_str varchar2(200);
  3    l_cnt number;
  4  begin
  5    for cur_r in (select table_name from ae_apps) loop
  6      begin
  7        l_str := 'select count(*) from ' || cur_r.table_name;
  8        execute immediate l_str into l_cnt;
  9        dbms_output.put_Line(cur_r.table_name ||': '|| l_cnt);
 10      exception
 11        when others then
 12          dbms_output.put_line(cur_r.table_name ||': '|| sqlerrm);
 13      end;
 14    end loop;
 15  end;
 16  /
EMP: 14
DEPT: 4
DOES_NOT_EXIST: ORA-00942: table or view does not exist

PL/SQL procedure successfully completed.

SQL>

The last row (in output) represents table that doesn't exist, and that fact was handled in exception section.

Code you posted fetches sum(numobjects), but - I don't know what it represents. Anyway, now that you know how to approach the problem, adjust your own code so that it compiles and returns desired result.

I need to use an execute immediate.

DECLARE
   CURSOR cv_table IS select 'ae_dt'||APPID, APPNAME from AE_APPS;
   v_table         varchar2(10);
   v_appname       varchar2(50);
   c number;
BEGIN
   OPEN cv_table;
   LOOP
      FETCH cv_table INTO v_table, v_appname;
      EXIT WHEN cv_table%NOTFOUND;
      execute immediate 'SELECT sum(NUMOBJECTS) FROM '|| v_table into c;
     dbms_output.put_line(v_appname||' Number of document pages: '||to_char (c) );
  END LOOP;

END;