oracle - How to use WITH clause within EXECUTE IMMEDIATE

Question

I am trying to use a with clause within one of the execute immediate statement. It runs fine, but doesn't provide an output. It says anonymous block completed. I have tried SET SERVEROUTPUT ON command and still doesn't work. Could someone please help me out on this one.

begin
execute immediate 'WITH GG AS (
SELECT G46.PROV_NUM ,G46.SEQ_NUM, FM46G_ALTID_TYPE_1 AS ALTID_TYPE ,ALTID_1 AS ALTID ,FM46G_ALTID_SRC_1 AS ALTID_SRC FROM SPSMDMRW.SCW_CFF_46G G46
UNION
SELECT G46.PROV_NUM ,G46.SEQ_NUM, ALTID_TYPE_2 AS ALTID_TYPE,ALTID_2 AS ALTID,FM46G_ALTID_SRC_1 AS ALTID_SRC  FROM SPSMDMRW.SCW_CFF_46G  G46
UNION
SELECT G46.PROV_NUM ,G46.SEQ_NUM, ALTID_TYPE_3 AS ALTID_TYPE,ALTID_3 AS ALTID,ALTID_SRC_2 AS ALTID_SRC  FROM SPSMDMRW.SCW_CFF_46G G46 
UNION
SELECT G46.PROV_NUM ,G46.SEQ_NUM, ALTID_TYPE_4 AS ALTID_TYPE ,ALTID_4 AS ALTID ,ALTID_SRC_3 AS ALTID_SRC  FROM SPSMDMRW.SCW_CFF_46G G46
) 
select * from GG';
end;

Answer 1

It is not the WITH factoring clause, but the fact that you aren't fetching the result into anything.

Should be something like this:

SQL> set serveroutput on
SQL>
SQL> declare
  2    l_dname sys.odcivarchar2list;
  3  begin
  4    execute immediate 'with gg as
  5                         (select ename from emp where deptno = 10
  6                          union all
  7                          select ename from emp where deptno = 30
  8                         )
  9                       select ename from gg'
 10    bulk collect into l_dname;
 11
 12    for i in l_dname.first .. l_dname.last loop
 13      dbms_output.put_line(l_dname(i));
 14    end loop;
 15  end;
 16  /
ALLEN
WARD
MARTIN
BLAKE
TURNER
JAMES

PL/SQL procedure successfully completed.

SQL>