Spool the data from execute immediate within pl/sql block

Question

Ho to spool the data from execute immediate in pl/sql.

Below is the code.

    DECLARE
     from_dt varchar(300):=&from_date;
     to_dt varchar2(300):=&to_date;
    account varchar2(200):=&account;
    mbl_table varchar2(100);
    dn_table varchar2(100);
    id number;
    Begin

    select aid into id from account where upper(acode)=upper(account);
    select tablename into mbl_table from partner_mbl where pid in (select pid from account where upper(acode)=upper(account));
    select dn_tablename into dn_table from partner_mbl where pid in (select pid from account where upper(acode)=upper(account));
    dbms_output.put_line(mbl_table);
    FOR i IN (select 'p_'||to_char((ROWNUM-1 + to_date(from_dt, 'ddmonyyyy')),'ddmonyyyy') part FROM all_objects
     WHERE ROWNUM-2 < to_date(to_dt, 'ddmonyyyy')-to_date(from_dt, 'ddmonyyyy'))loop

 execute immediate 'Select a.pcode,A.Acode,To_Char(A.Mobile),a.msg,a.senderid,To_Char(A.Rts,'||'''dd/mm/yyyy hh24:mi:ss'''||'),To_Char(A.Sts,'||'''dd/mm/yyyy hh24:mi:ss'''||'),A.Statusflag,A.Statusid,To_Char(B.Lastts,'||'''dd/mm/yyyy hh24:mi:ss'''||'),B.Statusflag,B.Err_Des From '|| mbl_table ||' partition('||i.part||') A, '|| dn_table ||' partition('||i.part||') B Where a.aid='||id|| ' And A.Msgid = B.Msgid(+)';

end loop;
    End;

how to spool the output of execute immediate into csv file

