-- 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;
9869