Re: Reporting with dynamic sql

Deltones wrote:
> 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

You didn't insert anything. How can you expect to get anything back?

But on another topic this is a really bad construct. I would recommend against using PL/SQL to build tables or the building of tables on the fly.

Based on my interpretation of what you are doing one table is all you need. Just add a date column to it.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org