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

WITH TABLE_1 AS (SELECT * FROM EXAMPLE_TABLE),
     TABLE_2 AS (SELECT * FROM EXAMPLE_TABLE_2)
SELECT 'T1' AS "TABLE_NAME", COUNT(*) AS "N_ROW" FROM TABLE_1
UNION ALL
SELECT 'T2' AS "TABLE_NAME", COUNT(*) AS "N_ROW" FROM TABLE_2;


TABLE_NAME
N_ROW


T1
2

T2
3