p.s. Oracle 9.2.0.1 is a very old and unsupported version of Oracle, as well as being very buggy. 9.2.0.7 was considered the first stable version of 9i, but even that is now old and unsupported. You really need to upgrade to a recent version of the database, at least 11g, if not the new 12c version.

Whenever you do dynamic SQL, you should put the entire dynmic statement into a single string variable. During debugging, display that string before executing it. If you get a run-time error, that will show you the statement that caused it, which often makes the cause obvious. For example:

DECLARE

CURSOR ut IS

SELECT table_name

FROM user_tables;

cnt NUMBER;

sql_txt VARCHAR2 (1000);

BEGIN

FOR t IN ut

LOOP

sql_txt := 'SELECT COUNT (*) '

|| 'FROM "' || t.table_name || '"';

dbms_output.put_line (sql_txt || ' = sql_txt'); -- For debugging

EXECUTE IMMEDIATE sql_txt INTO cnt;

dbms_output.put_line ( 'Table: '

|| t.table_name

|| ' ('

|| cnt

|| ' rows)'

);

END LOOP;

END;

/

You can comment out the extra put_line call when you're confident that the dyanmic statement is working.

If you have any non-standard table names (for example, names that contain spaces) you need to enclose the table names in double-quotes, as I did above.

Table names is stored in a separate table; how can I use field name as table name?

This is where I used to point a finger at the absurdity of such an approach, the fundamental and total failure of such a data model, and laugh myself silly.

But after umpteenth time of seeing this brick wall, chosen as "The Solution", being run into at speed by some ignorant sod, it ceases to be funny - and is just a sad, sad, spectacle.

If you want to discuss a meaningful solution, as oppose to how to deal with brick walls as solutions, I'm all ears and will do my best to assist.

Now excuse me as I move off to the side, allowing you to continue running into the wall, bleed, in the belief that the speed with which you hit the brick wall, makes you now so much closer to a solution.