假设所有的表都有一个email列，然后循环遍历这些表并构建SQL，然后调用EXECUTE IMMEDIATE一次：

DECLARE
  v_sql    CLOB := NULL;
  v_emails SYS.ODCIVARCHAR2LIST;
BEGIN
  FOR r IN (SELECT table_name FROM user_tables)
  LOOP
    IF v_sql IS NULL THEN
      v_sql := 'SELECT email from ' || r.table_name;
    ELSE
      v_sql := v_sql || ' UNION ALL SELECT email from ' || r.table_name;
    END IF;
  END LOOP;
  EXECUTE IMMEDIATE v_sql BULK COLLECT INTO v_emails;

  -- Do something with the emails.
  FOR i IN 1 .. v_emails.COUNT
  LOOP
    DBMS_OUTPUT.PUT_LINE(v_emails(i));
  END LOOP;
END;
/

其中，对于样本数据：

CREATE TABLE table1 (email) AS
SELECT 'alice@example.com' FROM DUAL UNION ALL
SELECT 'betty@example.com' FROM DUAL UNION ALL
SELECT 'carol@example.com' FROM DUAL UNION ALL
SELECT 'debra@example.com' FROM DUAL;

CREATE TABLE table2 (email) AS
SELECT 'emily@example.com' FROM DUAL UNION ALL
SELECT 'fiona@example.com' FROM DUAL;

CREATE TABLE table3 (email) AS
SELECT 'gerri@example.com' FROM DUAL;

输出：

emily@example.com
fiona@example.com
alice@example.com
betty@example.com
carol@example.com
debra@example.com
gerri@example.com

如果没有收藏：

DECLARE
  v_sql CLOB := NULL;
  v_cur SYS_REFCURSOR; 
  v_email  VARCHAR2(4000);
BEGIN
  FOR r IN (SELECT table_name FROM user_tables)
  LOOP
    IF v_sql IS NULL THEN
      v_sql := 'SELECT email from ' || r.table_name;
    ELSE
      v_sql := v_sql || ' UNION ALL SELECT email from ' || r.table_name;
    END IF;
  END LOOP;

  OPEN v_cur FOR v_sql;
  LOOP
    FETCH v_cur INTO v_email;
    EXIT WHEN v_cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_email);
  END LOOP;
  CLOSE v_cur;
END;
/

或

DECLARE
  v_cur    SYS_REFCURSOR;
  v_email  VARCHAR2(4000);
BEGIN
  FOR r IN (SELECT table_name FROM user_tables)
  LOOP
    OPEN v_cur FOR 'SELECT email from ' || r.table_name;
    LOOP
      FETCH v_cur INTO v_email;
      EXIT WHEN v_cur%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(v_email);
    END LOOP;
    CLOSE v_cur;
  END LOOP;
END;
/

其输出相同。
fiddle

为什么不是一个简单的联盟？

create or replace view v_email as
select email from tb1
union all
select email from tb2
union all
select email from tb3;

为什么你认为你需要PL/SQL？