By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
begin
Declare
v_sql varchar2(500);
v_sql2 varchar2(500);
v_prj_id varchar2(4000):='ProjectA,ProjectB,ProjectC,ProjectD';
BEGIN
--
FOR i IN (SELECT trim(regexp_substr(v_prj_id, '[^,]+', 1, LEVEL)) l
FROM dual
CONNECT BY LEVEL <= regexp_count(v_prj_id, ',') + 1
) LOOP
v_sql := v_sql || 'select '''|| i.l ||''' AS "PRJ_ID", EVENT, email,modified_by,modified from ' || i.l ||
'.Table UNION ALL ' || chr(10) ;
--
END LOOP;
v_sql2 := RTRIM(v_sql, 'UNION ALL ' || chr(10) ) || ';';
Dbms_Output.Put_Line (v_sql2);
END;
end;
/
1 rows affected
dbms_output:
select 'ProjectA' AS "PRJ_ID", EVENT, email,modified_by,modified from ProjectA.Table UNION ALL
select 'ProjectB' AS "PRJ_ID", EVENT, email,modified_by,modified from ProjectB.Table UNION ALL
select 'ProjectC' AS "PRJ_ID", EVENT, email,modified_by,modified from ProjectC.Table UNION ALL
select 'ProjectD' AS "PRJ_ID", EVENT, email,modified_by,modified from ProjectD.Table;