Re: Reporting with dynamic sql

On 15 Aug 2006 12:23:36 -0700, "Deltones" <vibroverb_at_hotmail.com> 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

creating 'temporary tables' on the fly has 'bad idea' inscribed all over it. This is Oracle. In Oracle you *don't* need temporary tables.

What you need is a REF CURSOR.

Like this

variable cur REF CURSOR
begin
open :cur for
'<your query *without temporary tables* here'; end;
/
print cur

And that is it.

And yes, this is documented.

--
Sybrand Bakker, Senior Oracle DBA