Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE OR REPLACE FUNCTION TABCOUNT (tbl_name IN VARCHAR2)
   RETURN PLS_INTEGER
IS
   retval   PLS_INTEGER;
BEGIN
   EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || tbl_name INTO retval;

   RETURN retval;
END;
/
CREATE TABLE EXAMPLE_TABLE ("ROW_ID" INT);
INSERT INTO EXAMPLE_TABLE VALUES (1);
1 rows affected

INSERT INTO EXAMPLE_TABLE VALUES (2);
1 rows affected

SELECT TABCOUNT('EXAMPLE_TABLE') AS "N_ROW" FROM DUAL;


N_ROW


2


WITH TABLE_1 AS (SELECT * FROM EXAMPLE_TABLE)
SELECT 'T1' AS "TABLE_NAME", TABCOUNT (TABLE_1) AS "N_ROW"
  FROM DUAL;
ORA-00904: "TABLE_1": invalid identifier

CREATE TABLE EXAMPLE_TABLE_2 AS SELECT * FROM EXAMPLE_TABLE;
2 rows affected

INSERT INTO EXAMPLE_TABLE_2 VALUES (3);
1 rows affected

WITH TABLE_1 AS (SELECT * FROM EXAMPLE_TABLE),
     TABLE_2 AS (SELECT * FROM EXAMPLE_TABLE_2)
SELECT 'T1' AS "TABLE_NAME", TABCOUNT (TABLE_1) AS "N_ROW" FROM DUAL
UNION ALL
SELECT 'T2' AS "TABLE_NAME", TABCOUNT (TABLE_2) AS "N_ROW" FROM DUAL;
ORA-00904: "TABLE_1": invalid identifier

SELECT 'EXAMPLE_TABLE' AS "TABLE_NAME", TABCOUNT ('EXAMPLE_TABLE') AS "N_ROW" FROM DUAL
UNION ALL
SELECT 'EXAMPLE_TABLE_2' AS "TABLE_NAME", TABCOUNT ('EXAMPLE_TABLE_2') AS "N_ROW" FROM DUAL;


TABLE_NAME
N_ROW


EXAMPLE_TABLE
2

EXAMPLE_TABLE_2
3


WITH CTE (TBL_NAME) AS (
  SELECT 'EXAMPLE_TABLE' FROM DUAL
  UNION ALL
  SELECT 'EXAMPLE_TABLE_2' FROM DUAL
)
SELECT TBL_NAME AS "TABLE_NAME", TABCOUNT (TBL_NAME) AS "N_ROW" FROM CTE;


TABLE_NAME
N_ROW


EXAMPLE_TABLE
2

EXAMPLE_TABLE_2
3


SELECT COLUMN_VALUE AS "TABLE_NAME", TABCOUNT (COLUMN_VALUE) AS "N_ROW"
FROM TABLE(sys.odcivarchar2list('EXAMPLE_TABLE', 'EXAMPLE_TABLE_2'));


TABLE_NAME
N_ROW


EXAMPLE_TABLE
2

EXAMPLE_TABLE_2
3


select column_value as table_name,
  to_number(xmlquery('/ROWSET/ROW/C/text()'
    passing xmltype(dbms_xmlgen.getxml(
      'select count(*) as c from "' || column_value || '"'))
    returning content)) as n_row
from table(sys.odcivarchar2list('EXAMPLE_TABLE', 'EXAMPLE_TABLE_2'));


TABLE_NAME
N_ROW


EXAMPLE_TABLE
2

EXAMPLE_TABLE_2
3