Oracle 23c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table tb_test1 (id number);
create table tb_test2 (id) as select 42 from dual;
1 rows affected

DECLARE
  CURSOR my_cursor
  IS
    SELECT table_name
    FROM all_tables
    WHERE table_name LIKE 'TB_%'
    ORDER BY table_name ASC;
BEGIN
  FOR my_table_name IN my_cursor
  LOOP
    SELECT SUM(bytes)/1024/1024 AS "Table Size (MB)" 
    FROM user_segments 
    WHERE segment_name=my_table_name;
  END LOOP;
END;
/
ORA-06550: line 13, column 24:
PLS-00382: expression is of wrong type
ORA-06550: line 11, column 5:
PLS-00428: an INTO clause is expected in this SELECT statement

DECLARE
  CURSOR my_cursor
  IS
    SELECT table_name
    FROM all_tables
    WHERE table_name LIKE 'TB_%'
    ORDER BY table_name ASC;
BEGIN
  FOR my_table_name IN my_cursor
  LOOP
    SELECT SUM(bytes)/1024/1024 AS "Table Size (MB)" 
    FROM user_segments 
    WHERE segment_name=my_table_name.table_name;
  END LOOP;
END;
/
ORA-06550: line 11, column 5:
PLS-00428: an INTO clause is expected in this SELECT statement

DECLARE
  my_size NUMBER;
  CURSOR my_cursor
  IS
    SELECT table_name
    FROM all_tables
    WHERE table_name LIKE 'TB_%'
    ORDER BY table_name ASC;
BEGIN
  FOR my_table_name IN my_cursor
  LOOP
    SELECT SUM(bytes)/1024/1024
    INTO my_size
    FROM user_segments 
    WHERE segment_name=my_table_name.table_name;

    dbms_output.put_line('Table Size (MB): ' || my_size);
  END LOOP;
END;
/
1 rows affected

dbms_output:
Table Size (MB): 
Table Size (MB): .0625

SELECT ut.table_name, SUM(bytes)/1024/1024 AS "Table Size (MB)"
FROM user_tables ut
LEFT JOIN user_segments us
ON us.segment_name = ut.table_name
GROUP BY ut.table_name
ORDER BY ut.table_name;


TABLE_NAME
Table Size (MB)


TB_TEST1
null

TB_TEST2
.0625