Re: Reporting with dynamic sql

From: Deltones <vibroverb_at_hotmail.com>
Date: 16 Aug 2006 05:09:01 -0700
Message-ID: <1155730141.019570.136960@h48g2000cwc.googlegroups.com>

DA Morgan wrote:
> 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.
> --

DA,

You're right as far as INSERT is concerned, but I do have one row inserted for my tests. What's above is just the basic idea behind the larger program. As for creating table on the fly, well, that's the way the program I have to modify was done. One of the thing I'm asked to do is add the timestamp to the filename to correct a situation that happens in production with doubling of results when two users execute the program at the same time. The real temp table created is much larger than just one field and is fed from a pretty long query. Like I said, what's above is the general idea of what I need to do.

But in a way, you answered my question. It looks like dynamic sql and SQL*Plus report capabilities don't mix very well. Received on Wed Aug 16 2006 - 07:09:01 CDT