Reporting with dynamic sql

Hi all,

Newbie here. I've searched this newsgroup but couldn't put my finger on what I was looking for. Is it possible to mix dynamic sql with SQL*Plus reporting ability without having to "dbms_output" every line of the report? Basically, here's what I mean in the sample code below:

variable vTimeStamp char(6);

BEGIN    select to_char(sysdate, 'hhmiss') into :vTimeStamp from dual;

   execute immediate 'create table tmp_dummy_&&4' || '_'

                     || :vTimeStamp || ' (f_dummy varchar2(6))';

END;
/

ttitle left "XXXXXXXXX" CENTER "THIS IS A TEST"-

     skip 1 " TEST " skip 2

col aa format a6 heading 'Test'

BEGIN
   execute immediate 'SELECT f_dummy aa FROM tmp_dummy_&&4' || '_' || :vTimeStamp;
END;
/

exit;

It runs, but I don't get any report results. But if I put the "SELECT" statement outside the BEGIN / END, I get the results I want. Take note that the name of the table is entered in full when I do this. I know the table name for my test. But the real program will have table names with the kind of construction you see above.

You have a way to make this work as above or I really need to "dbms_output" everything between a BEGIN / END duo?

Regards,

Denis Received on Tue Aug 15 2006 - 14:23:36 CDT