Oracle creating a dynamic plsql command

Question

I'm trying to put together a procedure, which counts the rows for each PARTITION in a table but I'm getting a syntax error:

Errors: PROCEDURE COUNT_PARTITION Line/Col: 14/31 PLS-00103: Encountered the symbol "(" when expecting one of the following:

I know this isn't the most efficient way and I can use the num_rows column along with gathering statistics to achieve the same results.

Below is my test CASE. I know the problem is with the construction of the 'cmd' call but I can't seem to get it to work. Any help would be greatly appreciated.

CREATE OR REPLACE PROCEDURE cmd(p_cmd varchar2) 
 authid current_user
is
BEGIN 

dbms_output.put_line(p_cmd);

 execute immediate p_cmd;

END;
/


CREATE  OR REPLACE PROCEDURE
count_partition(
  p_tab varchar2
) authid current_user
is
  v_cnt integer;
BEGIN

   for cur_rec in (select table_name, partition_name,
partition_position
 FROM user_tab_partitions where table_name = p_tab order by partition_position) loop
      
   cmd ('select count(*) /*+ parallel(a,8) */
from ' ||p_tab|| 'PARTITION ' ('||cur_rec.partition_name||')' INTO v_cnt);
    DBMS_OUTPUT.PUT_LINE(cur_rec.table_name || ' ' || cur_rec.partition_name || ' ' || v_cnt || ' rows');
   end loop;
END;

Answer 1

Created a procedure and wrapper

CREATE  OR REPLACE PROCEDURE
count_partition(
  p_tab varchar2
) authid current_user
is
  sql_stmt varchar2(1024);
row_count number;

cursor get_tab is
select table_name,
         partition_name
from user_tab_partitions
where table_name=p_tab;

BEGIN 
    for get_tab_rec in   get_tab loop
    BEGIN
    sql_stmt := 'select count(*) /*+ parallel(a,4) */ from ' ||get_tab_rec.table_name||' partition ( '||get_tab_rec.partition_name||' )';

--dbms_output.put_line(sql_stmt);

    EXECUTE IMMEDIATE sql_stmt INTO row_count;

dbms_output.put_line('Table '||rpad(get_tab_rec.table_name
||'('||get_tab_rec.partition_name||')',50)
||' '||TO_CHAR(row_count)||' rows.');
exception when others then
dbms_output.put_line
('Error counting rows for table '||get_tab_rec.table_name);
    END;
  END LOOP;
END;
/

BEGIN 
    FOR cur_r IN(
      SELECT TABLE_NAME FROM USER_PART_TABLES 
)
   LOOP
  --DBMS_OUTPUT.put_line('Table '|| cur_r.table_name);

  count_partition (cur_r.table_name);
  
  END LOOP;
END;

~~