Re: Reporting with dynamic sql

Deltones wrote:

> 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.

Well it seemingly was done by someone whose understanding of databases was based on the Ingres family (Ingres, Sybase, Informix) rather than Oracle. Unless your desire is to contribute to and extend a concept that will kill performance and scalability ... make part of your modification removing this kludge.

> 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.

Unless you wish to be part of the problem rather than part of the solution. What you are describing is a textbook example of someone attacking Oracle and assuming it is SQL Server. Fix it or you will fight this until you resign.

> But in a way, you answered my question. It looks like dynamic sql and
> SQL*Plus report capabilities don't mix very well.

They way I answered it was to indicate that the entire concept and architecture are a disaster. Sit down with your IT manager and explain it to them. If they don't get it you are free to give them my email address and I'll be happy to do so.

-- 
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