-- Oracle 12c: Function to compute query within a query
-- Using the WITH syntax, we can get counts for several tables that 
-- have "DIM_" in their name.  A PL/SQL function is created to 
-- compute counts in a WITH clause and calling the function in
-- a query.

WITH 
 FUNCTION fn_get_counts(i_table IN VARCHAR2)
 RETURN  NUMBER
 IS
    v_cnt   NUMBER;
 BEGIN
   --If parallel option enabled it can use parallel processing 
   EXECUTE IMMEDIATE 'SELECT /*+ parallel(t, 8) */ COUNT(*) 
    FROM '||i_table||' t' INTO  v_cnt;

   RETURN v_cnt;
 EXCEPTION 
   WHEN OTHERS THEN
    RETURN  -1;
 END fn_get_counts;
SELECT
 owner||'.'||table_name  as table_name,
 fn_get_counts(i_table => owner||'.'||table_name) table_cnts 
FROM  all_tables
WHERE INSTR(table_name, 'ALL_') >0;