0

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;
Aswin Ajai
  • 11
  • 2
  • See similar question: https://stackoverflow.com/questions/14528853/how-to-output-result-of-select-statement-which-is-executed-using-native-dynamic – kfinity Jan 05 '21 at 18:18
  • Or this one: https://stackoverflow.com/questions/1366426/display-dynamic-execute-output-within-pl-sql-from-sqlplus – kfinity Jan 05 '21 at 18:19

1 Answers1

3

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>
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • If I execute without execute immediate it is showing results. Can you please advise what is the difference when we use execute immediate around this? Am a noobie in this. Please excuse if the doubt is a blunder – Aswin Ajai Jan 05 '21 at 18:30
  • Execute immediate reads as "dynamic SQL". There's nothing "dynamic" in your query (for example, it would be if it was a stored procedure that accepts table and/or column names, so you'd have to "compose" the SELECT statement based on what users passed to the procedure), so you could have removed it entirely. But, if you are practicing PL/SQL, then that - what I showed - is the way to do it. – Littlefoot Jan 05 '21 at 18:33
  • "If I execute without execute immediate it is showing results." Not with the code you are showing, it doesn't. PL/SQL does not directly interact with the client. If you want to sow the client some output from PL/SQL, you have to use dbms_output, which you don't seem to be doing. In addition, dbms_output only writes to a buffer. It is up to the calling client to process that buffer upon completion of the PL/SQL procedure. That's where SET SERVOUT ON comes in - telling sqlplus to process and display the dbms_ouput buffer. – EdStevens Jan 05 '21 at 19:36
  • The code, as you show it (with or without the execute immediate, should throw an error. As @Littlefoot said, you have to select INTO a variable, else pl/sql won't know what to do with the results of the SELECT. I don't see an INTO in any of your code. – EdStevens Jan 05 '21 at 19:38