Re: Reporting with dynamic sql

G Quesnel wrote:
> Do you realize that two users accessing the same temp table don't see
> each others' data. I wasn't sure you got that based on your comment.

And yet that's exactly what it seems to be doing. If I run the program alone, the sum of qty for example gives me 30. I asked a co-worker to run the same program, but with one or two seconds of difference from me. Result was 60, which is wrong, it should be 30.

So I create the table on the fly since I'm first and insert the data according to the query. When my coworker runs the program a few seconds later, he won't be creating the table since was already created by me, but he will be inserting data in it. That's what's causing the problem and why I was asked to add a timestamp to the name of the temp table. But now I see that it's going to create more tables on the fly if many users run the report within a short timeframe of each other. I see what you guys mean when you say it's not a good idea, but that's what I have to work with for now.

> >It looks like dynamic sql and SQL*Plus report capabilities don't mix very well
>
> I don't agree. You just need to get more familiar with them.
> If you want to mix variables to hold tablespace name and extent sizes
> with execute immediate and show the resulting statement, or # of rows
> updated , or ... in dbms_output. Mix it all in with regular SQL
> inbetween anonymous block and you get something pretty powerfull.
>
> HTH
Yeah, getting familiar with this is what I'm trying to do, and I realize I have a lot of ground to cover. I'll find a way :)

Denis Received on Wed Aug 16 2006 - 09:32:53 CDT