Re: Reporting with dynamic sql

Sybrand Bakker wrote:
> 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

Sybrand,

I think I saw that kind of construct while looking left and right for a solution to my problem. Like I said, oracle newbie here. I will need to investigate it, but I'm not sure if I'll be able to apply it to my current situation (See my reply to DA above).

On the other hand, let's say I use your construct instead of a temp table. I'm still not out of the wood am I? I'll not be able to use SQL*Plus reporting handling and have to construct every line of the report by hand and dbms_output.put_line everything right? Received on Wed Aug 16 2006 - 07:17:01 CDT