See the accepted answer of [How to create a oracle sql scrpt spool file](http://stackoverflow.com/questions/8590826/how-to-create-a-oracle-sql-scrpt-spool-file) — Klas Lindbäck, Mar 14 '14 at 14:06
You can use INTO as usual. Just be sure to put the INTO outside the string, like this: `EXECUTE IMMEDIATE 'select ...' INTO pcode, acode, ...;` — Klas Lindbäck, Mar 17 '14 at 07:13
Great @KlasLindbäck. Open Cursor for is working fine. But if i spool the data outside the loop only last row of the cursor will be spooled. — user3419321, Mar 17 '14 at 08:26
Put the `DBMS_OUTPUT.PRINT_LINE` call inside your loop and the `SPOOL` outside the anonymous procedure. — Klas Lindbäck, Mar 17 '14 at 08:28

score 2 · Answer 1 · answered Mar 18 '14 at 21:51

DECLARE
  from_dt   varchar(300) := &from_date;
  to_dt     varchar2(300) := &to_date;
  account   varchar2(200) := &account;
  mbl_table varchar2(100);
  dn_table  varchar2(100);
  id        number;
  /*define types*/ 
  /* change the next varchar2(1000) with your types*/
  t_pcode IS TABLE OF varchar2(1000);
  v_pcode t_pcode;

  t_Acode IS TABLE OF varchar2(1000);
  v_Acode t_Acode;

  t_Mobile IS TABLE OF varchar2(1000);
  v_Mobile t_Mobile;

  t_msg IS TABLE OF varchar2(1000);
  v_msg t_msg;

  t_senderid IS TABLE OF varchar2(1000);
  v_senderid t_senderid;

  t_Rts IS TABLE OF varchar2(1000);
  v_Rts t_Rts;

  t_Sts IS TABLE OF varchar2(1000);
  V_Sts t_Sts;

  t_Statusflag IS TABLE OF varchar2(1000);
  v_Statusflag t_Statusflag;

  t_Statusid IS TABLE OF varchar2(1000);
  v_Statusid t_Statusid;

  t_Lastts IS TABLE OF varchar2(1000);
  v_Lastts t_Lastts;

  t_Statusflag IS TABLE OF varchar2(1000);
  v_Statusflag t_Statusflag;

  t_Err_Des IS TABLE OF varchar2(1000);
  v_Err_Des t_Err_Des;
  /*end types*/
Begin

  select aid into id from account where upper(acode) = upper(account);
  select tablename
    into mbl_table
    from partner_mbl
   where pid in
         (select pid from account where upper(acode) = upper(account));
  select dn_tablename
    into dn_table
    from partner_mbl
   where pid in
         (select pid from account where upper(acode) = upper(account));
  dbms_output.put_line(mbl_table);
  FOR i IN (select 'p_' ||
                   to_char((ROWNUM - 1 + to_date(from_dt, 'ddmonyyyy')),
                           'ddmonyyyy') part
              FROM all_objects
             WHERE ROWNUM - 2 < to_date(to_dt, 'ddmonyyyy') -
                   to_date(from_dt, 'ddmonyyyy')) loop

    /*    execute immediate 'Select a.pcode,A.Acode,To_Char(A.Mobile),a.msg,a.senderid,To_Char(A.Rts,' ||
                          '''dd/mm/yyyy hh24:mi:ss''' || '),To_Char(A.Sts,' ||
                          '''dd/mm/yyyy hh24:mi:ss''' ||
                          '),A.Statusflag,A.Statusid,To_Char(B.Lastts,' ||
                          '''dd/mm/yyyy hh24:mi:ss''' ||
                          '),B.Statusflag,B.Err_Des From ' || mbl_table ||
                          ' partition(' || i.part || ') A, ' || dn_table ||
                          ' partition(' || i.part || ') B Where a.aid=' || id ||
                          ' And A.Msgid = B.Msgid(+)' ;
    */
    execute immediate 'Select a.pcode,A.Acode,To_Char(A.Mobile),a.msg,a.senderid,To_Char(A.Rts,' ||
                      '''dd/mm/yyyy hh24:mi:ss''' || '),To_Char(A.Sts,' ||
                      '''dd/mm/yyyy hh24:mi:ss''' ||
                      '),A.Statusflag,A.Statusid,To_Char(B.Lastts,' ||
                      '''dd/mm/yyyy hh24:mi:ss''' ||
                      '),B.Statusflag,B.Err_Des From ' || mbl_table ||
                      ' partition(' || i.part || ') A, ' || dn_table ||
                      ' partition(' || i.part || ') B Where a.aid=' || id ||
                      ' And A.Msgid = B.Msgid(+)' 
    BULK COLLECT INTO v_pcode, v_Acode, v_Mobile, v_msg, v_senderid, 
           v_Rts, V_Sts, v_Statusflag, v_Statusid, v_Lastts, 
           v_Statusflag, v_Err_Des;
    for i.. v_pcode.count loop
      dbms_output.put_line('pcode and msg values are: '||v_pcode(i)||' ' ||v_msg(i) ||' at line' || i );
      /*you can get out other values with same way*/
    end loop;

End;

score 1 · Answer 2 · answered Mar 18 '14 at 22:01

you can also take a look at oracle documentation for "oracle collections", "execute immediate", "bulk collect".

my previous post could solve your problem however here is a simple example as additional info.

DECLARE
  TYPE emp_typ IS TABLE OF scott.emp%ROWTYPE;
  e_tab     emp_typ;

BEGIN
  EXECUTE IMMEDIATE 'SELECT * FROM emp'
  BULK COLLECT INTO e_tab;

  DBMS_OUTPUT.put_line('Dynamic EXECUTE: ' || e_tab.count);
  for i in 1..e_tab.count loop
      DBMS_OUTPUT.put_line( e_tab(i).ename ||':'|| e_tab(i).empno );
      null;
  end loop;
END;
/

Spool the data from execute immediate within pl/sql block

2 Answers2