CLEAR SCREEN;
SET SERVEROUTPUT ON;
DECLARE
  stmt NVARCHAR2(200);
  myTable DBA_TABLES.TABLE_NAME%TYPE;
  myTableRows INTEGER;
  myOwner NVARCHAR2(200) := 'SOME_SCHEMA_NAME';
  CURSOR C1 IS SELECT TABLE_NAME FROM DBA_TABLES WHERE OWNER = myOwner ORDER BY TABLE_NAME;
BEGIN
  OPEN C1;
  LOOP
    FETCH C1 INTO myTable;
    EXIT WHEN C1%NOTFOUND;
    
    stmt := 'SELECT COUNT(*) FROM ' || myOwner || '.' || myTable || ';';
    DBMS_OUTPUT.PUT_LINE(stmt);

    EXECUTE IMMEDIATE stmt INTO myTableRows;
    DBMS_OUTPUT.PUT_LINE(myTable || '; count = ' || myTableRows);
  END LOOP;
  CLOSE C1;
END;

EXECUTE IMMEDIATE stmt INTO myTableRows;

DBMS_OUTPUT.PUT_LINE(stmt);

SELECT COUNT(*) FROM SOME_SCHEMA_NAME.SOME_TABLE;

Error starting at line : 3 in command -
DECLARE
  stmt NVARCHAR2(200);
  myTable DBA_TABLES.TABLE_NAME%TYPE;
  myTableRows INTEGER;
  myOwner NVARCHAR2(200) := 'SOME_SCHEMA_NAME';
  CURSOR C1 IS SELECT TABLE_NAME FROM DBA_TABLES WHERE OWNER = myOwner ORDER BY TABLE_NAME;
BEGIN
  OPEN C1;
  LOOP
    FETCH C1 INTO myTable;
    EXIT WHEN C1%NOTFOUND;

    stmt := 'SELECT COUNT(*) myCount FROM ' || myOwner || '.' || myTable || ';';
    --DBMS_OUTPUT.PUT_LINE(stmt);

    EXECUTE IMMEDIATE stmt INTO myTableRows;
    --DBMS_OUTPUT.PUT_LINE(myTable || '; count = ' || myTableRows);
    --DBMS_OUTPUT.PUT_LINE(myTable);
  END LOOP;
  CLOSE C1;
END;
Error report -
ORA-06550: Строка 16, столбец 23:
PLS-00382: выражение неправильного типа
ORA-06550: Строка 16, столбец 5:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

Error starting at line : 3 in command -
DECLARE
  stmt NVARCHAR2(200);
  myTable DBA_TABLES.TABLE_NAME%TYPE;
  myTableRows INTEGER;
  myOwner NVARCHAR2(200) := 'SOME_SCHEMA_NAME';
  CURSOR C1 IS SELECT TABLE_NAME FROM DBA_TABLES WHERE OWNER = myOwner ORDER BY TABLE_NAME;
BEGIN
  OPEN C1;
  LOOP
    FETCH C1 INTO myTable;
    EXIT WHEN C1%NOTFOUND;

    stmt := 'SELECT COUNT(*) myCount FROM ' || myOwner || '.' || myTable;
    --DBMS_OUTPUT.PUT_LINE(stmt);

    EXECUTE IMMEDIATE stmt INTO myTableRows;
    --DBMS_OUTPUT.PUT_LINE(myTable || '; count = ' || myTableRows);
    --DBMS_OUTPUT.PUT_LINE(myTable);
  END LOOP;
  CLOSE C1;
END;
Error report -
ORA-06550: Строка 16, столбец 23:
PLS-00382: выражение неправильного типа
ORA-06550: Строка 16, столбец 5:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

Строка 16, столбец 23:

 dynamic_sql_stmt 

String literal, string variable, or string expression that represents a  SQL statement. Its type must be either CHAR, VARCHAR2, or CLOB .

stmt NVARCHAR2(200);

stmt VARCHAR2(200);